DDL触发器监控脚本部署步骤

以下操作请使用sys用户：

--第一步：创建表（此表主要保存ddl触发器产生的信息），可以根据不同的业务，使用相关的监控用户，在此监控用户为c##upctest

从可维护性考虑，此表要长期保存ddl触发器产生的信息，建议采用范围分区. 

CREATE TABLE c##upctest.STATS$DDL_AUDIT

   (  USER_NAME VARCHAR2(30) ,

  SID NUMBER,

  INST_ID NUMBER,

  IP_ADDRESS VARCHAR2(20),

  OSUSER VARCHAR2(30),

  MACHINE VARCHAR2(64),

  OBJ_OWNER VARCHAR2(30),

  OBJ_NAME VARCHAR2(128),

  OBJ_TYPE VARCHAR2(18),

  DDL_TIME DATE,

  DDL_TYPE VARCHAR2(30),

  DDL_SQL VARCHAR2(4000)

   )

  PARTITION BY RANGE (DDL_TIME)

(PARTITION P_R_201912  VALUES LESS THAN (TO_DATE('2019-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),

 PARTITION P_R_202001  VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),

 PARTITION P_R_202002  VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),

 PARTITION P_R_202003  VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202004  VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202005  VALUES LESS THAN (TO_DATE('2020-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202006  VALUES LESS THAN (TO_DATE('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202007  VALUES LESS THAN (TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202008  VALUES LESS THAN (TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202009  VALUES LESS THAN (TO_DATE('2020-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202010  VALUES LESS THAN (TO_DATE('2020-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202011  VALUES LESS THAN (TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202012  VALUES LESS THAN (TO_DATE('2020-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_PMAX  VALUES LESS THAN (TO_DATE('2099-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

 )

COMMENT ON COLUMN STATS$DDL_AUDIT.USER_NAME  IS '登陆用户名'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.SID  IS 'session id'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.INST_ID  IS '实例号'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.IP_ADDRESS IS '客户端ip地址'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.OSUSER  IS '客户端操作系统用户名'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.MACHINE   IS '客户端执行的机器'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_OWNER  IS 'DDL操作对象的所有者'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_NAME   IS 'DDL操作所对应的数据库对象名'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_TYPE IS 'DDL操作所对应的数据库对象的类型'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_TIME  IS 'DDL操作时间'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_TYPE IS '触发器的系统事件名称'; 

COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_SQL  IS 'DDL语句';

--2：创建ddl监控触发器(表STATS$DDL_AUDIT的用户是c##upctest,可根据自己的业务进行修改)

CREATE OR REPLACE TRIGGER sys.TRG_DDL_AUDIT

AFTER DDL ON DATABASE

DECLARE

   v_sql_text   ora_name_list_t;

   v_ddl_sql    c##upctest.STATS$DDL_AUDIT.ddl_sql%TYPE;

   v_len        NUMBER;

   v_piece_len  NUMBER;

   v_sid        NUMBER;

   v_machine    VARCHAR2(64);

   v_osuser     VARCHAR2(30);

BEGIN

    -- Get DDL SQL statement

   v_len := 0;

    FOR i IN 1..ora_sql_txt(v_sql_text) LOOP

       v_piece_len := length(v_sql_text(i));

       EXIT WHEN v_len + v_piece_len > 4000;

       v_ddl_sql := v_ddl_sql||v_sql_text(i);

       v_len := v_len + v_piece_len;

    END LOOP;

    -- get sid and machine from audsid

   SELECT sid,machine,osuser INTO v_sid,v_machine,v_osuser

     FROM v$session

   where sid=userenv('sid');

    -- insert to audit table

   INSERT INTO c##upctest.STATS$DDL_AUDIT

    (

       user_name,

       sid,

       inst_id,

       ip_address,

       osuser,

       machine,

       obj_owner,

       obj_name,

       obj_type,

       ddl_time,

       ddl_type,

       ddl_sql

    )

   VALUES

    (

       ora_login_user,

       v_sid,

       ora_instance_num,

       sys_context('userenv','ip_address'),  -- ora_client_ip_address seems no use here?

       v_osuser,

       v_machine,

       ora_dict_obj_owner,

       ora_dict_obj_name,

       ora_dict_obj_type,

       SYSDATE,

       ora_sysevent,

       v_ddl_sql

    );

EXCEPTION

    WHEN OTHERS THEN

       NULL;

END trg_ddl_audit;

此触发器可适用操作范围: alter ,drop  create ,truncate ,analyze,comment,grant,revoke,rename,noaudit,audit,disassociate statistics,associate,statistics

以及分区表针对分区的truncate,drop ,split,add,exchange操作(因为分区表的这些操作都属于alter)

第三步：STATS$DDL_AUDIT表维护

1：首先创建好下一年的表STATS$DDL_AUDIT_2020

CREATE TABLE c##upctest.STATS$DDL_AUDIT_2020

   (  USER_NAME VARCHAR2(30) ,

  SID NUMBER,

  INST_ID NUMBER,

  IP_ADDRESS VARCHAR2(20),

  OSUSER VARCHAR2(30),

  MACHINE VARCHAR2(64),

  OBJ_OWNER VARCHAR2(30),

  OBJ_NAME VARCHAR2(128),

  OBJ_TYPE VARCHAR2(18),

  DDL_TIME DATE,

  DDL_TYPE VARCHAR2(30),

  DDL_SQL VARCHAR2(4000)

   )

  PARTITION BY RANGE (DDL_TIME)

 (

 PARTITION P_R_202001  VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),

 PARTITION P_R_202002  VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),

 PARTITION P_R_202003  VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202004  VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202005  VALUES LESS THAN (TO_DATE('2020-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202006  VALUES LESS THAN (TO_DATE('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202007  VALUES LESS THAN (TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202008  VALUES LESS THAN (TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202009  VALUES LESS THAN (TO_DATE('2020-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202010  VALUES LESS THAN (TO_DATE('2020-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202011  VALUES LESS THAN (TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_202012  VALUES LESS THAN (TO_DATE('2020-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,

 PARTITION P_R_PMAX  VALUES LESS THAN (TO_DATE('2099-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

 )

2：alter TRIGGER sys.TRG_DDL_AUDIT disable;

--3，4 两步在c##upctest用户下执行

3:rename STATS$DDL_AUDIT to STATS$DDL_AUDIT_2020

4:rename STATS$DDL_AUDIT_2020 to STATS$DDL_AUDIT;

5:alter trigger sys.TRG_DDL_AUDIT compile;

6:alter TRIGGER sys.TRG_DDL_AUDIT enable;

客户端ip触发器脚本监控

功能说明：记录客户端ip地址到v$session视图中的client_info 字段中

CREATE OR REPLACE  TRIGGER SYS.TRG_USER_LOGON

 after logon on database

begin

  dbms_application_info.set_client_info(sys_context('userenv','ip_address'));

end;