Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Disappearing Triggers!

Disappearing Triggers!

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 9 Sep 2002 16:14:19 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7029FAB8F@lnewton.leeds.lfs.co.uk>

If you are worried about people doing things to your database and you not knowing,
and you are running at 8i then try the following logged in as SYS :

create tablespace logging
datafile 'path to file' size 201m
extent management local
uniform size 64k;

create user logging identified by new_password default tablespace logging
temporary tablespace temp
quota 0 on system;

create table logging.ddl_log
( user_name varchar2(30),

   ddl_date         date,
   ddl_type         varchar2(30),
   object_type      varchar2(18),
   owner            varchar2(30),
   object_name      varchar2(128)

) tablespace logging;

create or replace trigger

   DDLTrigger
AFTER DDL ON DATABASE /* See below for changes to this line */ BEGIN
insert into logging.ddl_log
( user_name, ddl_date, ddl_type, object_type, owner, object_name)
VALUES ( ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name); END;
/

This will log all ddl on the database to the table in the logging user. If yo change the 'DLL' part to 'DROP' or 'LATER' then you only log those DDL commands.

You can find out who'se been doing DDL by :

select * from logging.ddl_log;

HTH Regards,
Norman.

PS. I can't accept any/all the credit - see http://www.dbazine.com/burleson7.html for details. Received on Mon Sep 09 2002 - 10:14:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US