권한 부여 SYS계정으로 트리거 생성자에게
-- 권한부여
grant select on sys.v_$session to CYKIM;
-- 부여 확인
SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE ='CYKIM'
AND TABLE_NAME LIKE 'V_$SESSION'
;
히스토리 테이블 생성
CREATE TABLE TB_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)
);
INDEX 생성
CREATE INDEX IX_DDL_HISTORY_01 ON TB_DDL_HISTORY(OBJECT_NAME);
CREATE INDEX IX_DDL_HISTORY_02 ON TB_DDL_HISTORY(OBJECT_OWNER);
CREATE INDEX IX_DDL_HISTORY_03 ON TB_DDL_HISTORY(EXEC_DTM DESC);
트리거 생성
CREATE OR REPLACE TRIGGER TRIGGER_DDL_HISTORY 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 TB_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;
/
결과 조회