CREATE TABLE db_ddl
(ddl_time VARCHAR2(30),
session_id NUMBER,
os_user VARCHAR2(200),
ip_address VARCHAR2(200),
terminal VARCHAR2(200),
host VARCHAR2(200),
user_name VARCHAR2(30),
ddl_type VARCHAR2(30),
object_type VARCHAR2(18),
owner VARCHAR2(30),
object_name VARCHAR2(128),
sql_text VARCHAR2(4000))
/
CREATE OR REPLACE TRIGGER tri_ddl
AFTER
DDL
ON SCHEMA
DECLARE
sql_text ora_name_list_t;
i number(10);
state_sql VARCHAR2 (4000);
BEGIN
begin
FOR i IN 1 .. ora_sql_txt (sql_text)
LOOP
state_sql := state_sql || sql_text (i);
END LOOP;
exception
when others then
sp_sqllog('TRI_DDL ERROR:' || ora_dict_obj_name || sqlerrm);
end;
state_sql := SUBSTRB (state_sql, 1, 4000);
if not state_sql like 'ALTER%COMPILE%' then
INSERT INTO db_ddl
(ddl_time,
session_id,
os_user,
ip_address,
terminal,
HOST,
user_name, ddl_type,
object_type, owner,
object_name, sql_text
)
VALUES (TO_CHAR (SYSDATE, 'yyyymmddhh24miss'),
SYS_CONTEXT ('USERENV', 'SESSIONID'),
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
SYS_CONTEXT ('USERENV', 'HOST'),
ora_login_user, ora_sysevent,
ora_dict_obj_type, ora_dict_obj_owner,
ora_dict_obj_name, state_sql
);
end if;
EXCEPTION
WHEN OTHERS
THEN
sp_sqllog('TRI_DDL ERROR:' || sqlerrm);
END;
/