Oracle对数据库行和数据库的监控

前言：

  Oracle对表的监控分为数据行修改DML的监控、对表的DDL监控
 

1、对表的DML监控（数据的增删改）

-- 创建测试表
create table tab_test01(
id varchar2(100) default sys_guid(),
name varchar2(100),
insert_date date default sysdate
);

drop table tab_test01_his purge;

create table tab_test01_his(
id_his varchar2(100) ,
name_his varchar2(100),
insert_date_his date ,
insert_date date,
dml_type varchar2(10)
);

insert into tab_test01(name)
select 'bbbbb' from dual;
commit;

select * from tab_test01_his;

select * from tab_test01;

update tab_test01 set name ='dddd' where id ='1DF67A1619850FA3E06540BE7B5E5EA3';
commit;

-- 创建触发器，
create or replace trigger tri_tab_test01
after insert or update or delete on c##djj.tab_test01
    for each row
  begin
    if inserting then
      insert into tab_test01_his values(:new.id,:new.name,:new.insert_date, sysdate, 'insert');
    elsif updating then
      insert into tab_test01_his values(:old.id,:old.name,:old.insert_date, sysdate, 'update');
    else
      insert into tab_test01_his values(:old.id,:old.name,:old.insert_date, sysdate, 'delete');
    end if;
  end;

CREATE OR REPLACE TRIGGER your_trigger_name
AFTER INSERT OR UPDATE OR DELETE ON your_table_name
FOR EACH ROW
DECLARE
    action_taken VARCHAR2(30);
BEGIN
    IF INSERTING THEN
        action_taken := 'INSERT';
    ELSIF UPDATING THEN
        action_taken := 'UPDATE';
    ELSE
        action_taken := 'DELETE';
    END IF;
 
    INSERT INTO audit_table (table_name, row_id, action, user_name, timestamp)
    VALUES ('YOUR_TABLE_NAME', :NEW.id, action_taken, USER, SYSTIMESTAMP);
END;

2、对表的DDL操作的监控

-- 创建用户和授权
#需要使用sys用户授权

create user c##djj identified by abcABC123##  
default tablespace org12c  
temporary tablespace org12c_temp;  

GRANT CONNECT TO c##djj;
GRANT RESOURCE TO c##djj;
GRANT CREATE VIEW TO c##djj;
GRANT UNLIMITED TABLESPACE TO c##djj;
GRANT SELECT ANY TABLE TO c##djj;
ALTER USER c##djj DEFAULT ROLE ALL;
GRANT select on SYS.V_$OPEN_CURSOR TO c##djj;

-- 建日志表
DROP SEQUENCE SEQ_DDL_VERSION;
CREATE SEQUENCE SEQ_DDL_VERSION INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;

CREATE TABLE TB_SYSTEM_DDL_LOGS 
(
   EVENT_ID             VARCHAR2(32)         DEFAULT SYS_GUID() NOT NULL,
   EVENT_NAME           VARCHAR2(20),
   TERMINAL             VARCHAR2(50),
   DB_NAME              VARCHAR2(50),
   OBJECT_NAME          VARCHAR2(30),
   OBJECT_NAME_LIST     VARCHAR(300),
   OBJECT_OWNER         VARCHAR2(30),
   OBJECT_TYPE          VARCHAR2(20),
   IS_ALTER_COLUMN      VARCHAR(10),
   IS_DROP_COLUMN       VARCHAR(10),
   SQL_ID               VARCHAR(13),
   SQL_TEXT             CLOB,
   CURRENT_USER         VARCHAR(30),
   CURRENT_USERID       NUMBER,
   SESSION_USER         VARCHAR(10),
   SESSION_USERID       NUMBER,
   PROXY_USER           VARCHAR(30),
   PROXY_USERID         NUMBER,
   CURRENT_SCHEMA       VARCHAR(30),
   HOST                 VARCHAR(100),
   OS_USER              VARCHAR(60),
   IP_ADDRESS           VARCHAR(32),
   DDL_TIME             DATE                 DEFAULT SYSDATE,
   SESSION_ID           VARCHAR(32),
   VERSION_NO           NUMBER,
   CONSTRAINT PK_TB_SYSTEM_DDL_LOGS PRIMARY KEY (EVENT_ID)
);

