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

Creating a Scheduler job with a specific country Time Zone

Let us see how to create a Scheduler job with a specific country Start Time with its respective time zone, Lets say i want to create a scheduler job in the oracle database where its start time should be tiem zone for country PERU because if you specify systimestamp it will take the database time zone which cab be lets say India and to create a job which has a start time of local time of country PERU will be created as below :-


BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'MY_STATS_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2015/05/05 06:00:00.000000  America/Lima','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=06;'
      ,end_date        => NULL
      ,job_class       => 'SOME_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin DBMS_STATS.gather_schema_stats(ownname=>''SCHEMA_NAME'',cascade => TRUE,degree=>4); end;'
      ,comments        => NULL
    );
end;
/

Please note that we used America/Lima because its a time of Lima which is the captial of PERU.

begin
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'MY_STATS_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MY_STATS_JOB');
END;
/