Preventing the Audit of Analyze Index in Oracle

As audit records become very huge sometimes it becomes very important to ignore analyze index auditing and make sure to audit create and drop index only instead of analyze index which consumes too much space sometimes.

SQL> audit analyze index;
audit analyze index
      *
ERROR at line 1:
ORA-00956: missing or invalid auditing option

The meaning of the error is

[oracle@node4 ~]$ oerr ora 956
00956, 00000, "missing or invalid auditing option"
// *Cause:  AUDIT or NOAUDIT statement contains an invalid auditing option.
// *Action:  Use a valid option.

As we know that the records consume a lot of space in the SYS.AUD$ table. So it would make sense that you just audit the creation of indexes and drop of the indexes.

    SQL> audit index;
    Audit succeeded.
 
    SQL> connect orauser/orauser
    Connected.
    SQL> analyze index scott.SYS_C001031 compute statistics;
    Index analyzed.
 
    SQL> connect system/manager
    Connected.
 
    SQL> select USERNAME,OWNER,OBJ_NAME,ACTION,ACTION_NAME,PRIV_USED
         from dba_audit_trail where username='orauser';
 
    USERNAME   OWNER   OBJ_NAME      ACTION ACTION_NAME    PRIV_USED            
    ---------- ------- ------------ ------- -------------- --------------
    orauser    SCOTT   SYS_C001031       63 ANALYZE INDEX  ANALYZE ANY

 
So the INDEX statement includes the ANALYZE ANY privilege to be audited.

So how to do it ?

Audit the system privileges CREATE ANY INDEX and DROP ANY INDEX instead of auditing the INDEX statement that includes the ANALYZE ANY privilege:
 
1. Disable the INDEX auditing:

    SQL> noaudit index;
    Noaudit succeeded.

2. Audit the CREATE ANY INDEX and DROP ANY INDEX system privileges:

    SQL> audit create any index by orauser;
    Audit succeeded.
 
    SQL> audit drop any index by orauser;
    Audit succeeded.
 
    SQL> connect orauser/orauser;
    Connected.
 
    SQL> analyze index scott.SYS_C001031 compute statistics;
    Index analyzed.
 
    SQL> connect system/manager
    Connected.
 
    SQL> select * from dba_audit_trail;
    no rows selected