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
