前言
创建此触发器的主要目的是为了控制数据库的版本,虽然会将DDL语句保留但难免会出现遗漏,所以创建DDl触发器记录DDL操作,主要是用来核对数据库变更的SQL语句
创建用户并授权
#需要使用sys用户授权
CREATE USER DBADMIN IDENTIFIED BY DBADMIN;
GRANT CONNECT TO DBADMIN;
GRANT DBA TO DBADMIN;
GRANT SYS.V_$OPEN_CURSOR TO DBADMIN;
创建序列及表
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
'版本号';
序列的作用主要是为了标识DDL操作版本,采用的是一种类似与SVN版本控制的方式,每发生一次变化版本号就加一
表是用来存储变更记录的
创建触发器
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 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;
这个触发器中过滤了一些不必要操作,大家可以根据需求增加过滤内容
结果展示
SELECT EVENT_NAME,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,DDL_TIME,VERSION_NO
FROM DBADMIN.TB_SYSTEM_DDL_LOGS
ORDER BY VERSION_NO;
EVENT_NAME | OBJECT_TYPE | OBJECT_NAME | OBJECT_OWNER | DDL_TIME | VERSION_NO |
---|---|---|---|---|---|
CREATE | PACKAGE | PKG_LINE_RUN | BUS | 04-AUG-15 | 28 |
CREATE | PACKAGE BODY | PKG_LINE_RUN | BUS | 04-AUG-15 | 29 |
CREATE | PACKAGE | PKG_LINE_RUN | BUS | 04-AUG-15 | 32 |
CREATE | PACKAGE BODY | PKG_LINE_RUN | BUS | 04-AUG-15 | 33 |
CREATE | TABLE | ORA_TEMP_1_DS_150103 | SYS | 04-AUG-15 | 134 |
DROP | TABLE | ORA_TEMP_1_DS_150103 | SYS | 04-AUG-15 | 136 |
CREATE | TABLE | ORA_TEMP_1_DS_150104 | SYS | 04-AUG-15 | 137 |
DROP | TABLE | ORA_TEMP_1_DS_150104 | SYS | 04-AUG-15 | 139 |
CREATE | TABLE | ORA_TEMP_1_DS_150105 | SYS | 04-AUG-15 | 140 |
DROP | TABLE | ORA_TEMP_1_DS_150105 | SYS | 04-AUG-15 | 142 |
CREATE | TABLE | ORA_TEMP_1_DS_150106 | SYS | 04-AUG-15 | 143 |
DROP | TABLE | ORA_TEMP_1_DS_150106 | SYS | 04-AUG-15 | 145 |
CREATE | TABLE | DBMS_TABCOMP_TEMP_UNCMP | BUS | 04-AUG-15 | 146 |
CREATE | TABLE | DBMS_TABCOMP_TEMP_CMP | BUS | 04-AUG-15 | 147 |
DROP | TABLE | DBMS_TABCOMP_TEMP_UNCMP | BUS | 04-AUG-15 | 148 |
CREATE | TABLE | ORA_TEMP_1_DS_150107 | SYS | 04-AUG-15 | 149 |
DROP | TABLE | ORA_TEMP_1_DS_150107 | SYS | 04-AUG-15 | 151 |
DROP | TABLE | DBMS_TABCOMP_TEMP_CMP | BUS | 04-AUG-15 | 152 |
CREATE | TABLE | ORA_TEMP_1_DS_150108 | SYS | 04-AUG-15 | 153 |
DROP | TABLE | ORA_TEMP_1_DS_150108 | SYS | 04-AUG-15 | 155 |
CREATE | TABLE | ORA_TEMP_1_DS_150109 | SYS | 04-AUG-15 | 156 |
DROP | TABLE | ORA_TEMP_1_DS_150109 | SYS | 04-AUG-15 | 158 |
CREATE | TABLE | ORA_TEMP_1_DS_150110 | SYS | 04-AUG-15 | 159 |