ORA-00932 to Export Statistics After 12c Upgrade – Undocumented

Hello All,
I have a client that use to Export and Import Dictionary Statistics using a stats table. However, after upgrading database to 12c, is started to raise error below to export data to a stats table (created in 11g) or even to update stats in this table.

SQL> exec dbms_stats.upgrade_stat_table('GREPORA','MYSTATSTABLE');
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at line 28

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );
BEGIN DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' ); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 37085
ORA-06512: at line 1

I reviewed several references (all very interesting, by the way, recommend you to take a look):
– ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
– Datapump Export Fails With Ora-00932 (Doc ID 1300803.1)
– Error “ORA-00932 – Inconsistent Datatypes: Expected BINARY Got NUMBER” When Executing the Primavera Gather-Statistics Scripts on an Oracle 12c Database (Doc ID 2111114.1)
– https://oracle-base.com/articles/12c/concurrent-statistics-collection-12cr1
– http://www.morganslibrary.org/reference/pkgs/dbms_stats.html
– How To Export and Import Dictionary Statistics (Doc ID 1450820.1)
– Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

But no reference found to this specific issue, not even in MOS.

After some tests, I realized that maybe columns order in stats table can be changed in implementation between 11g and 12c. Bingo! The same columns and datatypes are in place, but in different order.

This is not documented in MOS neither on internet/independent blogs, but seems EXPORT% on DBMS_STATS presume stats table in it’s internal code without specified column orders.
As we know, a good development practice is to specify column names when performing any operation. Bad on you, Oracle!

After creating table “compatible” to 12.1 expected order, I reimported old data and issue was solved.
Note that I had to explicitly mention column order for that. Then, no errors found. See example below:

SQL> desc GREPORA.MYSTATSTABLE
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID 					    VARCHAR2(128)
 TYPE						    CHAR(1)
 VERSION					    NUMBER
 FLAGS						    NUMBER
 C1						    VARCHAR2(128)
 C2						    VARCHAR2(128)
 C3						    VARCHAR2(128)
 C4						    VARCHAR2(128)
 C5						    VARCHAR2(128)
 N1						    NUMBER
 N2						    NUMBER
 N3						    NUMBER
 N4						    NUMBER
 N5						    NUMBER
 N6						    NUMBER
 N7						    NUMBER
 N8						    NUMBER
 N9						    NUMBER
 N10						    NUMBER
 N11						    NUMBER
 N12						    NUMBER
 D1						    DATE
 R1						    RAW(1000)
 R2						    RAW(1000)
 CH1						    VARCHAR2(1000)
 CL1						    CLOB
 C6						    VARCHAR2(128)
 R3						    RAW(1000)
 N13						    NUMBER
 T1						    TIMESTAMP(6) WITH TIME ZONE


SQL> create table GREPORA.MYSTATSTABLE_old as select * from GREPORA.MYSTATSTABLE;

Table created.

SQL> commit;

Commit complete.

SQL> drop table GREPORA.MYSTATSTABLE;

Table dropped.

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('GREPORA','MYSTATSTABLE');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );

PL/SQL procedure successfully completed.

SQL> 

SQL> desc GREPORA.MYSTATSTABLE
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID 					    VARCHAR2(128)
 TYPE						    CHAR(1)
 VERSION					    NUMBER
 FLAGS						    NUMBER
 C1						    VARCHAR2(128)
 C2						    VARCHAR2(128)
 C3						    VARCHAR2(128)
 C4						    VARCHAR2(128)
 C5						    VARCHAR2(128)
 C6						    VARCHAR2(128)
 N1						    NUMBER
 N2						    NUMBER
 N3						    NUMBER
 N4						    NUMBER
 N5						    NUMBER
 N6						    NUMBER
 N7						    NUMBER
 N8						    NUMBER
 N9						    NUMBER
 N10						    NUMBER
 N11						    NUMBER
 N12						    NUMBER
 N13						    NUMBER
 D1						    DATE
 T1						    TIMESTAMP(6) WITH TIME ZONE
 R1						    RAW(1000)
 R2						    RAW(1000)
 R3						    RAW(1000)
 CH1						    VARCHAR2(1000)
 CL1						    CLOB

SQL> insert into GREPORA.MYSTATSTABLE select * from GREPORA.MYSTATSTABLE_old;
insert into GREPORA.MYSTATSTABLE select * from GREPORA.MYSTATSTABLE_old
                                            *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL> insert into GREPORA.MYSTATSTABLE(STATID,TYPE,VERSION,FLAGS,C1,C2,C3,C4,C5,N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,D1,R1,R2,CH1,CL1,C6,R3,N13,T1) select STATID,TYPE,VERSION,FLAGS,C1,C2,C3,C4,C5,N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,D1,R1,R2,CH1,CL1,C6,R3,N13,T1 from GREPORA.MYSTATSTABLE_old;

9668186 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> drop table GREPORA.MYSTATSTABLE_old;

Table dropped.

SQL>

Hope it helps you!
See you next week!