使用触发器记录exp执行情况

背景

我们在对用户备份进行检查的时候,通过视图只能对RMAM的备份情况进行检查，没有记录exp备份过程的视图，不过我们可以通过触发器来实现，如下

1. 创建记录表

CONNECT / AS SYSDBA
CREATE TABLE system.logon_audit_table
(
  logon_timestamp  DATE,
  logoff_timestamp DATE,
  sid              NUMBER,
  serial#          NUMBER,
  username         VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuserid         VARCHAR2(30),
  unique_sid       VARCHAR2(24)
)
/

2.创建记录logon的触发器.

CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
  v_machine      VARCHAR2(64);
  v_program      VARCHAR2(64);
  v_unique_sid   VARCHAR2(24);
  v_osuserid     VARCHAR2(30);
  v_sid          NUMBER(4);
  v_serial       NUMBER(4);
  
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine, program
      FROM v$session 
     WHERE serial# != 1
       AND audsid = USERENV('sessionid')
       AND logon_time = (SELECT MAX(logon_time) FROM v$session
                          WHERE audsid = USERENV('sessionid'))
-- Audit export/import utilities only (including export and import DataPump):
       AND (UPPER(program) LIKE 'EXP%' OR UPPER(program) LIKE 'IMP%')
  ;

BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, v_osuserid, v_machine, v_program;
  IF c1%FOUND THEN
     v_unique_sid := DBMS_SESSION.UNIQUE_SESSION_ID;
     INSERT INTO system.logon_audit_table VALUES ( sysdate, null, v_sid,
         v_serial, user, v_machine, v_program, v_osuserid, v_unique_sid);
  END IF;
  CLOSE c1;
END;
/
3. 创建基于logoff的触发器

CREATE OR REPLACE TRIGGER logoffauditing BEFORE LOGOFF ON database
DECLARE
  v_machine      VARCHAR2(64);
  v_program      VARCHAR2(64);
  v_unique_sid   VARCHAR2(24);
  v_osuserid     VARCHAR2(30);
  v_sid          NUMBER(4);
  v_serial       NUMBER(4);
  
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine, program
      FROM v$session
     WHERE serial# != 1
       AND audsid = USERENV('sessionid')
       AND status = 'ACTIVE'
-- Audit export/import utilities only (including export and import DataPump):
       AND (UPPER(program) LIKE 'EXP%' OR UPPER(program) LIKE 'IMP%')
  ;

BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, v_osuserid, v_machine, v_program;
  IF c1%FOUND THEN
     v_unique_sid := DBMS_SESSION.UNIQUE_SESSION_ID;
     UPDATE system.logon_audit_table SET logoff_timestamp=sysdate
       WHERE unique_sid = v_unique_sid;
  END IF;
  CLOSE c1;
END;
/

4.创建公共同义词
CREATE PUBLIC SYNONYM logon_audit_table FOR system.logon_audit_table;
/

5.查看执行exp和imp情况

SELECT * FROM logon_audit_table;
SQL> select * from logon_audit_table;

LOGON_TIMESTAMP      LOGOFF_TIMESTAMP        SID  SERIAL# USERNAME
-------------------- -------------------- ------ -------- -------------
20-JUL-2004 19:16.22 20-JUL-2004 19:17.31    143        5 SCOTT
20-JUL-2004 19:16.59 20-JUL-2004 19:17.12    156        9 SYSTEM
20-JUL-2004 19:18.07 20-JUL-2004 19:18.09    146        4 SYS
20-JUL-2004 19:18.34 20-JUL-2004 19:18.44    146        6 SCOTT

MACHINE       PROGRAM           OSUSERID        UNIQUE_SID
------------- ----------------- --------------- -------------
MYMACHINE     expdp@mymachine   MY_OSNAME       008F00050001
MYMACHINE     exp@mymachine     MY_OSNAME       009C00090001
MYMACHINE     exp@mymachine     MY_OSNAME       009200040001
MYMACHINE     impdp@mymachine   MY_OSNAME       009200060001


6. 如果要清除执行下面的语句

CONNECT / AS SYSDBA
DROP TRIGGER logonauditing;
DROP TRIGGER logoffauditing;
DROP PUBLIC SYNONYM logon_audit_table;
DROP TABLE system.logon_audit_table;