Below script find out which tablespaces don't have space in the database.
column Today NEW_VALUE mhoy noprint format a1 trunc
column Time NEW_VALUE mhora noprint format a1 trunc
column inst NEW_VALUE minst noprint format a1 trunc
select upper(instance) inst
from v$thread;
set pagesize 53
set linesize 120
set feedback off
ttitle left 'DB Report' -
right mtoday skip 1 -
left minst -
right mtime skip 1 -
center 'DATAFILES AND SIZES OF TABLESPACES' skip 3;
btitle skip 2 center 'Page :' format 999 sql.pno;
break on Tablespace on report
compute sum of KBTotal on report
compute sum of KBUsed on report
compute sum of KBFree on report
column Tablespace format a12
column File_name format a32
column status format a10
column KBTotal format 99,999,990
column KBUsed format 99,999,990
column KBFree format 99,999,990
column %Used format 990.99
column %Free format 990.99
column Extents format 990
column MaxExtentKB format 999,999
spool tbsp_free
SELECT TO_CHAR(sysdate, 'DD/MM/YY') Today
, TO_CHAR(sysdate, 'hh24:mi:ss') Time
, df.tablespace_name "Tablespace"
, df.file_name "File_name"
, count(*) "Extents"
, NVL(df.bytes,0)/1024 "KBTotal"
, (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed"
, SUM(NVL(fs.bytes,0))/1024 "KBFree"
, (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0)
/1024)) "%Used"
, ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024) "%Free"
, MAX(NVL(fs.bytes,0))/1024 "MaxExtentKB"
FROM dba_data_files df
, dba_free_space fs
WHERE df.file_id= fs.file_id(+)
GROUP BY df.tablespace_name
, df.file_name
, df.bytes
ORDER BY df.tablespace_name
/
spool off
ttitle off
btitle off
No comments:
Post a Comment