Criar views para monitorar de forma simples as tablespaces (datafile e tempfile)
Eu criei um usuário chamado sisbd (usuário genérico para todos os dbas -sistema do banco de dados).
grant select on dba_data_files to sisbd;
grant select on dba_temp_files to sisbd;
grant select on DBA_FREE_SPACE to sisbd;
grant select on DBA_tablespaces to sisbd;
grant select on dba_tablespace_groups to sisbd;
grant select on V_$TEMP_SPACE_HEADER to sisbd;
CREATE OR REPLACE FORCE VIEW "SISBD"."VW_TBS_LIVRE" ("TS", "TOTAL", "LIVRE", "CONTIGUO", "PERCENTUAL_LIVRE", "NRO_EXTENTS")
AS
SELECT A.TS,
A.TOTAL,
TRUNC(NVL(B.LIVRE,0),2) LIVRE,
TRUNC(NVL(B.CONTIGUO,0),2) CONTIGUO,
TRUNC(NVL(TRUNC((CONTIGUO*100)/TOTAL,2),0),2) PERCENTUAL_LIVRE,
TRUNC((NVL(B.CONTIGUO,0) *1024*1024/NVL(C.NEXT_EXTENT,c.INITIAL_EXTENT)),0) NRO_EXTENTS
FROM
(SELECT TABLESPACE_NAME TS,
SUM(BYTES)/1024/1024 TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A,
(SELECT TABLESPACE_NAME TS,
SUM(BYTES)/1024/1024 LIVRE,
MAX(BYTES)/1024/1024 CONTIGUO
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B,
DBA_TABLESPACES C
WHERE A.TS = B.TS (+)
AND A.TS = C.TABLESPACE_NAME
ORDER BY NRO_EXTENTS,
CONTIGUO
/
CREATE OR REPLACE FORCE VIEW "SISBD"."VW_TBS_TEMP_LIVRE" ("GROUP_NAME", "TS", "TOTAL", "LIVRE", "CONTIGUO", "PERCENTUAL_LIVRE", "NRO_EXTENTS")
AS
SELECT NVL(D.GROUP_NAME,'SEM GRUPO'),
A.TS,
A.TOTAL,
TRUNC(NVL(B.LIVRE,0),2) LIVRE,
-1 CONTIGUO,
TRUNC(NVL(TRUNC((LIVRE*100)/TOTAL,2),0),2) PERCENTUAL_LIVRE,
TRUNC((NVL(B.LIVRE,0) *1024*1024/NVL(C.NEXT_EXTENT,c.INITIAL_EXTENT)),0) NRO_EXTENTS
FROM
(SELECT TABLESPACE_NAME TS,
SUM(BYTES)/1024/1024 TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME
) A,
(SELECT TABLESPACE_NAME TS,
SUM(BYTES_FREE)/1024/1024 LIVRE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
) B,
DBA_TABLESPACES C,
dba_tablespace_groups D
WHERE A.TS = B.TS (+)
AND A.TS = C.TABLESPACE_NAME
AND A.TS = D.TABLESPACE_NAME(+)
ORDER BY NRO_EXTENTS,
LIVRE
/
