grant change notification permissions. And trc files...

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

Oracle server Permissions

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

What to check

Reference

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

col GRANTEE for A30
col PRIVILEGE for A30
col ADMIN_OPTION for A30

SELECT grantee, privilege, admin_option, common, inherited
  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 XXX;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO USERNAME;

Check Change Notification Permission:

SELECT grantee, privilege, admin_option, common, inherited
  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 

COL TABLE_OWNER FOR A30

COL TABLE_NAME FOR A30

SELECT table_owner, table_name, timestamp 

  FROM DBA_TAB_MODIFICATIONS

ORDER BY table_owner, table_name;

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';

trc files under trace folder

Multiple trc file, with same text are generated under trace folder, with text like:

kpondGetCQNRegId(): subname: CHNF29418 regid 29418

kpondGetCQNRegId(): subname: CHNF29416 regid 29416

kpondGetCQNRegId(): subname: CHNF29414 regid 29414

kpondGetCQNRegId(): subname: CHNF29412 regid 29412

set linesize 400
col username for A24

col table_name FOR A40

col callback for A80

SELECT regid username, table_name, regflags, callback

  FROM DBA_CHANGE_NOTIFICATION_REGS

 WHERE regid iN (29412, 29414, 29416, 29418);

 

REGID USERNAME        TABLE_NAME                                 REGFLAGS 

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

CALLBACK

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

29412 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.52)(PORT=47633))?PR=0

29414 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.53)(PORT=47632))?PR=0

29416 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.53)(PORT=47633))?PR=0

29418 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.52)(PORT=47632))?PR=0

SELECT table_name, count(*) 

  FROM DBA_CHANGE_NOTIFICATION_REGS 

GROUP BY table_name;

 

TABLE_NAME                                                     COUNT(*)

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

SCHEMA_NAME.IPN_TRIGGER_NOT_REPLICATED                           32

SCHEMA_NAME.GA_PROPERTY_VALUES_DEF                                4

SCHEMA_NAME.SHM_SERVICES                                         22

SCHEMA_NAME.IPN_INVALIDATE_TRIGGER                               12

SCHEMA_NAME.IPN_SOD_JOBS                                          2

SCHEMA_NAME.FIVEG_REGISTRATION                                    4

SCHEMA_NAME.SHM_SERVICES_ACTIVE_SITE                             12

SCHEMA_NAME.SHM_SERVICE_PROCESSES                                22

SCHEMA_NAME.GA_PROPERTIES_CONF                                    4

SCHEMA_NAME.GA_PROPERTY_VALUES_CONF                               4

SCHEMA_NAME.CGA_ITEMS                                             4

SCHEMA_NAME.GA_PRODUCT_TREE_CONF                                  1

SCHEMA_NAME.IPN_SOD_JOBS_TRIGGER                                 10

SCHEMA_NAME.GA_PROPERTIES_DEF                                     4

SCHEMA_NAME.REP_COUNTERS                                         12

SCHEMA_NAME.IPN_IOT_OTA_SEG_JOBS                                  2