Ddl trigger
EXEM Knowledge Base
목차 |
[편집] 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;
/