/* ORACLE DDL trigger */ :::Create audit table CREATE TABLE a | SergeyGurin

/*
ORACLE
DDL trigger
*/

:::Create audit table
CREATE TABLE audit_ddl
(
t_sysdate DATE,
t_osuser VARCHAR2 (255),
t_current_user VARCHAR2 (255),
t_host VARCHAR2 (255),
t_terminal VARCHAR2 (255),
t_owner VARCHAR2 (30),
t_type VARCHAR2 (30),
t_name VARCHAR2 (30),
t_sysevent VARCHAR2 (30)
);

:::Create DDL trigger
CREATE OR REPLACE TRIGGER audit_ddl_trg
AFTER DDL
ON SCHEMA /*ON DATABASE*/
BEGIN
IF (ora_sysevent = 'DROP') THEN
INSERT
INTO audit_ddl
(
t_sysdate,
t_osuser,
t_current_user,
t_host,
t_terminal,
t_owner,
t_type,
t_name,
t_sysevent
)
VALUES
(
SYSDATE,
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'HOST'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent
);
END IF;
END;

:::Get result
SELECT * FROM audit_ddl ORDER BY 1 DESC;