tecnologia | consultoria | banco de dados

Oracle - Monitorar CREATE / DROP de objetos

Nesta publicação será mostrado um exemplo de Trigger para monitoramento de objetos criados ou deletados do schema em um banco de dados Oracle.

--TABELA DE LOG
CREATE TABLE tb_log_create
(
dt_execucao DATE
,nm_database VARCHAR2(30)
,nm_schema VARCHAR2(30)
,nm_objeto VARCHAR2(30)
,tp_objeto VARCHAR2(30)
,tp_evento VARCHAR2(30)
,nm_usuario_rede VARCHAR2(80)
,nm_equipamento VARCHAR2(80)
)
NOLOGGING;

--TRIGGER
CREATE OR REPLACE TRIGGER trg_log_create
AFTER CREATE
OR DROP
--OR ALTER
--OR TRUNCATE
ON SCHEMA
DECLARE
v_linha hr.tb_log_create%ROWTYPE;
v_dummy INTEGER;
BEGIN
-- DATA DO EVENTO
v_linha.dt_execucao := SYSDATE;
-- BANCO DE DADOS
v_linha.nm_database := upper(sys_context('userenv', 'DB_NAME'));
-- SCHEMA
v_linha.nm_schema := sys_context('userenv', 'CURRENT_USER');
-- NOME DO OBJETO
v_linha.nm_objeto := ora_dict_obj_name;
-- TIPO DE OBJETO
v_linha.tp_objeto := ora_dict_obj_type;
-- TIPO DE EVENTO
v_linha.tp_evento := ora_sysevent;
-- USUARIO DE REDE QUE EXECUTOU O COMANDO
v_linha.nm_usuario_rede := upper(sys_context('userenv', 'OS_USER'));
-- HOSTNAME
v_linha.nm_equipamento := sys_context('userenv', 'HOST');

SELECT COUNT(1)
INTO v_dummy
FROM tb_log_create l
WHERE TRUNC(dt_execucao,'MONTH') = TRUNC(SYSDATE, 'MONTH')
AND l.nm_objeto = v_linha.nm_objeto
AND l.nm_usuario_rede = v_linha.nm_usuario_rede
AND l.tp_objeto = v_linha.tp_evento;
IF v_dummy = 0 THEN
INSERT INTO tb_log_create
VALUES
(
v_linha.dt_execucao
,v_linha.nm_database
,v_linha.nm_schema
,v_linha.nm_objeto
,v_linha.tp_objeto
,v_linha.tp_evento
,v_linha.nm_usuario_rede
,v_linha.nm_equipamento
);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END trg_log_create;
Data publicação: 21:59 27/07/2016

Oracle - Job Scheduler

Nesta publicação será mostrado uma forma para agendamento de execuções automáticas no banco de dados Oracle.

No Oracle, a partir da versão 10G, a package DBMS_JOBS foi depreciada e disponibilizada a package DBMS_SCHEDULER.

Irei mostrar apenas a nova forma de execução, a qual permite consultar históricos de execução e, caso necessário, interromper a execução sem a necessidade de possuir privilégios de DBA (Database Administrator).


Tipos de frequência:
YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY

Tipos de intervalo:
O valor padrão é 1 e o valor máximo é 999

--CRIANDO UM JOB

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'JB_MEU_JOB'
,JOB_TYPE => 'STORED_PROCEDURE'
,JOB_ACTION => 'SCHEMA.PRC_MINHA_PROCEDURE'
,START_DATE => ?FROM_TZ(SYSTIMESTAMP,'-03:00')
,REPEAT_INTERVAL => 'FREQ=SECONDLY;INTERVAL=30'
,AUTO_DROP => FALSE
,COMMENTS => 'JOB QUE EXECUTA A PROCEDURE SCHEMA.PRC_MINHA_PROCEDURE A CADA 30 SEGUNDOS'
,ENABLED => TRUE
);
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'JB_MEU_JOB'
,JOB_TYPE => 'PLSQL_BLOCK'
,JOB_ACTION => '
/*
PROPÓSITO: JOB DE EXEMPLO COM BLOCO PL/SQL
REVISÕES:
Ver Data Autor Descrição
--------- ---------- ------------------ ---------------------------------
1.0 11/05/2016 Leandro Sacramento 1) Criação

*/
DECLARE
V_COUNT NUMBER := NULL;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM VW_MINHA_VIEW
WHERE DS_COLUNA IS NULL;
IF V_COUNT > 0 THEN
PRC_MINHA_PROCEDURE();
END IF;
'
,START_DATE => FROM_TZ(SYSTIMESTAMP,'-03:00')
,REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15; BYMINUTE=0; BYSECOND=0'
,AUTO_DROP => FALSE
,COMMENTS => 'JOB QUE EXECUTA A PROCEDURE SCHEMA.PRC_MINHA_PROCEDURE A CADA 30 SEGUNDOS'
,ENABLED => TRUE
);
END;

--EXECUTAR O JOB

BEGIN
DBMS_SCHEDULER.RUN_JOB('SCHEMA.JB_MEU_JOB',TRUE);
END;

--PARAR EXECUÇÃO DO JOB

BEGIN
DBMS_SCHEDULER.STOP_JOB
(
job_name => 'SCHEMA.JB_MEU_JOB',
force => TRUE
);
END;

--DESABILITAR JOB

BEGIN
DBMS_SCHEDULER.DISABLE(NAME => 'SCHEMA.JB_MEU_JOB', force => TRUE);
END;

--HABILITAR JOB

BEGIN
DBMS_SCHEDULER.ENABLE(NAME => 'SCHEMA.JB_MEU_JOB');
END;

--ALTERANDO UM SCHEDULE

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE
(
NAME => 'SCHEMA.JB_MEU_JOB',
ATTRIBUTE => 'REPEAT_INTERVAL',
VALUE => 'FREQ=DAILY; BYHOUR=15; BYMINUTE=30'
);
END;

--EXCLUINDO UM JOB

BEGIN
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'SCHEMA.JB_MEU_JOB', FORCE => TRUE);
END;

--LIMPAR LOG MAIORES QUE 10 DIAS

BEGIN
DBMS_SCHEDLER.PURGE_LOG(log_history => 10, job_name => 'SCHEMA.JB_MEU_JOB');
END;

--HISTÓRICO DE EXECUÇÕES DOS JOBS

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS;

--MOSTRAR TODOS OS JOBS

SELECT JOB_NAME, STATE, LAST_START_DATE
FROM USER_SCHEDULER_JOBS;
Data publicação: 21:13 11/05/2016

Oracle - Estruturas de Controle

1) Instrução IF

IF <CONDITION> THEN
STATEMENT1;
STATEMENT2;
...
[ELSIF <CONDITION> THEN
STATEMENT1;
...]
[ELSE
STATEMENT1;
...]
END IF;

2) Instrução CASE

CASE <SELECTOR>
WHEN <EXPRESSION1> THEN <STATEMENT1>
WHEN <EXPRESSION2> THEN <STATEMENT2>
WHEN <EXPRESSION3> THEN <STATEMENT3>
...
[ELSE STATEMENT N]
END;

3) Instrução LOOP

LOOP
<STATEMENT1>;
<STATEMENT2>;
<STATEMENT3>;
...
EXIT [WHEN <CONDITION>];
END LOOP;

WHILE <CONDITION> LOOP
<STATEMENT1>;
<STATEMENT2>;
...
END LOOP;
FOR counter IN [REVERSE] lower..upper LOOP
<STATEMENT1>;
<STATEMENT2>;
<STATEMENT3>;
...
END LOOP;

DECLARE
lower NUMBER := 1;
upper NUMBER := 100;
BEGIN
FOR i IN lower..upper LOOP
DBMS_OUTPUT.PUTLINE('Counter: ' || TO_CHAR(i));
END LOOP;
END;

BEGIN
<<loop1>>
FOR i IN 1..5 LOOP
<<loop2>>
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUTLINE('i X j ' || TO_CHAR(i * j));
END LOOP loop2;
END LOOP loop1;
END;
Data publicação: 22:28 09/03/2016
Perfil
Olá jovem Padawan, seja bem vindo! Este site foi criado com o intuito de compartilhar um pouco de conhecimento de Tecnologia da Informação e Business Intelligence.

Linkedin  Youtube

"O sorriso é o símbolo do vencedor." (Masaaki Hatsumi)


Leandro Sacramento, Todos os direitos reservados - 2012 - 2020