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

方式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

方式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;

查询结果：