469,909 Members | 1,741 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

How to track structural changes in db

LC
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
Jul 19 '05 #1
3 4499

"LC" <le*******@hotmail.com> wrote in message
news:d1*************************@posting.google.co m...
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

Best way is to not give them the rights to do any. If you have to then
create a trigger on ddl changes and track the user and when in a table. See
SQL Reference documentation.
Jim
Jul 19 '05 #2
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.
Jul 19 '05 #3
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.

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by davidrubin | last post: by
2 posts views Thread by adam.spamfree | last post: by
5 posts views Thread by Daniel Walzenbach | last post: by
7 posts views Thread by Mike McGavin | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.