The Difference between DB and DB_EXTENDED

When doing Audit on any table on the the database , the default auditing is DB.

SQL > show parameters audit_trail

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

in this case , when you do audit on some table.

SQL> audit all on scott.emp by access;

Audit succeeded

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

3 rows updated.

if you want to know the statement made these changes, you will receive nothing on the sql_text field while you are selecting the audit_trial table.



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

Here is the difference between DB and DB_Extended shows. 

When you alter the db to db_extended , the sql_text field will be displayed.

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=sal*0.95 where job='MANAGER'