Re: How to track structural changes in db

From: tracy <tracykim10_at_yahoo.com.hk>
Date: 25 Jun 2004 00:48:20 -0700
Message-ID: <5c91cce9.0406242348.f9ff8aa_at_posting.google.com>

hi, Lee Cho
try this as provided by norman dunbar in DBAZine. step 1: create tablespace, user and table using system as below:

conn system/manager_at_test

/* create tablespace*/
create tablespace logging
datafile'/data2/test/logging.dbf' size 200m extent management local
uniform size 64k;

tablespace created

/*create user*/
create user logging identified by daft
default tablespace logging
temporary tablespace temp
quota 0 on system
quota 200m on logging

user created

/* create table*/

create table logging.ddl_log(
user_name varchar2(30),
ddl_date date,
ddl_type varchar2(30),
object_type varchar2(100),
owner varchar2(100),
object_name varchar2(150)
) tablespace logging

table created

---

connect sys_at_test
enter password: XXX
connected.

/*create trigger*/
create or replace trigger DDLtrigger
 after DDL on database
 begin
  insert into logging.ddl_log
  values(ora_login_user,
sysdate,

 ora_sysevent,
 ora_dict_obj_type,
 ora_dict_obj_owner,

  ora_dict_obj_name);
 exception
when others then
  NULL;
 end;

trigger created.

then using a userid and login. simply perform some ddl statemnet.

then query the table :
column user_name format a10
 column ddl_date format a20

  column owner_ format a10
 column object_name format a20
 column object_type format a20

 column ddl_type format a20
 set lines 150
 set pages 50
 select * from logging.ddl_log
/

u will able to see the track.

all the best!

regards,
tracy

kumara_at_questdiagnostics.com (abhay) wrote in message news:<23185450.0406240812.7d77ed6b_at_posting.google.com>...
> leecholim_at_hotmail.com (LC) wrote in message news:<d15e389.0406240044.65c6e9cd_at_posting.google.com>...
> > hi,
> > i worry about people doing something they shouldn't to my db and I
> > would like to track any structural changes (who and which)to my db. I
> > am using oracle 8.0.6.0.0 and 9.2.0.2.0.
> >
> > regards,
> > Lee Cho
>
> At least in case of 9.2.0.2.0 database, you can use APPLICATION_DDL trigger.
Received on Fri Jun 25 2004 - 09:48:20 CEST