SQL Query Check Oracle (9i/10g/11g) tablespace free space and space Usage

select a.tablespace_name                                              name,
       b.tablespace_name                                              dummy,
      (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024      mbytes,
      (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )
                - sum(a.bytes)/count( distinct b.file_id ))/1024/1024  used,
      (sum(a.bytes)/count( distinct b.file_id ))/1024/1024        free,
      100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))
           - (sum(a.bytes)/count( distinct b.file_id ) )) /
             (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name != 'UNDO'and a.tablespace_name = b.tablespace_name
having   100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))
         - (sum(a.bytes)/count( distinct b.file_id ) )) /
       (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) > 100-20 /*This is for 80% & Above*/
        group by a.tablespace_name, b.tablespace_name
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google