Database triggers for auditing

[UKDBA]

Hi
I want to use triggers in order to monitor any create,alter and drop activity in database. Is there any document or script available to implement it. If not then do we know which table of data dictionary
this information records so that i can write a trigger against the table who stores information about changes in database.

Thanks
UKDBA

[davey23uk]

check out the documentation on triggers

but you want something like this

create trigger xxxx
on update, delete, insert on table xxxx

syntax isnt exactly right but its the right area

[UKDBA]

hi
I want to do monitoring on whole database rather than one table.
What is the best way? Please dont mention auditing function of oracle.

UKDBA

[UKDBA]

I have found answer

DDL table -- Using the code snippet shown below, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes.
connect sys/manager;

drop table perfstat.stats$ddl_log;


create table
perfstat.stats$ddl_log
(
user_name varchar2(30),
ddl_date date,
ddl_type varchar2(30),
object_type varchar2(18),
owner varchar2(30),
object_name varchar2(128)
)
tablespace perfstat
;

DDL Trigger -- The DDL trigger executes every time a DDL statement is executed, and adds new entries to the stats$ddl_log table.


connect sys/manager

create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$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;
/