Para mostrar que funciona, segue um exemplo *** simples *** - por favor nÃO O
USE como uma solução completa !!!! - xerocado do link que eu te passei :
=> crio uma tabela para manter a info customizada :
SYS:AS SYSDBA@XE:SQL>create table HR.AUDIT_DDL
(RUN_DATE date,
osuser varchar2(33),
current_user varchar2(33),
login_user varchar2(33),
host varchar2(33),
terminal varchar2(33),
owner varchar2(33),
OBJECT_TYPE varchar2(33),
object_name varchar2(33),
sysevent clob,
PROGRAM varchar2(33),
MACHINE varchar2(33)
);
==> dou as permissões :
SYS:AS SYSDBA@XE:SQL>grant select on v_$open_cursor to HR;
ConcessÒo bem-sucedida.
SYS:AS SYSDBA@XE:SQL>grant select on v_$sql to hr;
ConcessÒo bem-sucedida.
SYS:AS SYSDBA@XE:SQL>grant select on v_$SESSION to HR;
ConcessÒo bem-sucedida.
==> crio a trigger , no caso conectado como o HR :
HR:@XE:SQL> create or replace trigger HR.TRG_AUDIT_DDL before ddl on schema
2 declare
3 v_count varchar2(200);
4 l_sysevent varchar2(25);
5 v_program varchar2(25);
6 v_machine varchar2(25);
7 BEGIN
8 --select count(*) into v_count from important_objects where object_name=
ora_dict_obj_name;
9 -- if (v_count>0)
10 -- then
11 select program,machine into v_program,v_machine from v$session where au
dsid = sys_context( 'userenv', 'sessionid' );
12 select ora_sysevent into l_sysevent from dual;
13 if ( l_sysevent = 'ALTER' ) then
14 insert into HR.AUDIT_DDL(RUN_DATE, osuser,current_user,login_user,hos
t,terminal,owner,OBJECT_TYPE,object_name,sysevent,PROGRAM,MACHINE)
15 select sysdate,
16 sys_context('USERENV','OS_USER') ,
17 sys_context('USERENV','CURRENT_USER') ,
18 ora_login_user,
19 sys_context('USERENV','HOST') ,
20 sys_context('USERENV','TERMINAL') ,
21 ora_dict_obj_owner,
22 ora_dict_obj_type,
23 ora_dict_obj_name, B.sql_fulltext,v_program,v_machine
24 from v$open_cursor A,V$SQL b
25 where upper(A.sql_text) like 'ALTER%'
26 and regexp_like(A.sql_text,ora_dict_obj_name,'i')
27 AND A.SQL_ID=B.SQL_ID
28 --and last_sql_active_time>sysdate - 15/1440
29 ;
30 else
31 insert into HR.AUDIT_DDL(RUN_DATE, osuser,current_user,login_user,hos
t,terminal,owner,OBJECT_TYPE,object_name,sysevent,PROGRAM,MACHINE)
32 values(
33 sysdate,
34 sys_context('USERENV','OS_USER') ,
35 sys_context('USERENV','CURRENT_USER') ,
36 ora_login_user,
37 sys_context('USERENV','HOST') ,
38 sys_context('USERENV','TERMINAL') ,
39 ora_dict_obj_owner,
40 ora_dict_obj_type,
41 ora_dict_obj_name,
42 ora_sysevent,v_program,v_machine
43 );
44 end if;
45* END;
HR:@XE:SQL>/
Gatilho criado.
==> okdoc, vou fazer uns DDLs e DCLs :
HR:@XE:SQL>grant select on regions to system;
ConcessÒo bem-sucedida.
HR:@XE:SQL>create table TT(x date);
Tabela criada.
HR:@XE:SQL>desc countries
Nome Nulo?
Tipo
------------------------------------------------------------------------- -----
--- -------------------------------------------------
COUNTRY_ID NOT N
ULL CHAR(2)
COUNTRY_NAME
VARCHAR2(40)
REGION_ID
NUMBER
HR:@XE:SQL>alter table TT add z varchar2(10);
Tabela alterada.
HR:@XE:SQL>select * from audit_ddl;
RUN_DATE OSUSER CURRENT_USER LOG
IN_USER HOST
-------- --------------------------------- --------------------------------- ---
------------------------------ ------------
TERMINAL OWNER OBJECT_TYPE
OBJECT_NAME
--------------------------------- ---------------- -----------------------------
---- ------------------------------
SYSEVENT
PROGRAM
--------------------------------------------------------------------------------
---------------------------------
MACHINE
---------------------------------
15/03/16 ITLJLC_XYZ\jose_chiappa HR HR
ITLJLC_XYZ\B
RZBEL177L
BRZBEL177L HR OBJECT PRIVILEGE
REGIONS
GRANT
sqlplus.exe
ITLJLC_XYZ\BRZBEL177L
15/03/16 ITLJLC_XYZ\jose_chiappa HR HR
ITLJLC_XYZ\B
RZBEL177L
BRZBEL177L HR TABLE
TT
CREATE
sqlplus.exe
ITLJLC_XYZ\BRZBEL177L
15/03/16 ITLJLC_XYZ\jose_chiappa HR HR
ITLJLC_XYZ\B
RZBEL177L
BRZBEL177L HR TABLE
TT
alter table TT add z varchar2(10)
sqlplus.exe
ITLJLC_XYZ\BRZBEL177L
HR:@XE:SQL>
[]s
Chiappa
