Oracle常用脚本-监控DDL

CREATE TABLE db_ddl
    (ddl_time                       VARCHAR2(30),
    session_id                     NUMBER,
    os_user                        VARCHAR2(200),
    ip_address                     VARCHAR2(200),
    terminal                       VARCHAR2(200),
    host                           VARCHAR2(200),
    user_name                      VARCHAR2(30),
    ddl_type                       VARCHAR2(30),
    object_type                    VARCHAR2(18),
    owner                          VARCHAR2(30),
    object_name                    VARCHAR2(128),
    sql_text                       VARCHAR2(4000))
/
CREATE OR REPLACE TRIGGER tri_ddl
 AFTER
  DDL
 ON SCHEMA
DECLARE
   sql_text    ora_name_list_t;
   i           number(10);
   state_sql   VARCHAR2 (4000);
BEGIN
  
   begin
   FOR i IN 1 .. ora_sql_txt (sql_text)
   LOOP
      state_sql := state_sql || sql_text (i);
   END LOOP;
   exception
      when others then
      sp_sqllog('TRI_DDL ERROR:' || ora_dict_obj_name || sqlerrm);
   end;
  
   state_sql := SUBSTRB (state_sql, 1, 4000);

   if not state_sql like 'ALTER%COMPILE%' then
   INSERT INTO db_ddl
               (ddl_time,
                session_id,
                os_user,
                ip_address,
                terminal,
                HOST,
                user_name, ddl_type,
                object_type, owner,
                object_name, sql_text
               )
        VALUES (TO_CHAR (SYSDATE, 'yyyymmddhh24miss'),
                SYS_CONTEXT ('USERENV', 'SESSIONID'),
                SYS_CONTEXT ('USERENV', 'OS_USER'),
                SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
                SYS_CONTEXT ('USERENV', 'TERMINAL'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                ora_login_user, ora_sysevent,
                ora_dict_obj_type, ora_dict_obj_owner,
                ora_dict_obj_name, state_sql
               );
   end if;
EXCEPTION
   WHEN OTHERS
   THEN
      sp_sqllog('TRI_DDL ERROR:' || sqlerrm);
END;
/