Monday, July 28, 2014


How to see the database is open (mount or unmont ) state in 11g ?

select database_status from v$instance
select * from v$database

_______________________________________________________________________________________________


2. HOW MUCH SPACE IS AVAILABLE IN UNDO N HOW MUCH IS USED


SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+
u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
(select tablespace_name , sum(blocks)*8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select
UPPER(value)
from v$parameter where name = 'undo_tablespace');

3. How much active,expired and unexpired

=====================================



set linesize 152

col tablespace_name for a20

col status for a10

select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name;
(2:35:42 AM) vipul.pahuja@oracle.com: SQL> select * from t11;

SALARY
----------
12000

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 16 1208 .009216309

SQL> update t11 set salary=15000 where salary=12000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> update t11 set salary=20000 where salary=15000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> update t11 set salary=25000 where salary=20000;

1 row updated.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 ACTIVE 1 128 .000976563
UNDOTBS1 EXPIRED 33 744 .00567627
UNDOTBS1 UNEXPIRED 15 1080 .008239746

SQL> commit;

Commit complete.

SQL> select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status having tablespace_name=upper('&TSNAME')
order by tablespace_name; 2 3 4 5
Enter value for tsname: UNDOTBS1
old 4: group by tablespace_name, status having tablespace_name=upper('&TSNAME')
new 4: group by tablespace_name, status having tablespace_name=upper('UNDOTBS1')

TABLESPACE_NAME STATUS Extent Count Total Blocks SPACEINGB
------------------------------ --------- ------------ ------------ ----------
UNDOTBS1 EXPIRED 34 752 .005737305
UNDOTBS1 UNEXPIRED 15 1200 .009155273

SQL>






Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Friday, February 14, 2014

How to unlock the user in oracle Sql

How to unlock the user in oracle 


check more videos on
http://www.youtube.com/channel

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav




Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Monday, February 10, 2014


















Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Saturday, December 28, 2013

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Monday, December 9, 2013

Oracle Certification Path


 Oracle certification path



For those who are looking for oracle certification ( OCA / OCP) in 10g


Oracle Associate Certification Path ( OCA)
STEP 1  
1Z0-051 Oracle Database 11g: SQL Fundamentals I
STEP 2  
1Z0-042 Oracle Database 10g: Administration I
Than you  are  Oracle Database 10gAdministrator Certified Associate
Now Next step is  Oracle Database 10g Administrator Certified Professional
 

 



Oracle Professional Certification Path (OCP)
STEP 1 - PRIOR CERTIFICATION
Oracle Database 10g Administrator Certified Associate
STEP 2 - COMPLETE TRAINING
Complete one of the approved advanced courses
STEP 3 - PASS THIS EXAM  1Z0-043   Oracle Database 10g: Administration II
STEP 4 - COMPLETE THIS FORM
Complete the Course Submission Form
Oracle Database 10gAdministrator Certified Professional






for more info check Oracle web page .



Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Friday, September 27, 2013

Download and Installation of Oracle VM Virtual machine

Download and Installation of Oracle VM Virtual machine

       
                         Here we will see, from where we can download the oracle Vm virtual machine and how install it .also you can find   Prerequisites , Download location and   Installation process..
                               watch more on Chetan yadav videos's
  for screen shot of download and installation of oracle VM virtual machine more....
 download docs from Document

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav