• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • 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

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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