Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Audit all objects of a schema

I'm working on Oracle EE 8.1.7 .
 
I want to audit any alter,drop,truncate etc. on all the objects of a particular schema.

How an I attain this?
0
cheemahs
Asked:
cheemahs
1 Solution
 
ser6398Commented:
You can use Oracle LogMiner to gather this information.  You can find out details about LogMiner here:

http://demo.oracle.com.pl/8i/database/features/logminer.html

0
 
dpd0809Commented:
You can also turn the auditing feature on all objects of the table.  Then, they are stored in the audit tables.  You need a) plenty of space to store this data b) ability to deal with slower response of the database c) Plenty of disk space in your archive log directory, if you are in archved log mode.
0
 
sunrock_inCommented:
The way to use Auditing is

connect as dba
spool <filename>
select 'audit all on '||table_name||';' from dba_tables
where owner='SCHEMANAME';
Spool off

@filename

optionally you can add 'whenever successfull' or whenever not successfull' options to get results when the statements executed are successfull or not.

to switch off auditing

connect as dba
spool <filename>
select 'noaudit all on '||table_name||';' from dba_tables
where owner='SCHEMANAME';
Spool off

@filename

You can get all the information required about the user/terminal/host from dba_audit_trail table or
aud$ view.

Thanks
Sunrock_in
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now