15:53:53 SQL > CREATE TABLE perfstat.STATS$DDL_LOG
15:55:30 2 (
15:55:30 3 USER_NAME VARCHAR2(25 BYTE),
15:55:30 4 DDL_DATE DATE,
15:55:30 5 DDL_TYPE VARCHAR2(25 BYTE),
15:55:30 6 OBJECT_TYPE VARCHAR2(25 BYTE),
15:55:30 7 OWNER VARCHAR2(25 BYTE),
15:55:30 8 OBJECT_NAME VARCHAR2(30 BYTE),
15:55:30 9 TERMINAL_NAME VARCHAR2(40 BYTE),
15:55:30 10 IP_ADDRESS VARCHAR2(30 BYTE)
15:55:30 11 );
Table created.
15:55:32 SQL > grant all on perfstat.STATS$DDL_LOG to apps;
Grant succeeded.
15:55:38 SQL > conn apps
Enter password:
Connected.
15:55:45 SQL > create synonym DDL_LOG for perfstat.STATS$DDL_LOG;
Synonym created.
15:55:53 SQL > conn / as sysdba
Connected.
15:55:57 SQL > CREATE OR REPLACE TRIGGER DDLTrigger
15:56:21 2 AFTER DDL ON DATABASE
15:56:21 3 DECLARE
15:56:21 4 v_terminal_name varchar2(40);
15:56:21 5 v_ip_address varchar2(40);
15:56:21 6 BEGIN
15:56:21 7 SELECT SYS_CONTEXT ('USERENV', 'TERMINAL')
15:56:21 8 INTO v_terminal_name
15:56:21 9 FROM DUAL;
15:56:21 10 SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
15:56:21 11 INTO v_ip_address
15:56:21 12 FROM DUAL;
15:56:21 13 insert into
15:56:21 14 perfstat.stats$ddl_log
15:56:21 15 (
15:56:21 16 user_name,
15:56:21 17 ddl_date,
15:56:21 18 ddl_type,
15:56:21 19 object_type,
15:56:21 20 owner,
15:56:21 21 object_name,
15:56:21 22 terminal_name ,
15:56:21 23 ip_address
15:56:21 24 )
15:56:21 25 VALUES
15:56:21 26 (
15:56:21 27 ora_login_user,
15:56:21 28 sysdate,
15:56:21 29 ora_sysevent,
15:56:21 30 ora_dict_obj_type,
15:56:21 31 ora_dict_obj_owner,
15:56:21 32 ora_dict_obj_name,
15:56:21 33 v_terminal_name,
15:56:21 34 v_ip_address
15:56:21 35 );
15:56:21 36
15:56:21 37 END;
15:56:22 38 /
Trigger created.
16:01:36 SQL > conn apps
Enter password:
Connected.
16:02:36 SQL > select * from DDL_LOG;
USER_NAME DDL_DATE DDL_TYPE OBJECT_TYPE OWNER OBJECT_NAME TERMINAL_NAME IP_ADDRESS
---------- --------- ---------- --------------- -------- -------------------- -------------------- ----------------
APPS 16-JUN-08 CREATE TABLE APPS MOHIT LGDBA 10.102.120.118
APPS 16-JUN-08 DROP TABLE APPS MOHIT LGDBA 10.102.120.118
==============================
