Monitor all DDL Statements at Database Level

Monitor All DDL Statements at Database Level

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

Description:

            I want to track all DDL statements at Database level.

Solution:

Following the steps to track all DDL statements at Database level:

1-      First to create a Table that store DDL statements

CREATE TABLE DDL_MONITOR_DB

(

  DATE_TIME  DATE,

  DDL_EVENT  VARCHAR2(500),

  OS_USER    VARCHAR2(50),

  MACHINE    VARCHAR2(100)

)

2-      Create a Trigger at Database level to catches all DDL statements

CREATE OR REPLACE TRIGGER DDL_MONITOR

AFTER CREATE OR DROP OR TRUNCATE OR ALTER ON DATABASE

DECLARE

   V_OSUSER   V$SESSION.OSUSER%TYPE;

   V_MACHINE  V$SESSION.MACHINE%TYPE;

   V_IP   VARCHAR2(20);

   V_DATABASE V$PARAMETER.VALUE%TYPE;

BEGIN

      SELECT VALUE INTO V_DATABASE FROM v$parameter WHERE NAME = 'db_name';

                 V_OSUSER  :=SYS_CONTEXT('USERENV','OS_USER');

                 V_MACHINE :=SYS_CONTEXT('USERENV','TERMINAL');

                 V_IP      := SYS_CONTEXT('USERENV','IP_ADDRESS');

INSERT INTO DDL_MONITOR_DB (date_time, ddl_event, os_user, machine)

VALUES (SYSDATE, 'Username: (' || ora_login_user || ')   ' || 'Action: (' || ora_sysevent || ')   ' ||

'Object: (' || ora_dict_obj_owner||'.'||ora_dict_obj_name || ')   ' ||

'Type: (' || ora_dict_obj_type || ')', V_OSUSER, V_IP || ' (' || V_MACHINE || ')');

EXCEPTION WHEN OTHERS THEN NULL;

END;

/

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