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 /u01/db/product/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$;