How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4 check parameter
SQL> show parameter audit_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string          /d011/db11i/db/oaproddb/10.2.0/rdbms/audit
audit_sys_operations            boolean     TRUE
audit_syslog_level                string
audit_trail                            string           DB

Step 5. Log in as sysdba.

 SQL> audit all by apps by access;
     
OR
        Give the audit permission for delete and drop objects.

        SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

 SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate - 1

SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;