COMMENT ON TABLE TB_SYSTEM_DDL_LOGS IS
'【数据库日志】DDL日志表';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.EVENT_ID IS
'事件ID自动生成';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.EVENT_NAME IS
'事件名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.TERMINAL IS
'客户端操作系统终端的名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.DB_NAME IS
'数据库名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_NAME IS
'DDL发生的对象名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_NAME_LIST IS
'对象列表';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_OWNER IS
'DDL发生对象的宿主';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OBJECT_TYPE IS
'对象类别';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IS_ALTER_COLUMN IS
'当列被修改的时候为真，否则为假 ';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IS_DROP_COLUMN IS
'当列被DROP的时候为真，否则为假 ';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SQL_ID IS
'SQL_ID';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SQL_TEXT IS
'SQL语句';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_USER IS
'当前SESSION拥有权限的用户的名称（比如说当前SESSION是SYS,但是正在执行system.myproc，那么current_user就是system）';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_USERID IS
'当前SESSION拥有的权限的用户的ID';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_USER IS
'session所属的用户名';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_USERID IS
'当前SESSION所属的用户id';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.PROXY_USER IS
'打开当前SESSION的用户的名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.PROXY_USERID IS
'打开当前SESSION的用户的ID';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.CURRENT_SCHEMA IS
'当前SESSION缺省的SCHEMA名称，可以用SESSION SET CURRENT_SCHEMA语句修改';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.HOST IS
'客户端的主机名称';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.OS_USER IS
'客户端的操作系统用户名';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.IP_ADDRESS IS
'客户端的IP地址';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.DDL_TIME IS
'修改时间';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.SESSION_ID IS
'SESSION_ID';

COMMENT ON COLUMN TB_SYSTEM_DDL_LOGS.VERSION_NO IS
'版本号';

select * from TB_SYSTEM_DDL_LOGS

