Tablespace information in Oracle

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”

Advertisements

About PARTHASARATHY.G
I am working as an Associate DBA in fugo consulting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: