Bom, todo DBA que se prese deve acompanhar o crescimento da base conforme o tempo. Prever o espaço em disco necessário nos próximos meses, saber qual época ocorre maior crescimento da base. Acompanhar de perto objetos críticos que ocupam mais espeço, etc. É claro que a própria Oracle e outros fornecedores possuem ferramentas bastente sofisticadas para fazer algumas destas coisas. É bem verdade que boa parte delas faz em maior ou menor grau algo bem semelhante ao que vou mostrar adiante. No entanto, eu gosto da minha solução, pois eu consigo entendê-la facilmente e modificar para necessidades específicas. No mínimo é um bom exercício de aprendizado.

Vejamos alguns requisitos que eu montei:

Objetos

CREATE TABLESPACE 'DBA_LOG_DADOS'
  DATAFILE '/u01/oradata/nome_da_base/dba_log_dados_01.dbf'
  SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER dba_log IDENTIFIED BY dba
    DEFAULT TABLESPACE dba_log_dados
    QUOTA UNLIMITED ON dba_log_dados
    ACCOUNT LOCK;

GRANT CREATE PROCEDURE TO dba_log;
GRANT CREATE TABLE TO dba_log;

-- Executar como SYSDBA
GRANT SELECT ON dba_objects to dba_log;
GRANT SELECT ON dba_segments to dba_log;
GRANT SELECT ON dba_data_files to dba_log;
GRANT SELECT ON dba_free_space to dba_log;
GRANT SELECT ON dba_tables to dba_log;

CREATE SEQUENCE dba_log.log_seq;

CREATE TABLE dba_log.log(
    id_log      number(10),
    rotina      varchar2(100),
    usuario     varchar2(30) DEFAULT USER,
    inicio      date DEFAULT SYSDATE,
    fim         date,
    CONSTRAINT  log_pk PRIMARY KEY(id_log)
);

CREATE TABLE dba_log.erros (
    id_log      number(10),
    cod_erro    number(10),
    mensagem     varchar2(64),
    data        TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE dba_log.tablespace (
    nome        varchar2(30),
    maximo      number(8) NOT NULL,
    alocado     number(8) NOT NULL,
    utilizado   number(8) NOT NULL,
    livre       number(8) NOT NULL,
    data        date DEFAULT SYSDATE,
    CONSTRAINT tablespaces_pk PRIMARY KEY (nome,data)
);

CREATE OR REPLACE PROCEDURE dba_log.tablespace_load AS
  v_log_seq number(10);
  v_code number(10);
  v_errm varchar2(64);
BEGIN

  SELECT dba_log.log_seq.nextval INTO v_log_seq FROM dual;
  INSERT INTO dba_log.log (id_log, rotina) VALUES (v_log_seq,'tablespace_load');

  INSERT INTO dba_log.tablespace (nome, maximo, alocado, utilizado, livre)
    SELECT
      u.tablespace_name,
      m.maximo,
      m.alocado,
      u.utilizado,
      l.livre
      FROM
        (SELECT tablespace_name, CEIL (SUM (bytes) / 1048576) utilizado
           FROM dba_segments
           GROUP BY tablespace_name) u,
        (SELECT
           tablespace_name,
           CEIL (SUM (bytes) / 1048576) alocado,
           CEIL (SUM (DECODE (autoextensible, 'NO', bytes, maxbytes)) / 1048576) maximo
           FROM dba_data_files
           GROUP BY tablespace_name) m,
        (SELECT
           tablespace_name,
           CEIL (SUM (bytes) / 1048576) livre
           FROM dba_free_space
           GROUP BY tablespace_name) l
      WHERE
        l.tablespace_name = u.tablespace_name AND
        l.tablespace_name = m.tablespace_name
    ;
  UPDATE dba_log.log SET fim = SYSDATE WHERE id_log = v_log_seq;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 64);
      INSERT INTO dba_log.erros (id_log, cod_erro, mensagem) VALUES (v_log_seq, v_code, v_errm);
END;
/

CREATE TABLE dba_log.objeto_qt (
    tipo        varchar2(19),
    esquema     varchar2(30),
    status      varchar2(7),
    qt          number(5) NOT NULL,
    data        date DEFAULT SYSDATE,
    CONSTRAINT objeto_qt_pk PRIMARY KEY (tipo, esquema, status, data)
);

CREATE OR REPLACE PROCEDURE dba_log.objeto_qt_load AS
  v_log_seq number(10);
  v_code number(10);
  v_errm varchar2(64);
BEGIN
  SELECT dba_log.log_seq.nextval INTO v_log_seq FROM dual;
  INSERT INTO dba_log.log (id_log, rotina) VALUES (v_log_seq,'objeto_qt_load');

  INSERT INTO dba_log.objeto_qt (tipo, esquema, status, qt)
    SELECT b.tipo, b.esquema, b.status, b.qt
      FROM
        (SELECT object_type tipo, owner esquema, status FROM dba_objects
           MINUS
           SELECT tipo, esquema, status FROM dba_log.objeto_qt) a,
        (SELECT object_type tipo, owner esquema, status, count(*) qt
           FROM dba_objects
           GROUP BY owner, object_type, status) b
      WHERE
        a.tipo = b.tipo AND
        a.esquema = b.esquema AND
        a.status = b.status
      ORDER BY esquema, tipo, status
   ;

  INSERT INTO dba_log.objeto_qt (tipo, esquema, status, qt)
    SELECT o.tipo, o.esquema, o.status, o.qt
      FROM
        dba_log.objeto_qt q,
        (SELECT object_type tipo, owner esquema, status, count(*) qt
           FROM dba_objects
           GROUP BY owner, object_type, status) o,
        (SELECT tipo, esquema, status, max(data) data
           FROM dba_log.objeto_qt
           GROUP BY tipo, esquema, status) d
      WHERE
        o.tipo = q.tipo AND
        o.tipo = d.tipo AND
        o.esquema = q.esquema AND
        o.esquema = d.esquema AND
        o.status = q.status AND
        o.status = d.status AND
        q.data = d.data AND
        o.qt != q.qt
        order by o.esquema, o.tipo, o.status
  ;
  UPDATE dba_log.log SET fim = SYSDATE WHERE id_log = v_log_seq;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 64);
      INSERT INTO erros (id_log, cod_erro, mensagem) VALUES (v_log_seq, v_code, v_errm);
END;
/

CREATE TABLE dba_log.objeto_invalido (
    tipo        varchar2(19),
    esquema     varchar2(30),
    nome        varchar2(128),
    data        date DEFAULT SYSDATE,
    CONSTRAINT objeto_invalido_pk PRIMARY KEY (tipo, esquema, nome, data)
);

CREATE OR REPLACE PROCEDURE dba_log.objeto_invalido_load AS
  v_log_seq number(10);
  v_code number(10);
  v_errm varchar2(64);

BEGIN
  SELECT dba_log.log_seq.nextval INTO v_log_seq FROM dual;
  INSERT INTO dba_log.log (id_log, rotina) VALUES (v_log_seq,'objeto_invalido_load');
  INSERT INTO dba_log.objeto_invalido (tipo, esquema, nome)
    SELECT object_type tipo, owner esquema, object_name nome
      FROM dba_objects
      WHERE status != 'VALID'
    MINUS
    SELECT tipo, esquema, nome FROM dba_log.objeto_invalido
  ;
  UPDATE dba_log.log SET fim = SYSDATE WHERE id_log = v_log_seq;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 64);
      INSERT INTO dba_log.erros (id_log, cod_erro, mensagem) VALUES (v_log_seq, v_code, v_errm);
END;
/

CREATE TABLE dba_log.objeto_tamanho (
    tipo        varchar2(19),
    tablespace  varchar2(30),
    esquema     varchar2(30),
    nome_part   varchar2(112),
    tamanho     number(8),
    extents     number(5),
    num_reg     number(10),
    data        date DEFAULT SYSDATE,
    CONSTRAINT objetos_tamanho_pk PRIMARY KEY (tipo, esquema, nome_part, data)
);

CREATE OR REPLACE PROCEDURE dba_log.objeto_tamanho_load AS
  v_log_seq number(10);
  v_code number(10);
  v_errm varchar2(64);

BEGIN

  SELECT dba_log.log_seq.nextval INTO v_log_seq FROM dual;
  INSERT INTO dba_log.log (id_log, rotina) VALUES (v_log_seq,'objeto_tamanho_load');

  INSERT INTO dba_log.objeto_tamanho
    (tipo, tablespace, esquema, nome_part, tamanho, extents, num_reg)
    SELECT b.tipo, b.tablespace, b.esquema, b.nome_part, b.tamanho, b.extents, b.num_reg
    FROM
      (SELECT
         segment_type tipo,
         owner esquema,
         NVL2(partition_name, segment_name || '/' || partition_name, segment_name) nome_part
         FROM dba_segments
      MINUS
      SELECT tipo, esquema, nome_part FROM dba_log.objeto_tamanho) a,
      (SELECT
        s.segment_type tipo,
        s.tablespace_name tablespace,
        s.owner esquema,
        NVL2(s.partition_name, s.segment_name || '/' || s.partition_name, s.segment_name) nome_part,
        CEIL(s.bytes/1048576) tamanho,
        s.extents,
        t.num_rows num_reg
        FROM
          dba_segments s,
          dba_tables t
       WHERE
         (s.bytes > 67108864 OR s.extents > 50 OR t.num_rows > 1000000) AND
          s.owner = t.owner (+)AND
          s.segment_name = t.table_name (+)) b
    WHERE
      a.tipo = b.tipo AND
      a.esquema = b.esquema AND
      a.nome_part = b.nome_part
  ;

  INSERT INTO dba_log.objeto_tamanho
    (tipo, tablespace, esquema, nome_part, tamanho, extents, num_reg)
    SELECT o.tipo, o.tablespace, o.esquema, o.nome_part, o.tamanho, o.extents, o.num_reg
      FROM
        dba_log.objeto_tamanho l,
        (SELECT tipo, esquema, nome_part, max(data) data
          FROM dba_log.objeto_tamanho
          GROUP BY tipo, esquema, nome_part) d,
        (SELECT
          s.segment_type tipo,
          s.tablespace_name tablespace,
          s.owner esquema,
          NVL2(s.partition_name, s.segment_name || '/' || s.partition_name, s.segment_name) nome_part,
          CEIL(s.bytes/1048576) tamanho,
          s.extents,
          t.num_rows num_reg
          FROM
            dba_segments s,
            dba_tables t
          WHERE
            (s.bytes > 67108864 OR s.extents > 50 OR t.num_rows > 1000000) AND
            s.owner = t.owner (+)AND
            s.segment_name = t.table_name (+)) o
      WHERE
        l.tipo = d.tipo AND
        l.tipo = o.tipo AND
        l.esquema = d.esquema AND
        l.esquema = o.esquema AND
        l.nome_part = d.nome_part AND
        l.nome_part = o.nome_part AND
        l.data = d.data AND
        (o.tamanho != CEIL(l.tamanho) OR l.extents != o.extents OR l.num_reg != o.num_reg)
      ORDER BY o.esquema, o.tablespace, o.tipo desc
  ;
  UPDATE dba_log.log SET fim = SYSDATE WHERE id_log = v_log_seq;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 64);
      INSERT INTO dba_log.erros (id_log, cod_erro, mensagem) VALUES (v_log_seq, v_code, v_errm);
END;
/

Agendamento

Se você estiver utilizando o Oracle 10g ou superior, deve preferir usar o SCHEDULER:

