oracle ddl 监控
1.创建用户并授权
#需要使用sys用户授权
CREATE USER DBADMIN IDENTIFIED BY DBADMIN;
GRANT CONNECT TO DBADMIN;
GRANT DBA TO DBADMIN;
GRANT select on SYS.V_$OPEN_CURSOR TO DBADMIN;
2.建立日志表
DROP SEQUENCE SEQ_DDL_VERSION;`
`CREATE SEQUENCE SEQ_DDL_VERSION INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;`
`DROP TABLE TB_SYSTEM_DDL_LOGS CASCADE CONSTRAINTS;`
`/*==============================================================*/`
`/* TABLE: TB_SYSTEM_DDL_LOGS */
/*==============================================================*/`
`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`
`'版本号';`
3.建立触发器
-
CREATE OR REPLACE TRIGGER TRIG_MONITOR_SYSTEM_DDL AFTER DDL ON DATABASE /** * 创建时间：2014年7月1日09:49:02 * 描述：监控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 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;
4.结果展示
新建一张表
create table test_a
(
EVENT_ID VARCHAR2(32)
);
SELECT EVENT_NAME,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,DDL_TIME,VERSION_NO FROM DBADMIN.TB_SYSTEM_DDL_LOGS ORDER BY VERSION_NO;
1 9361F8C9DDAF4419B146177E144E3B8B CREATE CSA ORCL TEST_A DBADMIN TABLE aqunau2axk29p <clob> DBADMIN 92 DBADMIN 92 DBADMIN WORKGROUP\CSA CSA\18628 127.0.0.1 2021/6/11 15:40:59 30855 1
5.将监控结果写入本地文件中
5.1在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();
}
}
}
}
}
5.2 创建存储过程
create or replace procedure ddl_writer_procedure(path varchar2,data varchar2)
as language java name
'OracleDDLWrite.writeDDL(java.lang.String,java.lang.String)';
5.3 赋权
JAVA程序里涉及到对文件的读写操作，所以要先修改权限。
以管理员身份登录进数据库
begin
dbms_java.grant_permission('DBADMIN','SYS:java.io.FilePermission','E:\meng.txt','read,write,execute,delete');
end;
5.4 修改TRIGGER
将之前的触发器内容中的写库操作改为 调用存储过程
--向TB_SYSTEM_DDL_LOGS日志表中插入DDL操作记录
....
COMMIT;
将上述代码块之间的内容改为
ddl_writer_procedure('E:\\meng.txt', TR_STMT);
即可；
5.5 结果展示
执行建表语句后查看文件
