Implementing DDL Monitoring with Oracle DDL triggers

Objective

The primary purpose of this trigger is to control the version of the database, although the DDL statements are persisted but inevitably omitted, so DDL triggers are created to record the DDL operations, primarily the SQL statements used to reconcile database changes

Create a user and authorize

#需要使用sys用户授权CREATE USER DBADMIN IDENTIFIED BY DBADMIN;GRANT CONNECT TO DBADMIN;GRANT DBA TO DBADMIN;GRANT SYS.V_$OPEN_CURSOR TO DBADMIN;

Creating sequences and tables

 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 TABLETb_system_ddl_logs (event_id VARCHAR2 ( +)DEFAULTSys_guid () not NULL, Event_Name VARCHAR2 ( -), TERMINAL VARCHAR2 ( -), Db_name VARCHAR2 ( -), object_name VARCHAR2 ( -), Object_name_listVARCHAR( -), Object_owner VARCHAR2 ( -), Object_type VARCHAR2 ( -), Is_alter_columnVARCHAR(Ten), Is_drop_columnVARCHAR(Ten), sql_idVARCHAR( -), Sql_text CLOB,Current_User         VARCHAR( -), Current_userid Number,Session_user         VARCHAR(Ten), Session_userid Number, Proxy_userVARCHAR( -), Proxy_userid Number, Current_schemaVARCHAR( -), HOSTVARCHAR( -), Os_userVARCHAR( -), IP_AddressVARCHAR( +), Ddl_timeDATE                 DEFAULTSysdate, session_idVARCHAR( +), Version_no Number,CONSTRAINTPk_tb_system_ddl_logsPRIMARY KEY(event_id));COMMENT on table tb_system_ddl_logs is ' database log ' DDL log table '; COMMENT on COLUMN tb_system_ddl_logs. EVENT_ID is ' event ID auto-generated '; COMMENT on COLUMN tb_system_ddl_logs. Event_Name is ' event name '; COMMENT on COLUMN tb_system_ddl_logs. TERMINAL is ' client operating system terminal name '; COMMENT on COLUMN tb_system_ddl_logs. Db_name is ' database name '; COMMENT on COLUMN tb_system_ddl_logs. object_name is ' DDL occurred object name '; COMMENT on COLUMN tb_system_ddl_logs. Object_name_list is ' object list '; COMMENT on COLUMN tb_system_ddl_logs. The host of the object Object_owner is ' DDL '; COMMENT on COLUMN tb_system_ddl_logs. Object_type is ' object category '; COMMENT on column Tb_system_ddl_logs.is_alter_column is ' when the column is modified is true, otherwise false '; COMMENT on column Tb_system_ddl_logs.is_drop_column is ' when column isDROPThe time is true, otherwise false'; COMMENT on COLUMN tb_system_ddl_logs. SQL_ID is 'sql_id'; COMMENT on COLUMN tb_system_ddl_logs. Sql_text is 'SQLStatement'; COMMENT on COLUMN tb_system_ddl_logs. Current_User is 'CurrentSESSIONThe name of the user who owns the permission (for example, the currentSESSIONis sys, but is executing system.myproc, thenCurrent_Useris System)'; COMMENT on COLUMN tb_system_ddl_logs. Current_userid is 'CurrentSESSIONThe ID of the user who owns the permission'; COMMENT on COLUMN tb_system_ddl_logs. Session_user is 'SessionThe user name that belongs to'; COMMENT on COLUMN tb_system_ddl_logs. Session_userid is 'CurrentSESSIONThe user ID that belongs to'; COMMENT on COLUMN tb_system_ddl_logs. Proxy_user is 'Open the currentSESSIONThe name of the user'; COMMENT on COLUMN tb_system_ddl_logs. Proxy_userid is 'Open the currentSESSIONThe ID of the user'; COMMENT on COLUMN tb_system_ddl_logs. Current_schema is 'CurrentSESSIONThe defaultSCHEMAName, you can useSESSION SETCurrent_schema Statement Modification'; COMMENT on COLUMN tb_system_ddl_logs. HOST is 'Host name of the client'; COMMENT on COLUMN tb_system_ddl_logs. Os_user is 'Operating system user name for the client'; COMMENT on COLUMN tb_system_ddl_logs. IP_Address is 'IP address of the client'; COMMENT on COLUMN tb_system_ddl_logs. Ddl_time is 'Modification time'; COMMENT on COLUMN tb_system_ddl_logs. session_id is 'session_id'; COMMENT on COLUMN tb_system_ddl_logs. Version_no is 'Version number';

The role of the sequence is primarily to identify the DDL operation version, which is a similar to SVN version control, with each change in the version number plus a

Tables are used to store change records.

Create a Trigger

create  OR  replace  trigger  trig_monitor_system_ddlafter  DDL on  database /* * * Creation time: 2014  year 7  month 1  Day 09 : 49 : 02  * Description: Monitor DDL operations and log DDL operations and DDL statements to the log table */declare   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 ();      --Get user information      SELECTNVL (Sys_context (' USERENV ',' TERMINAL '),"'),--the name of the client operating system terminal NVL (Sys_context (' USERENV ',' IP_Address '),"'),--the name of the client operating system terminal NVL (Sys_context (' USERENV ',' Current_User '),"'),--CurrentSESSIONThe name of the user who owns the permission (for example, the currentSESSIONis sys, but is executing system.myproc, thenCurrent_Useris System) NVL (Sys_context (' USERENV ',' Current_userid '),"'),--CurrentSESSIONThe ID of the user who owns the permission NVL (Sys_context (' USERENV ',' Session_user '),"'),--SESSIONThe user name that belongs to NVL (Sys_context (' USERENV ',' Session_userid '),"'),--CurrentSESSIONThe owning User ID NVL (sys_context (' USERENV ',' Proxy_user '),"'),--Open the currentSESSIONThe name of the user NVL (Sys_context (' USERENV ',' Proxy_userid '),"'),--Open the currentSESSIONThe user's ID NVL (Sys_context (' USERENV ',' Current_schema '),"'),--CurrentSESSIONThe defaultSCHEMAName NVL (Sys_context (' USERENV ',' HOST '),"'),--the host name of the client NVL (Sys_context (' USERENV ',' Os_user '),"'),--the client's operating system user name NVL (sys_context (' USERENV ',' SESSIONID '),"')--SESSIONThe ID intoTr_terminal,tr_ipaddr,tr_cur_user,tr_cur_userid,tr_se_user,tr_se_userid,tr_proxy_user,tr_proxy_userid, TR_CUR_S C,tr_host,tr_os_user,tr_sessionid fromDUAL;      --Get the DDL SQL statement, and if the statement is too long to get all, you can query according to sql_id       BEGINSELECT sql_text,sql_id  to 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 and then tr_sql_id: = NULL; TR_STMT: = NULL; END;      --inserting DDL action records into the Tb_system_ddl_logs log tableIF ora_sysevent <> 'truncate   ' and ORA_DICT_ Obj_name not like '  sys_c% 

This trigger filters out some unnecessary actions, and you can increase the filter content according to your needs.

Results show

SELECT EVENT_NAME,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,DDL_TIME,VERSION_NOFROM DBADMIN.TB_SYSTEM_DDL_LOGSORDER 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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Implementing DDL Monitoring with Oracle DDL triggers