获取某张表上的增删改信息的两种办法

获取某张表上的增删改信息的两种办法 方式1： 我们可以通过触发器的方式获取相关信息 create table tab_monitor as (select sid,username,program,machine,'000.000.000.000'  ipadd,sysdate moditime from v$session where 0=1); 该表分别记录了，删除的用户、机器名、ip及删除时间等 2、创建触发器    create or replace trigger tab_monitor_tri       before delete or update or insert    on ZC.T1       for each row       begin        insert into tab_monitor         select sid,username,program,machine,sys_context('userenv','ip_address'),sysdate         from v$session where audsid = userenv('sessionid');       end;        / 测试 SQL> insert into t1  values('asdfasf'); 1 row created. SQL> commit; Commit complete. SQL> delete from t1 where x='asdfasf'; 1 row deleted. SQL> commit; Commit complete. 查询记录表 select * from tab_monitor;        SID USERNAME PROGRAM      MACHINE  IPADD       MODITIME ---------- -------- ------------------------ -------- --------------- ---------        135 ZC     sqlplus@zc (TNS V1-V3)   zc       18-DEC-24        135 ZC     sqlplus@zc (TNS V1-V3)   zc       18-DEC-24    方式2： 我们可以通过审计的方式获取相关信息 首先要保证数据库开启了审计 SQL> show parameter aud NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest      string /u02/app/oracle/admin/test/adu mp audit_sys_operations      boolean TRUE audit_syslog_level      string audit_trail      string DB unified_audit_common_systemlog      string unified_audit_sga_queue_size      integer 1048576 unified_audit_systemlog      string 开启数据库的表审计  Audit Insert,Update,Delete,Select on ZC.T1 by access whenever successful; 关闭数据库的表审计 noaudit Insert,Update,Delete,Select on ZC.T1;   查询审计的信息 set lines 1000 col OS_USERNAME for a10 col USERNAME for a11 col USERHOST for a10 col TERMINAL for a10 col TIMESTAMP for a20 col obj_name for a10 col OWNER for a10 col ACTION_NAME for a11 col TRANSACTIONID for a16 col sql_text for a50 SELECT USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME='T1' ORDER BY TIMESTAMP; 查询结果： set lines 1000 col OS_USERNAME for a10 col USERNAME for a11 col USERHOST for a10 col TERMINAL for a10 col TIMESTAMP for a20 col obj_name for a10 col OWNER for a10 col ACTION_NAME for a11 col TRANSACTIONID for a16 col sql_text for a50 SELECT USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME='T1' ORDER BY TIMESTAMP; ZC     zc        17-DEC-24     ZC        T1     SELECT SYSTEM     zc        18-DEC-24     ZC        T1   SELECT ZC     WORKGROUP\ 18-DEC-24     ZC        T1   SELECT     APP03 ZC     zc        18-DEC-24     ZC        T1   SELECT ZC     zc        18-DEC-24     ZC        T1   SELECT ZC     zc        18-DEC-24     ZC        T1   SELECT ZC     zc        18-DEC-24     ZC        T1   SELECT ZC     zc        18-DEC-24     ZC        T1   INSERT ZC     zc        18-DEC-24     ZC        T1   INSERT ZC     zc        18-DEC-24     ZC        T1   INSERT ZC     zc        18-DEC-24     ZC        T1   INSERT ZC     zc        18-DEC-24     ZC        T1   DELETE