Ddl Trigger

How to create a DDL Trigger By: Rahul Gupta [email protected] http://onlineerpdba.blogspot.com/

Note: Very good for monitoring all the DDL at your database

SQL > Conn / as sysdba SQL > CREATE TABLE perfstat.STATS$DDL_LOG ( USER_NAME VARCHAR2(25 BYTE), DDL_DATE DATE, DDL_TYPE VARCHAR2(25 BYTE), OBJECT_TYPE VARCHAR2(25 BYTE), OWNER VARCHAR2(25 BYTE), OBJECT_NAME VARCHAR2(30 BYTE), TERMINAL_NAME VARCHAR2(40 BYTE), IP_ADDRESS VARCHAR2(30 BYTE) ); Table created.

SQL > grant all on perfstat.STATS$DDL_LOG to apps; Grant succeeded.

SQL > conn apps Enter password: Connected. SQL > create synonym DDL_LOG for perfstat.STATS$DDL_LOG; Synonym created.

SQL > conn / as sysdba Connected.

SQL >

CREATE OR REPLACE TRIGGER DDLTrigger AFTER DDL ON DATABASE DECLARE v_terminal_name varchar2(40); v_ip_address varchar2(40); BEGIN SELECT SYS_CONTEXT ('USERENV', 'TERMINAL') INTO v_terminal_name FROM DUAL; SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO v_ip_address FROM DUAL; insert into perfstat.stats$ddl_log ( user_name, ddl_date, ddl_type, object_type, owner, object_name, terminal_name , ip_address ) VALUES ( ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, v_terminal_name, v_ip_address ); END; / Trigger created.

SQL > /*

conn apps Enter password: Connected.

Sample Output */

SQL > USER_NAME ---------APPS APPS

> select * from DDL_LOG; DDL_DATE --------16-JUN-08 16-JUN-08

DDL_TYPE ---------CREATE DROP

OBJECT_TYPE --------------TABLE TABLE

OWNER -------APPS APPS

OBJECT_NAME -------------------MOHIT MOHIT

TERMINAL_NAME -------------------LGDBA LGDBA

IP_ADDRESS --------------10.102.120.118 10.102.120.118

==================================================================================================================