前言：
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)';
