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
