Enable Standard Database Auditing

Enable Standard Auditing

SQL> show parameters audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

SQL> alter system set audit_trail=db scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> Startup
ORACLE instance started.
Total System Global Area  440401920 bytes
Fixed Size                  1249440 bytes
Variable Size             109055840 bytes
Database Buffers          322961408 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

SQL> show parameters audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


SQL> audit all on scott.emp by access;

Audit succeeded.

SQL> update emp set sal=sal*0.95 where job='MANAGER';

3 rows updated.


You will get the following record in the audit trail:

SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;

USERNAME   OWNER      OBJ_NAME             ACTION_NAME     SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT      SCOTT      EMP                  UPDATE


If however you change the audit trail to DB_EXTENDED and restart the database:

SQL> alter system set audit_trail='db_extended' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> Startup
ORACLE instance started.
Total System Global Area  440401920 bytes
Fixed Size                  1249440 bytes
Variable Size             113250144 bytes
Database Buffers          318767104 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> show parameters audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED


The same activity will produce a record with the text:

SQL> select username, owner, obj_name, action_name, sql_text from dba_audit_trail;

USERNAME   OWNER      OBJ_NAME             ACTION_NAME     SQL_TEXT
---------- ---------- -------------------- --------------- --------------------
SCOTT      SCOTT      EMP                  UPDATE
SCOTT      SCOTT      EMP                  UPDATE          update emp set sal=s
                                                           al*0.95 where job='M
                                                           ANAGER'
Note
====
You can also use the following syntax:

SQL> alter system set audit_trail='db','extended' scope=spfile;
System altered.
Or:
SQL> alter system set audit_trail=db,extended scope=spfile;
System altered.

But you can’t use ‘DB,EXTENDED’—which is a little confusing at first:

SQL> alter system set audit_trail='db,extended' scope=spfile;
alter system set audit_trail='db,extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value xml,extended for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db