Oracle11g system-level trigger to track drop misoperations

Oracle11g system-level trigger to track drop misoperations

Preface:
The data in a table in the db is always disordered. I guess I 've experienced data synchronization operations such as drop and create, but no one knows where to operate it now, therefore, we are going to make a trigger to record the database account of the application server that operated the operation.

3. System-level trigger 3.1 trigger event

Includes various DDL operations and various database events. ddl includes create, alter, drop, rename, grant, revoke, audit, noaudit, commit, truncate, analyze, associate statistics, and disassociate statistis. The trigger time can be before or after.

3.2 Create a trigger record table

-- Create tablecreate table Z_TRIG_SYS (lt DATE, sid NUMBER, serial # NUMBER, username VARCHAR2 (30), osuser VARCHAR2 (64), machine VARCHAR2 (32), terminal VARCHAR2 (16 ), object_name VARCHAR2 (200), ora_sysevent VARCHAR2 (200), program VARCHAR2 (64), sqltext VARCHAR2 (4000), status VARCHAR2 (30), client_ip VARCHAR2 (60 ), ora_dbname VARCHAR2 (60), ora_client_ip_address VARCHAR2 (60); -- Add comments to the columns comment on column Z_TRIG_SYS.lt is 'entry time '; comment on column Z_TRIG_SYS.sid is 'current session id'; comment on column Z_TRIG_SYS.serial # is 'sid serial number, sequence incrementing '; comment on column Z_TRIG_SYS.username is 'login Username '; comment on column Z_TRIG_SYS.osuser is 'operator's OS system'; comment on column Z_TRIG_SYS.machine is 'operator's machine name'; comment on column Z_TRIG_SYS.object_name is 'Operation object name '; comment on column Z_TRIG_SYS.ora_sysevent is 'Operation event'; comment on column Z_TRIG_SYS.sqltext is 'execution SQL frag'; comment on column Z_TRIG_SYS.client_ip is 'client ip '; comment on column Z_TRIG_SYS.ora_dbname is 'database executed '; comment on column Z_TRIG_SYS.ora_client_ip_address is 'client IP address ';

Original blog address: http://blog.csdn.net/mchdba/article/details/49643235. Thank You For Your reprinting.

3.3 create a system-level trigger

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 sysdate,               s.SID,                  s.SERIAL#,                  s.USERNAME,                  s.OSUSER,               s.MACHINE,               s.TERMINAL,               s.PROGRAM,               ora_dict_obj_name,               ora_sysevent,               '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') from 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 debugging Error

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

Remove 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> 

We can see that there are records, but some have not obtained the values, such as ora_client_ip_address, which need to be improved.