在日常的工作过程中,需要记录运维人员的所有数据库修改操作,
可以建立行级的的触发器来完成。
select *from lsb_aa1 select *from lsb_bb1; update lsb_aa1 set ye=7000; delete from lsb_aa1 where id=1 create table lsb_aa1 (id number(20), ye number(12,2)); create table lsb_bb1 (id number(20), ye number(12,2)); insert into lsb_aa1 (id,ye) values(1,5000); insert into lsb_aa1 (id,ye) values(2,6000); create or replace trigger table_aud after insert or delete or update on lsb_aa1 FOR EACH ROW declare begin if UPDATING ('ye') then insert into lsb_bb1 (id,ye) values(:new.id,:new.ye); end if; if INSERTING then insert into lsb_bb1 (id,ye) values(:new.id,:new.ye); end if; if DELETING then insert into lsb_bb1 (id,ye) values(:old.id,:old.ye); end if; end;
但如果这里所需要监视的表是业务表,在日常业务运营过程中会有大量的数据插入和修改,
这些记录都应该认为是正常的业务数据,我们真正需要记录的是【运维人员做的DML语句】
那么怎么做呢,最重要的一个摆了出来
问题:对于众多的数据操作,怎样从数据库中区分修改来自A、程序操作数据库 B、PL/SQL或者其他数据库工具操作数据库
问题还没解决,步骤中可能用到的sql记录在这里
--查看全部session中SQL: select substr(s.username,1,18) username, s.sid,s.serial#,s.machine,y.sql_text from v$session s,v$process p,v$transaction t,v$rollstat r,v$rollname n,v$sql y where s.paddr = p.addr and s.taddr = t.addr (+) and t.xidusn = r.usn (+) and r.usn = n.usn (+) and s.username is not null and s.sql_address=y.address --and s.sid=56 order by s.sid,s.serial#,s.username,s.status
--最重要的是后边的两个字段 select SYS_CONTEXT('USERENV','TERMINAL') terminal, SYS_CONTEXT('USERENV','LANGUAGE') language, SYS_CONTEXT('USERENV','SESSIONID') sessionid, SYS_CONTEXT('USERENV','INSTANCE') instance, SYS_CONTEXT('USERENV','ENTRYID') entryid, SYS_CONTEXT('USERENV','ISDBA') isdba, SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, SYS_CONTEXT('USERENV','CURRENT_USER') current_user, SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, SYS_CONTEXT('USERENV','SESSION_USER') session_user, SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, SYS_CONTEXT('USERENV','DB_NAME') db_name, SYS_CONTEXT('USERENV','HOST') host, SYS_CONTEXT('USERENV','OS_USER') os_user, SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data, SYS_CONTEXT('USERENV','CURRENT_SQL') current_sql, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') client_identifier, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY'), --最重要的是后边的两个字段 SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id from dual