ORACLE11G system level triggers to track and monitor drop errors

Preface:
DB has a table in the data is always chaotic, guess is experienced drop, create data synchronization operation, but now nobody knows where to operate, so ready to make a trigger to record which application server that DB account operation.

3, System level triggers 3.1 Triggering events

Includes various DDL operations, as well as various database events, DDL including Create, alter, DROP, rename, Grant, REVOKE, audit, Noaudit, Commit, truncate, analyze, associate Statistics, disassociate Statistis. Trigger time can be before or after

3.2 Creating a table for Trigger records

--Create tableCREATE TABLE Z_trig_sys (LT DATE, sid number, serial# number,Username VARCHAR2 ( -), Osuser VARCHAR2 ( -), Machine VARCHAR2 ( +), Terminal VARCHAR2 ( -), object_name VARCHAR2 ( $), Ora_sysevent VARCHAR2 ( $), Program VARCHAR2 ( -), SQLText VARCHAR2 (4000), Status VARCHAR2 ( -), Client_ip VARCHAR2 ( -), Ora_dbname VARCHAR2 ( -), Ora_client_ip_address VARCHAR2 ( -));--ADD comments to the columnsComment onColumn z_trig_sys.lt is' Entry time '; comment onColumn Z_trig_sys.sid is' The current sessionID'; Comment onColumn Z_trig_sys.serial#   is' Sid's serial number, order self-increment '; Comment onColumn Z_trig_sys.username is' Logged in user name '; Comment onColumn Z_trig_sys.osuser is' operator's OS system '; Comment onColumn Z_trig_sys.machine is' operator's machine name '; Comment onColumn Z_trig_sys.object_name is' Operation object name '; Comment onColumn Z_trig_sys.ora_sysevent is' Operational events '; Comment onColumn Z_trig_sys.sqltext is' Executed SQL fragment '; comment onColumn Z_trig_sys.client_ip is' Client IP '; Comment onColumn Z_trig_sys.ora_dbname is' Executed database '; Comment onColumn Z_trig_sys.ora_client_ip_address is' Client IP address ';

Original Blog Address: http://blog.csdn.net/mchdba/article/details/49643235, declined reprint

3.3 Setting up system level triggers

Create or Replace trigger trig_systemafter drop on Databasebegin if ora_login_user!= ' system ' then insert into Z_trig_              SYS (LT, sid, Serial#, username           , Osuser, machine, terminal, object_name                , Ora_sysevent, program, SQLTEXT, status , Client_ip, Ora_dbname, ora_client_ip_address) SELECT Sys               Date, S.sid, s.serial#, S.username, S.osuser, S.machine, S.terminal, S.program, Ora_dict_obj_name, Ora_syseve              NT, ' drop object on database ', ' ', Sys_context (' Userenv ', ' ip_address '),  Ora_database_name,            Ora_client_ip_address from V$sql Q, v$session s where s.audsid= (select Userenv (' SESSIONID ') f           Rom dual) and s.prev_sql_addr=q.address and s.prev_hash_value = Q.hash_value;           --commit;   --and Sys_context (' Userenv ', ' ip_address ')! = ' 192.168.180.106 '; End If;end Trig_system;

3.4, debug error

Ora-00604:error occurred at recursive SQL level 1
Ora-04092:cannot COMMIT in a trigger

Remove the commit from the trigger;

4. View the monitoring results as follows:

Sql> CREATE TABLE Zz_back (ID number); Table createdsql> INSERT INTO zz_back values (1); 1 row insertedsql> commit; Commit completesql> drop table zz_back;                                                           Table droppedsql> SELECT * from Z_trig_sys;lt SID serial# USERNAME osuser                                                                      Machine TERMINAL object_name                                                                     Ora_sysevent                                                                          Program SQLText                                                    STATUS client_ip Ora_dbname ora_client_ip_address---------------------- --------- ------------------------------ ------------------------------------------------------------------------------------------------ ---------------- --------------------------------------------------------------- ----------------- -------------------------------------------------------------------------------- -------------- -------------------------------------------------- ------------------------------------------------------------- ------------------- ------------------------------ ------------------------------------------------------------ -- ---------------------------------------------------------- -----------------------------------------------------                                                    -------2015/11/4 1 787 63023 POWERDESK Administrator                                                                     Workgroup\win-timman Win-timman Plsqldev.exe                                                             Zz_back DROP Drop object on DatabasE 192.168.120.181  Powerdes sql>

See there are records, but some have not taken value, such as ora_client_ip_address, etc., to continue to improve

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ORACLE11G system level triggers to track and monitor drop errors