Dbms_stats.import_table_stats has Different Behavior on Different Version (Doc ID 2439592.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• After setting a specified table's statistics preferences  'PUBLISH' to 'FALSE',use the dbms_stats.import_table_stats to import statistics which is exported before
  will have different behavior on different version:
  
  
  • 11.2.0.1 &11.2.0.2  : the import statistic will be published.
  • Ver >=11.2.0.3       : the import statistic will not be published, and be pending.

  TEST CASE:

      1) Gather Table Stats on a table and set table to Pending Stats
         exec dbms_stats.gather_table_stats(ownname=>'USER1',tabname=>'T1',cascade=>TRUE);
         exec DBMS_STATS.SET_TABLE_PREFS('USER1', 'T1', 'PUBLISH', 'FALSE');
      2) export Stats
         exec dbms_stats.create_stat_table('USER1','ST1');
         exec dbms_stats.export_table_stats( ownname=>'USER1', tabname=>'T1', statown=>'USER1', stattab=>'ST1');
      3) delete Stats of the table
         exec dbms_stats.delete_table_stats( ownname=>'USER1', tabname=>'T1');
      4) import Stats of the table which is backup by 1)
         exec dbms_stats.import_table_stats( ownname=>'USER1', tabname=>'T1', statown=>'USER1', stattab=>'ST1');

•   11.2.0.1 &11.2.0.2: the statistic will be published:
  
      Example output:

Changes

 PSR update

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.