BEGIN

  DBMS_SCHEDULER.CREATE_WINDOW(
    window_name=>'SYS.MONTH_START_WINDOW',
    resource_plan=>'SYSTEM_PLAN',
    start_date=>SYSTIMESTAMP,
    duration=>numtodsinterval(240, 'minute'),
    repeat_interval=>'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=3',
    end_date=>null,
    window_priority=>'LOW',
    comments=>'Start of the month window for maintenance task'
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DBA_LOG.TABLESPACE_LOAD_MENSAL',
    job_type => 'STORED_PROCEDURE',
    job_action => 'DBA_LOG.TABLESPACE_LOAD',
    schedule_name => 'SYS.MONTH_START_WINDOW',
    enabled => TRUE
  );

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '"DBA_LOG"."TABLESPACE_LOAD_MENSAL"',
    attribute => 'job_priority',
    value => 4
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DBA_LOG.OBJETO_TAMANHO_LOAD_SEMANAL',
    job_type => 'STORED_PROCEDURE',
    job_action => 'DBA_LOG.OBJETO_TAMANHO_LOAD',
    schedule_name => 'SYS.WEEKEND_WINDOW',
    enabled => TRUE
  );

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'DBA_LOG.OBJETO_TAMANHO_LOAD_SEMANAL',
    attribute => 'job_priority',
    value => 4
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DBA_LOG.OBJETO_INVALIDO_LOAD_DIARIO',
    job_type => 'STORED_PROCEDURE',
    job_action => 'DBA_LOG.OBJETO_INVALIDO_LOAD',
    schedule_name => 'SYS.WEEKNIGHT_WINDOW',
    enabled => TRUE
  );

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'DBA_LOG.OBJETO_INVALIDO_LOAD_DIARIO',
    attribute => 'job_priority',
    value => 4
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DBA_LOG.OBJETO_QT_LOAD_DIARIO',
    job_type => 'STORED_PROCEDURE',
    job_action => 'DBA_LOG.OBJETO_QT_LOAD',
    schedule_name => 'SYS.WEEKNIGHT_WINDOW',
    enabled => TRUE
  );

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'DBA_LOG.OBJETO_QT_LOAD_DIARIO',
    attribute => 'job_priority',
    value => 4
  );

END;
/

Se estiver utilizando o Oracle 9i ou inferiro, terá que utilizar os JOBs para agendar a coleta de dados:

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'BEGIN DBA_LOG.OBJETO_QT_LOAD; END;',
    TRUNC(SYSDATE) + 1/24, 'TRUNC(SYSDATE) + 1/24 + 30');
  COMMIT;
END;
/

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'BEGIN DBA_LOG.TABLESPACE_LOAD; END;',
    TRUNC(SYSDATE) + 1/24, 'TRUNC(SYSDATE + 30,''MONTH'') + 1/24');
  COMMIT;
END;
/

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'BEGIN DBA_LOG.OBJETO_TAMANHO_LOAD; END;',
    TRUNC(SYSDATE) + 1/24, 'NEXT_DAY(TRUNC(SYSDATE), ''SATURDAY'') + 1/24');
  COMMIT;
END;
/

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'BEGIN DBA_LOG.OBJETO_INVALIDO_LOAD; END;',
    TRUNC(SYSDATE) + 1/24, 'TRUNC(SYSDATE) + 25/24');
  COMMIT;
END;
/

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'BEGIN DBA_LOG.OBJETO_QT_LOAD; END;',
    TRUNC(SYSDATE) + 1/24, 'TRUNC(SYSDATE) + 25/24');
  COMMIT;
END;
/

Conclusão

Com os dados coletados nas tabelas, você só precisa agora exercitar um pouco do seu conhecimento de SQL para fazer consultas criativas e gerar relatórios dos mais diversos e entregar para o seu chefe no final do ano. Não, adianta nada criar os objetos agora e tentar fazer mágica. Após um anos, você poderá observar com alguma precisão a sazonalidade das aplicações e fazer boas projeções. Que tal começar o ano com um mínimo de coleta de dados na sua base? Quando a turma do ITIL bater na sua porta, algumas coisas já estarão encaminhadas para o seu lado.

Uma resposta

  1. encontrei um erro nasta procedure

    CREATE OR REPLACE PROCEDURE dba_log.tablespace_load AS

Deixe uma resposta