CREATE TABLE AUDIT_DDL ( DDL_DATE date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
IP_ADDRESS VARCHAR2(100),
module varchar2(100),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30),
sql_txt varchar2(4000) )
tablespace tbs_audit;
NEED TO CREATE TRIGGER AT SYS USER TO CATCH STATEMENTS.
create or replace trigger
sys.audit_ddl_trg after ddl on database
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := ”;
n number;
begin
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(
DDL_DATE,osuser,
current_user,host,
terminal,
ip_address,module,
owner,
type,name,sysevent,sql_txt)
values(
sysdate,
sys_context(‘USERENV’,’OS_USER’) ,
sys_context(‘USERENV’,’CURRENT_USER’) ,
sys_context(‘USERENV’,’HOST’) ,
sys_context(‘USERENV’,’TERMINAL’) ,
UTL_INADDR.get_host_name(‘USERENV’),
sys_context(‘USERENV’,’MODULE’) ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end;
/
