ORACLE AUDIT FOR ALTER COMMAND

CREATE TABLE DBA_AUDIT_TAB_KSH (USERNAME VARCHAR2(10), SQL_TEXT VARCHAR2(2000),TIMESTAMP DATE);

CREATE OR REPLACE TRIGGER DBA_AUDIT_KSH

BEFORE ALTER ON SCHEMA

DECLARE

sql_text ora_name_list_t;

stmt VARCHAR2(2000);

n integer;

dt date;

BEGIN

null;

IF (ora_dict_obj_type IN ( 'TABLE') )

then

n:= ora_sql_txt(sql_text);

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

dt:=TO_DATE(SYSDATE,'DD-MM-YYYY HH24:MI:SS');

INSERT INTO DBA_AUDIT_TAB_KSH (username,sql_text,timestamp) VALUES (user,stmt,dt);

END IF;

END DBA_AUDIT_KSH;

/