TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)


prompt .
prompt ======================================
prompt . TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)
prompt ======================================
column Tablespace format a30
column Size format 999,999,999,999
column Used format 999,999,999,999
column Free format 999,999,999,999
column "% Used" format 999.99
select tablespace_name "Tablesapce",
 bytes "Size",
 nvl(bytes-free,bytes) "Used",
 nvl(free,0) "Free",
 nvl(100*(bytes-free)/bytes,100) "% Used"
 from(
 select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
 FROM (select tablespace_name, sum(bytes) bytes
 from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
 where ddf.tablespace_name = dfs.tablespace_name(+)
 group by ddf.tablespace_name, ddf.bytes)
 order by 5 desc;
set feedback off
set heading off
select rpad('Total',30,'.') "Tablespace",
 sum(bytes) "Size",
 sum(nvl(bytes-free,bytes)) "Used",
 sum(nvl(free,0)) "Free",
 (100*(sum(bytes)-sum(free))/sum(bytes)) "% Used"
 from(
 select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
 FROM (select tablespace_name, sum(bytes) bytes
 from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
 where ddf.tablespace_name = dfs.tablespace_name(+)
 group by ddf.tablespace_name, ddf.bytes);
set feedback on
set heading on

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: