여기저기 정보 조합, 수정하여 현 시스템에 최종 적용한 것입니다.
trigger 관련 에러 발생해도 DDL 문 실행은 되도록, 에러 무시 exception 처리하였습니다.
혹시 모를 DDL 오류 발생시 alter trigger ddl_history_trigger disable; 처리하여 에러 수정 가능합니다.
또한 DBA role이 있어도 pl/sql 문 내부에서 실행시 개별 privilege 필요하므로,
사전에 trigger 를 만드는 계정에 v_$session 에 대한 권한 부여해야 합니다.
-- v_$session 조회권한 부여 (sys 권한)
SQL> grant select on sys.v_$session to 트리거계정명
-- 이력을 저장할 테이블
CREATE TABLE DDL_HISTORY
(
EXEC_DTM DATE,
USERNAME VARCHAR2(128 BYTE),
ACTION_EVENT VARCHAR2(100 BYTE),
OBJECT_TYPE VARCHAR2(32 BYTE),
OBJECT_OWNER VARCHAR2(128 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
PROGRAM VARCHAR2(48 BYTE),
MACHINE VARCHAR2(64 BYTE),
IP_ADDRESS VARCHAR2(45 BYTE),
OSUSER VARCHAR2(30 BYTE),
SQL_ID VARCHAR2(13 BYTE),
SQL_TEXT VARCHAR2(4000 BYTE)
);
-- 인덱스는 추후 조회조건 위주로 생성
CREATE INDEX DDL_HISTORY_N1 ON DDL_HISTORY(OBJECT_NAME);
CREATE INDEX DDL_HISTORY_N2 ON DDL_HISTORY(OBJECT_OWNER);
CREATE INDEX DDL_HISTORY_N3 ON DDL_HISTORY(EXEC_DTM DESC);
-- trigger script
CREATE OR REPLACE TRIGGER DDL_HISTORY_TRIGGER AFTER DDL ON DATABASE
DECLARE
l_program VARCHAR2(48);
l_machine VARCHAR2(64);
l_osuser VARCHAR2(30);
l_username VARCHAR2(128);
l_sql_id VARCHAR2(13);
l_sql_arr ORA_NAME_LIST_T;
l_arr_cnt BINARY_INTEGER := 0;
l_sql_len BINARY_INTEGER := 0;
l_sql_text VARCHAR2(4000);
BEGIN
-- SQL_TEXT 4000자 이하로 자르기
l_arr_cnt := ORA_SQL_TXT(l_sql_arr);
FOR i in 1..l_arr_cnt LOOP
l_sql_text := l_sql_text || SUBSTRB(l_sql_arr(i), 1, 4000 - l_sql_len);
l_sql_len := LENGTHB(l_sql_text);
IF l_sql_len >= 4000 THEN
EXIT;
END IF;
END LOOP;
-- WITH(materialize)와 같은 내부 DDL 제외
IF ( ORA_DICT_OBJ_NAME NOT LIKE 'SYS_TEMP%' AND ORA_DICT_OBJ_NAME NOT LIKE 'ORA_TEMP%' )
THEN
SELECT ss.program,
ss.machine,
ss.osuser,
ss.username,
ss.sql_id
INTO l_program,
l_machine,
l_osuser,
l_username,
l_sql_id
FROM sys.v_$session ss
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ROWNUM = 1;
IF l_machine NOT LIKE '제외할 머신명%' THEN
INSERT INTO ddl_history
(
exec_dtm,
username,
action_event,
object_type,
object_owner,
object_name,
program,
machine,
ip_address,
osuser,
sql_id,
sql_text
)
VALUES (
SYSDATE,
ORA_LOGIN_USER,
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_OWNER,
ORA_DICT_OBJ_NAME,
l_program,
l_machine,
SYS_CONTEXT('USERENV','IP_ADDRESS'),
l_osuser,
l_sql_id,
l_sql_text );
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/