Ddl trigger

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] DDL Trigger란

[편집] 사용 예

[편집] DDL Trigger를 이용해 DDL 추적하기

DDL Trigger내에서 사용 가능한 문맥 정보는 다음과 같은 일련의 함수로 접근 가능하다.

  • ora_sysevent
  • ora_login_user
  • ora_dict_obj_type
  • ora_dict_obj_owner
  • ora_dict_obj_name
  • ora_sql_txt

DDL Trigger를 이용해서 DDL을 추적하는 방법은 아래 Metalink Note를 참조한다.

HOW TO CAPTURE ALL THE DDL STATEMENTS

CREATE TABLE DDL_ACTIONS 
( 
    counter number(38) 
    ,user_name VARCHAR2(4000) 
    ,ddl_date VARCHAR2(26) 
    ,ddl_type VARCHAR2(4000) 
    ,object_type VARCHAR2(4000) 
    ,owner VARCHAR2(4000) 
    ,object_name VARCHAR2(4000) 
    ,sqltext CLOB 
); 


create or replace trigger DDLTrigger 
AFTER DDL ON DATABASE 
declare 
  l_cnt BINARY_INTEGER := 0; 
  l_len integer := 0; 
  l_no integer := 1; 
  l_s varchar2(32767) := ''; 
  l_sql_text ora_name_list_t; 
BEGIN 
  l_cnt := ora_sql_txt(l_sql_text); 
  for i in 1..l_cnt loop 
    if l_cnt = 1 then 
      insert into DDL_ACTIONS 
      ( 
        counter 
        ,user_name 
        ,ddl_date 
        ,ddl_type 
        ,object_type 
        ,owner 
        ,object_name 
        ,sqltext 
      ) 
      VALUES 
      ( 
        i 
        ,ora_login_user 
        ,to_char(systimestamp,'yyyy.mm.dd hh24:mi:ss.FF') 
        ,ora_sysevent 
        ,ora_dict_obj_type 
        ,ora_dict_obj_owner 
        ,ora_dict_obj_name 
        ,l_sql_text(i) 
      ); 
    else 
      if l_len + length(l_sql_text(i)) > 32767 then 
        -- insert 
        insert into 
        DDL_ACTIONS 
        ( 
        counter 
        ,user_name 
        ,ddl_date 
        ,ddl_type 
        ,object_type 
        ,owner 
        ,object_name 
        ,sqltext 
        ) 
        VALUES 
        ( 
        l_no 
        ,ora_login_user 
        ,to_char(systimestamp,'yyyy.mm.dd hh24:mi:ss.FF') 
        ,ora_sysevent 
        ,ora_dict_obj_type 
        ,ora_dict_obj_owner 
        ,ora_dict_obj_name 
        ,l_s 
        ); 
        l_len := length(l_sql_text(i)); 
        l_s := l_sql_text(i); 
        l_no := l_no + 1; 
      else 
        -- concat 
        l_s := l_s||l_sql_text(i); 
        l_len := l_len + length(l_sql_text(i)); 
      end if; 
    end if; 
  end loop; 
  
  if l_s != '' then 
    insert into DDL_ACTIONS 
    ( 
      counter 
      ,user_name 
      ,ddl_date 
      ,ddl_type 
      ,object_type 
      ,owner 
      ,object_name 
      ,sqltext 
      ) 
    VALUES 
    ( 
      l_no 
      ,ora_login_user 
      ,to_char(systimestamp,'yyyy.mm.dd hh24:mi:ss.FF') 
      ,ora_sysevent 
      ,ora_dict_obj_type 
      ,ora_dict_obj_owner 
      ,ora_dict_obj_name 
      ,l_s 
    ); 
  end if; 
end;
/ 

[편집] 관련 자료

  1. Executing DDL in the DDL trigger