Monitor all DDL statements at the database
                                                 Last update (2010-07-15 13:24:33)
                                                                                                                   Date added (2008-02-15 12:19:03)

Summary
You can create a "monitor procedure" that monitors all DDL statements at the database and notifies for example about DROP or TRUNCATE

Explanation
You need to create:
1. One trigger(DDL_MONITOR) that catches all the DDL statements
2. One table that the DDL stored(HELPDESK.S_DDL_MONITOR)
3. One procedure that sends emails to the DBA recipients

2. One table where the DDL statements stored
CREATE TABLE HELPDESK.S_DDL_MONITOR
(
  DATE_TIME  DATE,
  DDL_EVENT  VARCHAR2(500),
  OS_USER    VARCHAR2(30),
  MACHINE    VARCHAR2(150)
)
TABLESPACE HELPDESK NOLOGGING NOCACHE NOPARALLEL;

1. One trigger that catches all the DDL.
CREATE OR REPLACE TRIGGER DDL_MONITOR
AFTER CREATE OR DROP OR TRUNCATE OR ALTER ON DATABASE
DECLARE

CURSOR cur_recipients IS SELECT * FROM helpdesk.s_recipients;

   V_TERMINAL V$SESSION.TERMINAL%TYPE;
   V_OSUSER   V$SESSION.OSUSER%TYPE;
   V_MACHINE  V$SESSION.MACHINE%TYPE;
   V_IP       VARCHAR2(20);
   V_DATABASE V$PARAMETER.VALUE%TYPE;
   crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

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 HELPDESK.S_DDL_MONITOR(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 || ')');

IF ora_sysevent = 'DROP' OR ora_sysevent = 'TRUNCATE' THEN

FOR c1 IN cur_recipients 
LOOP 
		    Send_Mail('skaparelis@ote.gr', c1.recipient,  'Subject: ' || 'DB Server:  ' || V_DATABASE || '  ' ||
			'Username: (' || ora_login_user || ')   ' || 'Action: (' || ora_sysevent || ')   ' ||
			'Object: (' || ora_dict_obj_owner||'.'||ora_dict_obj_name || ')   ' ||
			'Type: (' || ora_dict_obj_type || ')' || '   ' || 'Osuser: (' || V_OSUSER || ')' || crlf, 
			'DATE:       ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')  || crlf ||
		    'USERNAME:   ' || ora_login_user || crlf ||
			'ACTION:     ' || ora_sysevent || crlf ||
			'OBJECT:     ' || ora_dict_obj_owner||'.'||ora_dict_obj_name || crlf ||
			'TYPE:       ' || ora_dict_obj_type || crlf ||
		    'OSUSER:     ' || V_OSUSER || crlf ||
		    'MACHINE:    ' || V_IP || ' (' || V_MACHINE || ')');
			
END LOOP;

END IF;

EXCEPTION WHEN OTHERS THEN NULL;

END;
/
Reviews
Filters
Search