ORACLE TABLESPACE USAGE

CHECK tablespace usage script:

Method 1 

select total.tablespace_name,

       round(total.MB, 2) as Total_MB,

       round(total.MB - free.MB, 2) as Used_MB,

       round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

  from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_free_space

         group by tablespace_name) free,

       (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

          from dba_data_files

         group by tablespace_name) total

 where free.tablespace_name = total.tablespace_name;

Method 2

Using OEM, setting in ALL Metrics / Setting, depending on the setting you may get the usage.

The script behind it.

select 

           tablespace_name, 

           round(used_space/(1024*1024),2), 

           round(tablespace_size/(1024*1024),2), 

           round(used_percent, 2) 

from 

           dba_tablespace_usage_metrics

Method 3

Get from check_mk script

select th.instance, f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, f.ONLINE_STATUS, t.BLOCK_SIZE, t.status, decode(sum(fs.blocks), NULL, 0, sum(fs.blocks)) free_blocks 

from v$thread th, dba_data_files f, dba_tablespaces t, dba_free_space fs 

where f.tablespace_name = t.tablespace_name and f.file_id = fs.file_id(+) 

group by th.instance, f.file_name, f.tablespace_name, f.status, f.autoextensible, f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status, t.block_size, t.status 

UNION 

SELECT th.instance, f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, 'TEMP', t.BLOCK_SIZE, t.status, sum(sh.blocks_free) free_blocks 

FROM v$thread th, dba_temp_files f, dba_tablespaces t, v$temp_space_header sh 

WHERE f.tablespace_name = t.tablespace_name and f.file_id = sh.file_id 

GROUP BY th.instance, f.file_name, f.tablespace_name, f.status, f.autoextensible, f.blocks, f.maxblocks, f.user_blocks, f.increment_by, 'TEMP', t.block_size, t.status;