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 DBADMIN 92 DBADMIN 92 DBADMIN WORKGROUP\CSA CSA628 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 结果展示

执行建表语句后查看文件