業(yè)務(wù)表:數(shù)據(jù)庫(kù)DDL語(yǔ)句記錄

create table SYS.DB_DDL_RECORD(
  OPERATETIME timestamp(6)
  ,IP_ADDRESS varchar2(30 byte)
  ,HOSTNAME varchar2(30 byte)
  ,MODULE varchar2(30 byte)
  ,OPERATION varchar2(30 byte)
  ,OBJECT_TYPE varchar2(30 byte)
  ,OBJECT_NAME varchar2(61 byte)
  ,SQL_STMT clob
  ,DB_SCHEMA varchar2(30 byte)
  ,UUID varchar2(32 byte)default SYS_GUID() not null enable
  ,constraint DB_DDL_RECORD_PK primary key(UUID) using index
);

comment on column SYS.DB_DDL_RECORD.OPERATETIME is '操作時(shí)間';
comment on column SYS.DB_DDL_RECORD.IP_ADDRESS is 'ip地址';
comment on column SYS.DB_DDL_RECORD.HOSTNAME is '連接電腦機(jī)器名';
comment on column SYS.DB_DDL_RECORD.MODULE is '連接使用Application';
comment on column SYS.DB_DDL_RECORD.OPERATION is '操作類(lèi)型';
comment on column SYS.DB_DDL_RECORD.OBJECT_TYPE is '數(shù)據(jù)庫(kù)對(duì)象類(lèi)型';
comment on column SYS.DB_DDL_RECORD.OBJECT_NAME is '數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)';
comment on column SYS.DB_DDL_RECORD.SQL_STMT is 'sql語(yǔ)句體';
comment on column SYS.DB_DDL_RECORD.DB_SCHEMA is '連接的schema';
comment on table SYS.DB_DDL_RECORD is '數(shù)據(jù)庫(kù)DDL語(yǔ)句記錄，有問(wèn)題請(qǐng)聯(lián)系Kindey.S，微信kindey123;

通過(guò)觸發(fā)器實(shí)現(xiàn)記錄

create or replace trigger DDL_RECORD
  after ddl on database
/*
authro:Kindey.S
date:2018-07-23
version:1.0.0.0
describe:create
date:2018-07-23
version:1.0.1.0
describe:修復(fù)hostname取值錯(cuò)誤問(wèn)題
remark:recording for DDL operating.
*/
declare
  pragma AUTONOMOUS_TRANSACTION;--開(kāi)啟自治事務(wù)
  PART number;--長(zhǎng)語(yǔ)句分段數(shù)量
  STMT clob := null;--轉(zhuǎn)換后的語(yǔ)句
  SQL_TEXT ORA_NAME_LIST_T;--原始語(yǔ)句
begin
  --長(zhǎng)語(yǔ)句轉(zhuǎn)換
  PART := ORA_SQL_TXT(SQL_TEXT);
  for i in 1 .. PART loop
    STMT := STMT || SQL_TEXT(i);
  end loop;
  --插入記錄
  insert into DB_DDL_RECORD
    (OPERATETIME, IP_ADDRESS, HOSTNAME, MODULE, OPERATION, OBJECT_TYPE, OBJECT_NAME, SQL_STMT,DB_SCHEMA)
  values
    (SYSTIMESTAMP,
     SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
     SYS_CONTEXT('USERENV', 'HOST'),
     SYS_CONTEXT('USERENV', 'MODULE'),
     ORA_SYSEVENT,
     ORA_DICT_OBJ_TYPE,
     ORA_DICT_OBJ_NAME,
     replace(STMT,CHR(0),''),
     user
   );
  commit;
end;
/