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
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
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..
for screen shot of download and installation of oracle VM virtual machine more....
download docs from Document
Subscribe to:
Posts (Atom)