how to start Auditing in oracle Database?

       As per organization policy we should set Auditing on particular user,oracle  database objects etc.
we set this auditing policy to check if  any unwanted operation has been performed on production database.
so my this post shows you how to start auditing on purticular table and user operation.

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/db11g/ad
                                                 ump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB



AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
The following list provides a description of each setting:

    none or false - Auditing is disabled.
    db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
    db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
    xml- Auditing is enabled, with all audit records stored as XML format OS files.
    xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
    os- Auditing is enabled, with all audit records directed to the operating system's audit trail.



SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             268437888 bytes
Database Buffers          146800640 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.

SQL> AUDIT ALL BY scott BY ACCESS;

Audit succeeded.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY scott BY ACCESS;

Audit succeeded.

SQL> AUDIT EXECUTE PROCEDURE BY scott  BY ACCESS;

Audit succeeded.

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE test_tab (
  id  NUMBER
);

SQL>INSERT INTO test (id) VALUES (1);

SQL>UPDATE test SET id = id;

SQL>SELECT * FROM test;

SQL>DELETE FROM test;

SQL>DROP TABLE test;
 
Table created.

SQL>
1 row created.

SQL>
1 row updated.

SQL>
        ID
----------
         1

SQL>
1 row deleted.

SQL>

Table dropped.

SQL>conn /as sysdba


SQL>SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

SQL>set lines 1000
COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SQL>SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail       /*** view as per your Audit_trail option for example for                              
WHERE  owner = 'SCOTT'       xml option v$xml_audit_trail ***/
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
SCOTT      04-JUL-13 12.30.41.310309 PM +05:30 SCOTT      TEST_TAB   CREATE TABLE
SCOTT      04-JUL-13 12.30.41.451926 PM +05:30 SCOTT      TEST_TAB   INSERT
SCOTT      04-JUL-13 12.30.41.457726 PM +05:30 SCOTT      TEST_TAB   UPDATE
SCOTT      04-JUL-13 12.30.41.460754 PM +05:30 SCOTT      TEST_TAB   SELECT
SCOTT      04-JUL-13 12.30.41.464694 PM +05:30 SCOTT      TEST_TAB   DELETE
SCOTT      04-JUL-13 12.30.55.593675 PM +05:30 SCOTT      TEST_TAB   DROP TABLE


                                             *********END***************