可追究责任的纪录数据库所有的重大DDL操作语句.docx

以前的写的两个挺有用的脚本，现在略微改写整理了一下，大家若有用到可参考。这两个脚本对数据库性能略有影响，但不大，主要用途为跟踪纪录对数据库的重大操作：一、纪录数据库所有的重大DDL操作，可追究责任DECLARE -安装DDL日志表 DDL_OBJ_TRACE V_I NUMBER(3); V_USER VARCHAR2(30);BEGIN SELECT COUNT(*) INTO V_I FROM DBA_TABLES WHERE TABLE_NAME=DDL_OBJ_TRACE; IF V_I=1 THEN SELECT OWNER INTO V_USER FROM DBA_TABLES WHERE TABLE_NAME=DDL_OBJ_TRACE; -TR_DDL_TRACE不失效前无法删除表DDL_OBJ_TRACE，此脚本仅限于第一次执行 EXECUTE IMMEDIATE DROP TABLE |V_USER|.DDL_OBJ_TRACE; END IF; EXECUTE IMMEDIATE CREATE TABLE DDL_OBJ_TRACE(TIMES DATE, OWNER VARCHAR2(32), OBJECT_NAME VARCHAR2(32), OBJECT_TYPE VARCHAR2(32), USER_NAME VARCHAR2(32), EVENTS VARCHAR2(32), IP_ADDRESS VARCHAR2(32), MACHINE VARCHAR2(64), PROGRAM VARCHAR2(64), SQL_TEXT VARCHAR2(4000), SQL_TEXT1 VARCHAR2(4000), SQL_TEXT2 VARCHAR2(4000) NOLOGGING; EXECUTE IMMEDIATE CREATE INDEX IND_DDL_TRACE_USER_OBJ ON SPACER.DDL_OBJ_TRACE(OWNER,OBJECT_NAME) NOLOGGING; EXECUTE IMMEDIATE CREATE INDEX IND_DDL_TRACE_OBJ ON SPACER.DDL_OBJ_TRACE(OBJECT_NAME) NOLOGGING;END;/-创造数据库级触发器CREATE OR REPLACE TRIGGER TR_DDL_TRACE AFTER DDL ON DATABASE-跟踪所有对数据表进行CREATE,ALTER,DROP及TRUNCATE的操作,并将其记录到表 DDL_OBJ_TRACE 中,该表创建语句如下:-CREATE TABLE DDL_OBJ_TRACE- (TIMES DATE,- OWNER VARCHAR2(32),- OBJECT_NAME VARCHAR2(32),- OBJECT_TYPE VARCHAR2(32),- USER_NAME VARCHAR2(32),- EVENTS VARCHAR2(32),- IP_ADDRESS VARCHAR2(32),- MACHINE VARCHAR2(64),- PROGRAM VARCHAR2(64),- SQL_TEXT VARCHAR2(4000),- SQL_TEXT1 VARCHAR2(4000),- SQL_TEXT2 VARCHAR2(4000);- 触发器可以在任意用户下,但用户必须具有以下权限(建议使用SYSTEM用户):- ADMINISTRATOR DATABASE TRIGGER- SELECT ANY DICTIONARY- CREATE ANY TABLE- INSERT ANY TABLE- UNLIMITED TABLESPACE- EXECUTE ANY PROCEDURE- 修改时间：2012-06-19DECLARE V_OBJ_NAME VARCHAR2(32); -目标名称 V_OWNER VARCHAR2(32); -目标拥有者 V_TYPE VARCHAR2(32); -目标类型(表,索引,过程,视图等) V_USER VARCHAR2(32); -数据库用户名 V_EVENTS VARCHAR2(32); -触发事件类型(CREATE,ALTER,DROP,TRUNCATE之一) V_IPADDRESS VARCHAR2(32); -发出语句的客户端IP地址 V_MACHINE VARCHAR2(64); -发出语句的客户端机器名 V_PROGRAM VARCHAR2(64); -发出语句的客户端程序名 V_SESSION_ID NUMBER(10); V_SQL_FULL VARCHAR2(32000); V_SQL_FULL1 VARCHAR2(5000); V_SQL_FULL2 VARCHAR2(5000); V_COUNT NUMBER(10); V_I NUMBER(10); V_EXIST NUMBER(10); V_SQL ORA_NAME_LIST_T; FUNCTION SUBSTR_CHINESE(I_STR IN VARCHAR2,I_POS IN OUT NUMBER) RETURN VARCHAR2 IS -为了避免SUBSTRB出现关个汉字的现象及SUBSTR出现无法存储的现象,自定义本函数,用于取不超过I_POS个字符,且不会出现半个汉字的现象 -但由于ORACLE中存储SQL语句使用了多条记录且未做半个汉字的判断,因此,最后提取的语句中仍可能有半个汉字的现象,特别是在存储过程中 V_I1 NUMBER(10); V_RESULT VARCHAR2(32000); BEGIN V_I1:=I_POS; WHILE LENGTHB(SUBSTR(I_STR,1,V_I1)I_POS LOOP V_I1:=V_I1-1; END LOOP; V_RESULT:=SUBSTR(I_STR,1,V_I1); I_POS:=V_I1; RETURN V_RESULT; END;BEGIN SELECT COUNT(*) INTO V_EXIST FROM DBA_TRIGGERS WHERE TRIGGER_NAME=TR_DDL_TRACE AND OWNER=SYSTEM; -判断系统用户下是否有相同的过程，如果有且当前用户不是system用户，触发器不需要处理任何事情，防止重复处理 IF V_EXIST=0 OR SYS_CONTEXT(USERENV,CURRENT_SCHEMA)=SYSTEM THEN V_OBJ_NAME:=NVL(TRIM(UPPER(ORA_DICT_OBJ_NAME),OTHERS); V_TYPE:=NVL(TRIM(UPPER(ORA_DICT_OBJ_TYPE),OTHERS); V_OWNER:=NVL(TRIM(UPPER(ORA_DICT_OBJ_OWNER),OTHERS); IF (V_TYPE NOT IN (TABLE,SYNONYM) OR (SUBSTR(V_OBJ_NAME,1,4)TMP_ AND SUBSTR(V_OBJ_NAME,1,5)TEMP_ AND SUBSTR(V_OBJ_NAME,1,4)BIN$) AND V_OWNER NOT IN (SYS,SYSTEM) THEN -不跟踪tmp及temp开头的临时表及bin$开头的回收站中的数据表清理,也不跟踪系统用户sys及system用户的结构变动 V_USER:=TRIM(UPPER(ORA_LOGIN_USER); V_EVENTS:=TRIM(UPPER(ORA_SYSEVENT); SELECT SYS_CONTEXT(USERENV,IP_ADDRESS) INTO V_IPADDRESS FROM DUAL; SELECT USERENV(SESSIONID) INTO V_SESSION_ID FROM DUAL; SELECT MAX(MACHINE),MAX(PROGRAM) INTO V_MACHINE,V_PROGRAM FROM V$SESSION WHERE AUDSID=V_SESSION_ID; V_COUNT:=ORA_SQL_TXT(V_SQL); V_SQL_FULL:=; V_I:=1; WHILE V_I=V_COUNT AND NVL(LENGTHB(V_SQL_FULL),0)0 THEN EXECUTE IMMEDIATE DROP SEQUENCE EXEC_SEQ; END IF; -生成序列 EXECUTE IMMEDIATE CREATE SEQUENCE EXEC_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE CACHE 20 NOORDER; -处理10的库的 SELECT COUNT(*) INTO V_I FROM USER_TABLES WHERE TABLE_NAME=EXEC_LOG; IF V_I0 THEN EXECUTE IMMEDIATE DROP TABLE EXEC_LOG PURGE; END IF; EXECUTE IMMEDIATE CREATE TABLE EXEC_LOG(LOG_NO NUMBER(10), USER_NAME VARCHAR2(32), BEGIN_TIME DATE, END_TIME DATE, SQL_TEXT VARCHAR2(4000), SQL_TEXT1 VARCHAR2(4000), SQL_TEXT2 VARCHAR2(4000), ROW_COUNT NUMBER(14), LOG_LEVEL VARCHAR2(20), LOG_TYPE VARCHAR2(20), LOG_DETAIL VARCHAR2(2000), IP_ADDRESS VARCHAR2(100), TIMES NUMBER(10,2)NOLOGGING;END;/CREATE OR REPLACE PROCEDURE EXECSQL(SQLSTRING IN VARCHAR2) AS -过 程 名：EXECSQL（） -功 能：动态执行SQL语句 -参 数：SQLSTRING为要执行的语句,I_NOTE为语句的说明 -修改时间：2012-06-18 ROWNUMID INTEGER; S_TIME DATE; V_ERR_MSG VARCHAR2(5000); V_SQLSTRING VARCHAR2(32767); V_SQL VARCHAR2(4000); V_SQL1 VARCHAR2(4000); V_SQL2 VARCHAR2(4000); V_CURTIME NUMBER(12,2); V_USETIME NUMBER(12,2); V_I NUMBER(10); -V_K NUMBER(2); V_SID NUMBER(10); V_OBJECT_NAME VARCHAR2(100); V_SES V$SESSION%ROWTYPE;BEGIN S_TIME:=SYSDATE; -V_K := 0; V_CURTIME:=DBMS_UTILITY.GET_TIME; V_SQLSTRING:=UPPER(TRIM(SQLSTRING); WHILE INSTR(V_SQLSTRING, )0 LOOP V_SQLSTRING:=REPLACE(V_SQLSTRING, , ); END LOOP;/*无论此库是否关闭回收站，此判断功能失效，以提升执行性能 -判断数据库版本号，如果是 10G或11G，且是DROP表时候需要清空回收站，避免能在回收站中找到该表而导致删除时候报错 IF SUBSTR(V_SQLSTRING,1,4)=DROP THEN SELECT COUNT(*) INTO V_I FROM V$VERSION WHERE UPPER(BANNER) LIKE %11G%11.% OR UPPER(BANNER) LIKE %10G%10.%; IF V_I 0 THEN IF INSTR(V_SQLSTRING,;) 0 THEN V_SQLSTRING := V_SQLSTRING| PURGE; ELSE V_K := 1; END IF; END IF; END IF;*/ EXECUTE IMMEDIATE V_SQLSTRING; ROWNUMID:=SQL%ROWCOUNT; -影响的记录数 /*忽略回收站带来的影响 IF V_K = 1 THEN EXECUTE IMMEDIATE PURGE RECYCLEBIN; END IF; */ V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100; IF V_USETIME=0.5 THEN -运行时间超过1秒或者有说明,记录语句日志 V_SQL:=TRIM(SUBSTRB(V_SQLSTRING,1,4000); V_SQL1:=TRIM(SUBSTRB(V_SQLSTRING,4001,4000); V_SQL2:=TRIM(SUBSTRB(V_SQLSTRING,8001,4000); INSERT INTO EXEC_LOG (LOG_NO,USER_NAME,BEGIN_TIME,END_TIME,SQL_TEXT,SQL_TEXT1,SQL_TEXT2,ROW_COUNT,LOG_LEVEL,LOG_DETAIL,IP_ADDRESS,TIMES) VALUES (EXEC_SEQ.NEXTVAL,SYS_CONTEXT(USERENV,CURRENT_SCHEMA),S_TIME,SYSDATE,V_SQL,V_SQL1,V_SQL2,ROWNUMID,INFO,NULL,SYS_CONTEXT(USERENV,IP_ADDRESS),V_USETIME); END IF;EXCEPTION WHEN OTHERS THEN ROLLBACK; V_ERR_MSG:=SQLERRM(SQLCODE); IF INSTR(V_ERR_MSG,2149)0 AND INSTR(SQLSTRING,DROP PARTITION)0 THEN -对于ORA-02149号错误(指定的分区不存在)不处理,也不报错,但产生警告信息 V_SQL:=TRIM(SUBSTRB(SQLSTRING,1,4000); V_SQL1:=TRIM(SUBSTRB(SQLSTRING,4001,4000); V_SQL2:=TRIM(SUBSTRB(SQLSTRING,8001,4000); V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100; INSERT INTO EXEC_LOG (LOG_NO,USER_NAME,BEGIN_TIME,END_TIME,SQL_TEXT,SQL_TEXT1,SQL_TEXT2,ROW_COUNT,LOG_LEVEL,LOG_DETAIL,IP_ADDRESS,TIMES) VALUES (EXEC_SEQ.NEXTVAL,SYS_CONTEXT(USERENV,CURRENT_SCHEMA),S_TIME,SYSDATE,V_SQL,V_SQL1,V_SQL2,NULL,WARN,V_ERR_MSG,SYS_CONTEXT(USERENV,IP_ADDRESS),V_USETIME); COMMIT; ELSE IF INSTR(V_ERR_MSG,NOWAIT)0 AND SUBSTR(SQLSTRING,1,4) IN (DROP,TRUN) THEN -资源被锁 IF SUBSTR(SQLSTRING,1,4)=DROP THEN V_OBJECT_NAME:=SUBSTR(SQLSTRING,12,LENGTH(SQLSTRING)-11); ELSE V_OBJECT_NAME:=SUBSTR(SQLSTRING,16,LENGTH(SQLSTRING)-11); END IF; SELECT MAX(OBJECT_ID) INTO V_I FROM DBA_OBJECTS WHERE OWNER=SYS_CONTEXT(USERENV,CURRENT_SCHEMA) AND OBJECT_NAME=V_OBJECT_NAME; SELECT NVL(MAX(SID),0) INTO V_SID FROM V$LOCK WHERE TYPE=TM AND ID1=V_I; IF V_SID0 THEN SELECT * INTO V_SES FROM V$SESSION WHERE SID=V_SID; V_ERR_MSG:=V_ERR_MSG|,锁住资源的线程号：|TRIM(TO_CHAR(V_SID)|,该线程用户名:|V_SES.USERNAME|,状态:|V_SES.STATUS|,线程发出机器名:|V_SES.TERMINAL|,操作系统用户名:|V_SES.OSUSER|,客户端程序:|V_SES.PROGRAM; END IF; ELSIF INSTR(V_ERR_MSG,0