• 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
  • 相关阅读:
    观察 HTML中id和name 的差异,被微软忽悠过的同学自动举手
    ScottGu中文博客: 新捆绑和缩小支持(ASP.NET 4.5系列)
    在证书存储区中找不到清单签名证书
    HTML中的转义字符
    SQL 查询本周/本月/本季度/本年的数据
    sql STUFF用法
    jQuery UI Autocomplete 体验
    你必须知道的ADO.NET(二)了解.NET数据提供程序
    ASP.NET MVC的全球化方案
    MongoDB是什么?
  • 原文地址:https://www.cnblogs.com/adamgq/p/12696475.html
Copyright © 2020-2023  润新知