Who Dropped Table?

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Thu, 18 Apr 2002 11:43:41 -0800
Message-ID: <F001.004488FF.20020418114341@fatcity.com>

Goofing around with a procedure to log DROP TABLE and CREATE TABLE to the alert log. Thought I would share it with you. This is easier to implement than auditing in my opinion. I use this is very stable environments, my alert log monitor will send me an email when the event is triggered. I have been using the sys.dbms_system.ksdwrt more and more lately and let me log monitor parse the results. I also log some server error events to the log, I will include that below. This helps me identify specific sessions having problems in the database. Enjoy!

• Ethan

create or replace trigger tk$ddl_event after DROP or CREATE

   on database
declare

   mysession v$session%rowtype;
begin

   if ora_dict_obj_type = 'TABLE' then

      select * into mysession from v$session where sid = (select distinct sid from v$mystat);

      sys.dbms_system.ksdwrt(2,'DDL-' || ora_sysevent || ' ' || ora_dict_obj_type ||

         ' ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' ||
         'SESSION ' || mysession.sid || ',' || mysession.serial# || ' USER '
||
         mysession.username || ',' || mysession.osuser || ' PROGRAM ' ||
mysession.program ||
         ',' || mysession.machine || ' OBJECT ' || ora_dict_obj_owner || '.'

|| ora_dict_obj_name);

   end if;
exception

   when others then

      dbms_output.put_line(dbms_utility.format_error_stack); end;

create or replace trigger tk$servererror after servererror

   on database
declare

   mysession v$session%rowtype;
begin

• Some errors are not logged...
• 1 - Key violation.
• 1013 - Use canceled operation.
• 1400 - Can not insert null.
• 1418 - Index does not exist.
• 1722 - Invalid number.
• 4043 - Object does not exist.
• 6550 - PL/SQL Complilation Failure -- if ora_server_error(1) not between 900 and 999 then if ora_server_error(1) not in (1,1013,1400,1418,1722,4043,6550) then select * into mysession from v$session where sid = (select distinct sid from v$mystat); sys.dbms_system.ksdwrt(2,'SERVERERROR[' || ora_server_error(1) || '] SESSION ' || mysession.sid || ',' || mysession.serial# || ' USER ' || mysession.username || ',' || mysession.osuser || ' PROGRAM ' || mysession.program || ',' || mysession.machine); end if; end if; exception when others then dbms_output.put_line(dbms_utility.format_error_stack); end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Apr 18 2002 - 14:43:41 CDT