• Oracle记录运维人员的操作


    在日常的工作过程中,需要记录运维人员的所有数据库修改操作,

    可以建立行级的的触发器来完成。

    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
  • 相关阅读:
    正则表达式30分钟入门教程
    Python的神奇方法指南
    Python 2.7教程
    javaweb开发.常用的第三方包
    javaweb开发.页面中文乱码问题
    javaweb开发.eclipse使用小常识
    javaweb开发3.基于Servlet+JSP+JavaBean开发模式的用户登录注册
    ionic3使用第三方图标
    json-server使用及路由配置
    javaweb开发2.新建一个javaweb项目
  • 原文地址:https://www.cnblogs.com/adamgq/p/12696475.html
Copyright © 2020-2023  润新知