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

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

acehints.com Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer