Trigger para PROCEDURES e FUNCTIONS

[Renato Menezes Viana]

Como colocar trigger toda vez que QUALQUER procedure ou function for iniciada (begin) ou finalizada (end)?

[dr_gori]

Existe o AUDIT.
Veja esse exemplo do Burleson:
http://www.dba-oracle.com/t_audit_store ... cation.htm

Question: I have a stored procedure and I want to count the number of times that it was executed during specific time frames. How do I audit the execution times for an Oracle stored procedure?

Answer: Oracle auditing can be used to track the invocation of Oracle functions and Oracle stored procedures and you can audit the number of times a stored procedure was called by defining an Oracle audit on the stored procedure:

Selecionar tudo

SQL> audit execute on scott.testing_proc_audit by access; 

Audit succeeded. 


SQL> select 
   os_username,
   username,
   obj_name,owner 
from 
   dba_audit_trail 
where 
   obj_name = 'TESTING_PROC_AUDIT'; 

no rows selected 

--call the stored procedure and trigger the audit operation 

SQL> select testing_proc_audit from dual; 

TESTING_P 
--------- 
04-AUG-14 


SQL> select 
   os_username,
   username,
   obj_name,owner 
from 
   dba_audit_trail 
where 
   obj_name = 'TESTING_PROC_AUDIT';

OS_USERNAME USERNAME OBJ_NAME         OWNER    TIMESTAMP 
----------- -------- ---------------- -------  --------- 
oracle      SCOTT    TEST_PROC_AUDIT  SCOTT    08-AUG-14

As we see, the audit command will place auditing for any sotred procedure on function into the dba_audit_trail view.

[Renato Menezes Viana]

Gori, bom eu tentei, mas claro que não é nada fácil assim.

Segui as instruções e claro não funcionou, apresenta como Audit com sucesso, mas o select sempre apresenta vazio.

Desisti e fiquei mesmo com a solução lusitana da WHO_TRACE_ME.

Abraços

[dr_gori]

Pelo que eu to vendo, isso precisa ser ativado por um DBA antes.
https://docs.oracle.com/cd/E11882_01/se ... TDPSG50000

No meu caso, o parâmetro ta NONE. Tem que estar DB_EXTENDED

Selecionar tudo

SQL> SHOW PARAMETER AUDIT_TRAIL
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

SQL>