-- 创建触发器，在dba的用户下执行
CREATE OR REPLACE TRIGGER TRIG_MONITOR_SYSTEM_DDL
AFTER DDL ON DATABASE
/**
 * 创建时间：2024年7月24日 16:46:38
 * 描述：监控DDL操作并将DDL操作及DDL语句记录到日志表中
   */
   DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
     TR_EVENT_ID VARCHAR2(32);
     TR_TERMINAL VARCHAR2(50);
     TR_IPADDR VARCHAR2(30);
     TR_CUR_USER VARCHAR2(30);
     TR_CUR_USERID NUMBER;
     TR_SE_USER VARCHAR2(30);
     TR_SE_USERID NUMBER;
     TR_PROXY_USER VARCHAR2(30);
     TR_PROXY_USERID NUMBER;
     TR_CUR_SC VARCHAR2(30);
     TR_HOST VARCHAR2(100);
     TR_OS_USER VARCHAR2(60);
 TR_SESSIONID VARCHAR2(32);
     TR_SQL_ID VARCHAR2(13);
     TR_SQL VARCHAR2(60);
     TR_VERSION_NO NUMBER;
     TR_N NUMBER;
     TR_STMT CLOB := NULL;
     TR_SQL_TEXT ORA_NAME_LIST_T;
   BEGIN
     TR_EVENT_ID := SYS_GUID();
     --获取用户信息
     SELECT NVL(SYS_CONTEXT('USERENV','TERMINAL'),''),--客户端操作系统终端的名称
            NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),''),--客户端操作系统终端的名称
            NVL(SYS_CONTEXT('USERENV','CURRENT_USER'),''),--当前SESSION拥有权限的用户的名称（比如说当前SESSION是SYS,但是正在执行SYSTEM.MYPROC，那么CURRENT_USER就是SYSTEM）
            NVL(SYS_CONTEXT('USERENV','CURRENT_USERID'),''),--当前SESSION拥有的权限的用户的ID
            NVL(SYS_CONTEXT('USERENV','SESSION_USER'),''),--SESSION所属的用户名
            NVL(SYS_CONTEXT('USERENV','SESSION_USERID'),''),--当前SESSION所属的用户ID
            NVL(SYS_CONTEXT('USERENV','PROXY_USER'),''),--打开当前SESSION的用户的名称
            NVL(SYS_CONTEXT('USERENV','PROXY_USERID'),''),--打开当前SESSION的用户的ID
        NVL(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),''),--当前SESSION缺省的SCHEMA名称
            NVL(SYS_CONTEXT('USERENV','HOST'),''),--客户端的主机名称
            NVL(SYS_CONTEXT('USERENV','OS_USER'),''),--客户端的操作系统用户名
            NVL(SYS_CONTEXT('USERENV','SESSIONID'),'')--SESSION的ID
     INTO TR_TERMINAL,TR_IPADDR,TR_CUR_USER,TR_CUR_USERID,TR_SE_USER,TR_SE_USERID,TR_PROXY_USER,TR_PROXY_USERID,
          TR_CUR_SC,TR_HOST,TR_OS_USER,TR_SESSIONID
     FROM DUAL;

 --获取DDL SQL语句，如果语句过长无法全部获得，可以根据SQL_ID查询
     BEGIN
      SELECT SQL_TEXT,SQL_ID INTO TR_SQL,TR_SQL_ID
          FROM SYS.V_$OPEN_CURSOR
          WHERE UPPER(SQL_TEXT) LIKE 'ALTER%'
                OR UPPER(SQL_TEXT) LIKE 'CREATE%'
            OR UPPER(SQL_TEXT) LIKE 'DROP%';

          TR_N := ORA_SQL_TXT(TR_SQL_TEXT);
       
          FOR I IN 1 .. TR_N LOOP
        TR_STMT := TR_STMT || TR_SQL_TEXT(I);
          END LOOP;
       
         EXCEPTION WHEN OTHERS THEN
          TR_SQL_ID := NULL;
          TR_STMT := NULL;

     END;

     --向TB_SYSTEM_DDL_LOGS日志表中插入DDL操作记录
     IF ORA_SYSEVENT <> 'TRUNCATE' AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS_C%' THEN
       SELECT SEQ_DDL_VERSION.NEXTVAL INTO TR_VERSION_NO FROM DUAL;
       INSERT INTO C##DJJ.TB_SYSTEM_DDL_LOGS
             (EVENT_ID,EVENT_NAME,TERMINAL,DB_NAME,OBJECT_NAME,OBJECT_OWNER,OBJECT_TYPE,
             IS_ALTER_COLUMN,IS_DROP_COLUMN,SQL_ID,SQL_TEXT,SESSION_ID,
             CURRENT_USER,CURRENT_USERID,SESSION_USER,SESSION_USERID,
         PROXY_USER,PROXY_USERID,CURRENT_SCHEMA,HOST,OS_USER,IP_ADDRESS,VERSION_NO)
   VALUES (TR_EVENT_ID,ORA_SYSEVENT,TR_TERMINAL,ORA_DATABASE_NAME,ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_TYPE,
         NULL,NULL,TR_SQL_ID,TR_STMT,TR_SESSIONID,
         TR_CUR_USER,TR_CUR_USERID,TR_SE_USER,TR_SE_USERID,
          TR_PROXY_USER,TR_PROXY_USERID,TR_CUR_SC,TR_HOST,TR_OS_USER,TR_IPADDR,TR_VERSION_NO
    );
    COMMIT;
  END IF;
END;

-- 测试
create table test_a 
  (
   EVENT_ID             VARCHAR2(32)   
  );
  
--查看日志表
SELECT * FROM TB_SYSTEM_DDL_LOGS ORDER BY VERSION_NO;

SELECT * FROM TB_SYSTEM_DDL_LOGS;

-- 将监控结果，写入本地文件
-- 在oracle中创建java sources
create or replace and compile java source named ddl_write as
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
public class OracleDDLWrite {
  public static void writeDDL(String path, String data) {
    BufferedWriter writer = null;
    if (data == null || data.length() == 0) {
      return;
    }
    try {
      writer = new BufferedWriter(new FileWriter(path, true));
      System.out.println("开始写文件，文件名称全路径 ：" + path);
      writer.write(data);
      writer.newLine();
      System.out.println("写文件结束");
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (writer != null) {
        try {
          writer.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
      }
    }
  }
}

--创建存储过程
create or replace procedure ddl_writer_procedure(path varchar2,data varchar2)

as language java name

'OracleDDLWrite.writeDDL(java.lang.String,java.lang.String)';