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
ku****@questdiagnostics.com (abhay) wrote in message news:<23**************************@posting.google. com>...
le*******@hotmail.com (LC) wrote in message news:<d1*************************@posting.google.c om>... 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.
