|
/** * 创建时间：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_R |
|