Auditing in Oracle

Auditing DML operations on table level

Audit update,delete,insert on scott.emp by access;

Auditing DDL operations

AUDIT create table,alter table,drop table By scott whenever sucessful;

To turn off all the auditing the statement is.
NOAUDIT ALL;
NOAUDIT ALL ON SCOTT.EMP;

To Check the auditing table details and the session information use below
select * from dba_audit_trail where username='SCOTT' and timestamp like '28-MAY-10%'
select * from aud$
select * from DBA_AUDIT_OBJECT where username ='SCOTT'


The Data dictionary views related auditing are
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL