前言

创建此触发器的主要目的是为了控制数据库的版本，虽然会将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版本控制的方式，每发生一次变化版本号就加一

表是用来存储变更记录的

创建触发器