Re: Disappearing Triggers!

Norman,

That looks like a useful little one to put in the FAQ if you can find a half hour to write it into the template

Suggested question:
'How can I track structural changes to my database ?'

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Norman Dunbar wrote in message ...


>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.

>

>

>