Audit DDL 用于记录Oracle DDL事件

创建表

--------------------------------------------------------
--  File created - Wednesday-May-11-2022   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table AUDIT_DDL
--------------------------------------------------------

  CREATE TABLE "AUDIT_DDL" 
   (    "D" DATE, 
    "OSUSER" VARCHAR2(255 BYTE), 
    "CURRENT_USER" VARCHAR2(255 BYTE), 
    "HOST" VARCHAR2(255 BYTE), 
    "TERMINAL" VARCHAR2(255 BYTE), 
    "OWNER" VARCHAR2(30 BYTE), 
    "TYPE" VARCHAR2(30 BYTE), 
    "NAME" VARCHAR2(30 BYTE), 
    "SYSEVENT" VARCHAR2(30 BYTE), 
    "SQL_TXT" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUDIT" 
 LOB ("SQL_TXT") STORE AS BASICFILE (
  TABLESPACE "SYSAUDIT" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

 

创建TRIGGER

create or replace trigger audit_ddl_trg after ddl on schema
/* 
This Trigger is for Excel Internal Usage, any object DDL changes will trigger this event. 
The corresponding DDL changes will be written in the AUDIT_DDL* table
  *The table AUDIT_DDL is under ITAUDIT schema.
*/

declare
  sql_text ora_name_list_t;
  ddlc clob:= '';
  n number;
begin
  if (ora_sysevent='TRUNCATE')
  then
    null;
  else
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
       ddlc := ddlc || sql_text(i);
    end loop;
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      ddlc
    );
  end if;
end;