Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Disappearing Triggers!
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)
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