행위

오라클 DDL 트리거

DB CAFE

1 권한 부여 SYS계정으로 트리거 생성자에게

-- 권한부여 
grant select on sys.v_$session to CYKIM;

-- 부여 확인
SELECT *
 FROM DBA_TAB_PRIVS
WHERE GRANTEE ='CYKIM'
  AND TABLE_NAME LIKE 'V_$SESSION'  
 ;

2 히스토리 테이블 생성

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)
);

3 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);

4 트리거 생성

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;
/

5 결과 조회

SELECT * FROM TB_DDL_HISTORY

출처 : https://lawmin.tistory.com/288