Where is the Audit Trailand how does it work?
The Audit Trail is kept in an internal table, SYS.AUD$ or in an external file to the database. Which is used depends on the AUDIT_TRAIL initialization parameter. Many people keep the audit trail records in the SYS.AUD$ for safe keeping. Before you can use it, you have to set the AUDIT_TRAIL initialization parameter to DB or TRUE and then bounce your database. Then, you have to tell the database what to audit. This is done with the AUDIT command. You can audit database access or access to specific tables. Oracle9i also introduces fine-grained auditing where you can audit access to specific records in specific rows. Once you've set up auditing and told the database what to audit, everytime that condition is met, a record is placed in the audit trail. You can query the audit trail, if it is in SYS.AUD$, by query the DBA_AUDIT_* views.
One of Oracle's best experts on database security is Pete Finnigan. He has a Web site with plenty of examples and white papers on auditing in an Oracle database.