GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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
/

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.