Querying audit trail data in the SYS_AUDIT_TRAIL system table
Users with administrator rights can query the SYS_AUDIT_TRAIL table by using normal SQL syntax.
Example: Viewing the SYS_AUDIT_TRAIL system table
SQL Query: SELECT CREATIME, LOGIN_USER, SQLSTR FROM SYS_AUDIT_TRAIL
Result:
CREATIME LOGIN_USER SQLSTR
2009-03-05 13:21:31 _SYSTEM AUDIT TRAIL ENABLED
2009-03-05 13:21:42 DBA CREATE USER DBUSER IDENTIFIED BY
2009-03-05 13:23:13 DBA CREATE SCHEMA DBA2
2009-03-05 13:23:23 DBA DROP SCHEMA DBA2
2009-03-05 13:23:24 DBA CREATE USER DBA2 IDENTIFIED BY
2009-03-05 13:32:22 DBUSER CREATE TABLE TEST (ID INTEGER)
2009-03-05 13:49:37 DBA CREATE CATALOG DBUSER
Example: Querying CREATE USER operations
SQL Query: SELECT CREATIME, LOGIN_USER, SQLSTR FROM SYS_AUDIT_TRAIL WHERE type='CREATE USER'
Result:
CREATIME LOGIN_USER SQLSTR
2009-03-05 13:21:42 DBA CREATE USER DBUSER IDENTIFIED BY
2009-03-05 13:23:24 DBA CREATE USER DBA2 IDENTIFIED BY
Go up to