Checagem de espaço e status dos datafiles

--PESQUISA TABLESPACES

col startup_time for a20
col instance_name for a15
col host_name for a40
set lines 200
select i.instance_name,
i.host_name,
to_char(i.startup_time,'dd/mm/yyyy hh24:mi:ss') startup_time,
d.log_mode,
i.status,
i.logins,
d.open_mode
from v$instance i,
v$database d;

CLEAR COLUMNS COMPUTES BREAKS
SET LINES 200
SET PAGES 200
COL FILE_NAME FOR A50
COL TABLESPACE_NAME FOR A30
COL STATUS FOR A21
COL "PCT FREE" FOR A10
COMPUTE SUM OF "TOTAL(MB)" ON REPORT
COMPUTE SUM OF "FREE(MB)" ON REPORT
BREAK ON REPORT
SELECT T.TABLESPACE_NAME,
TS.CONTENTS,
TS.STATUS,
ROUND(NVL(T.BYTES,0)/1024/1024,1) "TOTAL(MB)",
--NVL(NVL(F.FREE,FT.FREE),0)/1024/1024 "FREE(MB)",
ROUND((NVL(NVL(F.FREE,FT.FREE),0)/1024/1024),1) "FREE(MB)",
TO_CHAR(ROUND((NVL(F.FREE,FT.FREE)*100)/T.BYTES,1),999.9) "PCT FREE",
DECODE((CASE WHEN ROUND((NVL(NVL(F.FREE,FT.FREE),0)/1024/1024/1024)) >= 5 THEN 'OK' ELSE 'NOK' END),'OK','OK',DECODE(CONTENTS,'UNDO','OK - UNDO TABLESPACE',DECODE(CONTENTS,'TEMPORARY','OK - TEMP TABLESPACE',DECODE(ROUND((NVL(F.FREE,FT.FREE)*100)/T.BYTES) ,'0','CRITICAL','1','CRITICAL','2','CRITICAL','3','CRITICAL','4','CRITICAL','5','WARNING','6','WARNING','7','WARNING','8','WARNING','9','WARNING','OK'))))STATUS
FROM (SELECT D.TABLESPACE_NAME,
SUM(D.BYTES) BYTES
FROM DBA_DATA_FILES D
GROUP BY TABLESPACE_NAME
UNION
SELECT D.TABLESPACE_NAME,
SUM(D.BYTES) BYTES
FROM DBA_TEMP_FILES D
GROUP BY TABLESPACE_NAME) T,
(SELECT TABLESPACE_NAME,
SUM(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME,
SUM(BYTES_FREE) FREE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) FT,
DBA_TABLESPACES TS
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = FT.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = TS.TABLESPACE_NAME
--AND T.TABLESPACE_NAME = 'BFE01D'
ORDER BY 5;


--PESQUISA DATAFILES

set lines 1000
set pages 2000
col tablespace_name for a30
col name for a60
col status1 for a12
col status2 for a12
col status3 for a12
col online_status for a12
col status_backup for a12
col recover for a7
col error for a20
col "FILE#" for 9999
col "Total(Mb)" for 999999999
select df.tablespace_name,
d.FILE#,
d.NAME,
df.autoextensible,
df.bytes/1024/1024 "Total(Mb)",
d.status status1,
dh.STATUS status2,
df.status status3,
--df.online_status,
b.STATUS status_backup,
dh.RECOVER,
dh.ERROR
from v$datafile d,
dba_data_files df,
v$backup b,
v$datafile_header dh
where d.FILE# = dh.FILE#(+)
and d.FILE# = b.FILE#(+)
and d.FILE# = df.file_id(+)
order by df.tablespace_name,d.NAME;