grant change notification permissions

=======================

Oracle server Permissions

=======================

What to check

SQL> show parameter statistics

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------

client_statistics_level              string      TYPICAL

optimizer_adaptive_statistics        boolean     FALSE

optimizer_real_time_statistics       boolean     FALSE

optimizer_use_pending_statistics     boolean     FALSE

statistics_level                     string      BASIC

timed_os_statistics                  integer     0

timed_statistics                     boolean     FALSE 

statistics_level should be set to TYPICAL
timed_statistics should be set to TRUE

statistics_level

TYPICAL  - Is the default 

BASIC  - disables the collection of many of the important statistics

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

timed_statistics

Specifies whether statistics related to time are collected.

=======================

Schema Permissions

=======================

What to check

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE privilege = 'CHANGE NOTIFICATION' AND grantee = 'XXX';

sqlplus / as sysdba
grant change notification to vipcreator with admin option;
exit;

sqlplus vipcreator/xxxxxxx@orainst
SELECT 'grant change notification to '||USERNAME||';'
FROM ALL_USERS
WHERE ORACLE_MAINTAINED = 'N';

Execute the sqls in output 
GRANT CHANGE NOTIFICATION TO USERNAME;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO 
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO USERNAME;

Check Change Notification Permission:

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE privilege = 'CHANGE NOTIFICATION' 

   AND grantee = 'USERNAME';

Once there is notification, data will go into table DBA_CHANGE_NOTIFICATION_REGS

Table Level

See if monitoring is enabled
SELECT monitoring, count(1) 

  FROM USER_TABLES 

 GROUP BY MONITORING;

See if monitoring is logged to USER_TAB_MODIFICATIONS 

SELECT table_name, timestamp 

  FROM USER_TAB_MODIFICATIONS 

 WHERE table_name = 'SGA_W_LOG';

Flash writes to USER_TAB_MODIFICATIONS 

BEGIN

 DBMS_STATS.flush_database_monitoring_info;

END;

/

After flush, data should be in USER_TAB_MODIFICATIONS 

SELECT table_name, timestamp 

  FROM USER_TAB_MODIFICATIONS 

 WHERE table_name = 'SGA_W_LOG';