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!
Hello,
I have follow same steps as mention above. But when I am trying to execute INSERT statement it throws me an error that
ERROR at line 1:
ORA-00904: “T1”: invalid identifier
I checked and found that old table is not having few columns i.e. T1,C6,N13,R3,CL1.
Please help me to resolve the issue.
Thanks.