Tablespace information in Oracle
December 12, 2011 Leave a comment
Every DBA evolves his/her own style over a period of time. They build handy scripts to carry out each and every activity in the database, including monitoring jobs, gathering tablespace information etc etc. Here is one particular script that I use to gather free/utilized space information on tablespaces.
SELECT A.TABLESPACE_NAME, A.TOTALSPACE TOTALSPACE,(A.TOTALSPACE - B.FREESPACE) USEDSPACE, B.FREESPACE FREESPACE FROM (SELECT TABLESPACE_NAME,SUM/1024/1024/1024 TOTALSPACE FROM DBA_DATA_FILES where TABLESPACE_NAME in (select tablespace_name from dba_segments where segment_name in ('SET OF TABLES T1','T2','T3'.'T4'))GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME,SUM/1024/1024/1024 FREESPACE FROM DBA_FREE_SPACE where TABLESPACE_NAME in select tablespace_name from dba_segments where segment_name in ('SET OF TABLES T1','T2','T3'.'T4')) GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME =B.TABLESPACE_NAME ;
Sample Output for the above script will be
TABLESPACE_NAME TOTALSPACE USEDSPACE FREESPACE
GPAPP 101 92.4963989 8.50360107
PSINDEX 167 150.486206 16.5137939
Hope it will be useful for you. Comments are most welcome.
“Appreciation is a wonderful thing:It makes what is excellent in others belong to us as well”