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

"LC" <leecholim (AT) hotmail (DOT) com> wrote

create a trigger on ddl changes and track the user and when in a table. See
SQL Reference documentation.
Jim

leecholim (AT) hotmail (DOT) com (LC) wrote in message news:<d15e389.0406240044.65c6e9cd (AT) posting (DOT) google.com>...
At least in case of 9.2.0.2.0 database, you can use APPLICATION_DDL trigger.

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@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@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 (DOT) com (abhay) wrote in message news:<23185450.0406240812.7d77ed6b (AT) posting (DOT) google.com>...