Newsgroups: comp.databases.oracle.server
From: Norman Dunbar <Norman.Dunbar@lfs.co.uk>
Subject: Disappearing Triggers!
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7029FAB8F@lnewton.leeds.lfs.co.uk>
Sender: Norman Dunbar <Norman.Dunbar@lfs.co.uk>
Date: Mon, 9 Sep 2002 16:14:19 +0100 
X-Newsreader: Microsoft (R) Exchange Internet News Service Version 5.5.2653.11
References: <677227cc.0209071142.14576b10@posting.google.com> <Xns9282A07B1F59SunnySD@68.6.19.6> <677227cc.0209090642.5060f8a8@posting.google.com>
Lines: 56
NNTP-Posting-Host: mailhost3.lfs.co.uk
X-Trace: 1031584519 reading.news.pipex.net 1289 193.129.247.6
X-Complaints-To: abuse@uk.uu.net


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.