深圳 公司网站设计,重庆的网站设计公司价格,莱芜网逃人员,福州网站营销前言#xff1a; Oracle对表的监控分为数据行修改DML的监控、对表的DDL监控
1、对表的DML监控#xff08;数据的增删改#xff09; -- 创建测试表 create table tab_test01( id varchar2(100) default sys_guid(), name varchar2(100), insert_date date default sysdate…前言 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);
