Oracle 12c - getting IMPORT_TABLE_STATS from view

2
votes

I have some code running on Oracle 11g, we are migrating to 12c (12.2.0.1.0).
In one of the processing procedure DBMS_STATS.IMPORT_TABLE_STATS is invoked and in stattab parameter name of the view is provided.
The view is a simple select query from one table, one column is computed by decode function, other are taken as they are in the source column. The user that invokes IMPORT_TABLE_STATS is the owner of the destination table, view and table under the view.
In 11g code is working, in 12c I receive following error:

ORA-20000: Object does not exist or insufficient privileges.

Any ideas about reason? Are there changes in 12c version of DBMS_STATS implementation, that forbids the use of view as a source for IMPORT_TABLE_STATS?

1
oraclevieworacle12coptimizer-statistics
Out of curiosity, does it work if you run it as sysdba, or grant the user ANALYZE ANY? - kfinity
On first reading your question, my reaction was: you hacked DMBS_STATS and found out that IMPORT_TABLE_STATS accepts a view instead of the official CREATE_STAT_TABLE table, and now are unhappy that this hack doesn't work in 12.2. Having said that, I find such creativity impressive and certainly worth some investigation. - wolφi

1 Answers

0
votes

The tables created by CREATE_STAT_TABLE in 11.2 and 12.2 are different. Your view should at least look a bit like the official table if you want IMPORT_TABLE_STATS to swallow it.

Column    11.2                12.2
statid    VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
type      CHAR(1 CHAR)        CHAR(1 BYTE)
version   NUMBER              NUMBER
flags     NUMBER              NUMBER
c1        VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
c2        VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
c3        VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
c4        VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
c5        VARCHAR2(30 CHAR)   VARCHAR2(128 BYTE)
c6        -                   VARCHAR2(128 BYTE)
n1        NUMBER              NUMBER
n2        NUMBER              NUMBER
n3        NUMBER              NUMBER
n4        NUMBER              NUMBER
n5        NUMBER              NUMBER
n6        NUMBER              NUMBER
n7        NUMBER              NUMBER
n8        NUMBER              NUMBER
n9        NUMBER              NUMBER
n10       NUMBER              NUMBER
n11       NUMBER              NUMBER
n12       NUMBER              NUMBER
n13       -                   NUMBER
d1        DATE                DATE
R1        RAW(32)             RAW(1000)
R2        RAW(32)             RAW(1000)
R3        -                   RAW(1000)
CH1       VARCHAR2(1000 CHAR) VARCHAR2(1000 BYTE)
CL1       CLOB                CLOB
BL1       -                   BLOB