Oracle 11g扩展统计信息(extension statistics)导致迁移19c报ORA-39083&&ORA-00904
前言
遇到个很有意思的问题。
11g迁移19c的过程，在19c impdp的时候遇到如下报错。
一开始怀疑是虚拟列的问题，于是11g查询dba_tab_cols视图，有如下：
select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='' and table_name='';
而在19c导入报错后，同样查询：
select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN from dba_tab_cols where owner='' and table_name='';
19c的查询结果里边没有类似于SYS_STUJ3KC#SWJO6OK6YG7EPBX9YG那一大长串的列。
SYS_NC0004X$很好理解，这个是可能创建函数索引之后的辅助虚拟列，这个表是有一个函数索引的。
由于这类信息都由系统自动命名，名字不同很正常。
参考：Oracle中的SYS_NC00$ SYS_C000$ SYS_STU SYS_STS 和虚拟字段。
于是，在19c的impdp加上sqlfile查看实际上在数据库中跑的sql。在生成的sqlfile文件的最后，有一段sql如下：
-- fixup virtual columns... -- CONNECT SYS ALTER TABLE "XXXXXX"."XXXXXX" MODIFY ("SYS_STUJ3KC#SWJO6OK6YG7EPBX9YG" NUMBER GENERATED ALWAYS AS (SYS_OP_COMBINED_HASH("XXX_NO","XXX_DATE")) VIRTUAL ); -- done fixup virtual columns
怀疑这个是19c impdp版本的新特性，于是在11g端同样使用sqlfile生成文件，则没有看到类似的fixup virtual columns。
确定这个是19c的新特性，当然也可能是12c以后的。
但是网上和mos都搜索不到啥，自己研究了下把报错模拟出来了。
结论
首先，先说为什么。
这个是由于11g中使用了拓展统计信息导致的。
我们在收集列的统计信息与直方图时，往往都是对某一列的收集。当谓词使用多个相关列时，会导致约束条件的冗余。这几个相关的列也被称作关联列。出现这种情况时，查询优化器也会做出不准确的判断。所以我们必须对这些相关列收集统计信息或直方图来描述这种依赖关系。
幸运的是，从Oracle 11g开始，数据库可以收集基于表达式或者一组列上的对象统计信息和直方图，从而解决这种问题。这种新的统计叫做扩展的统计信息（extension statistics）。
这种技术实际上是基于表达式或一组列创建一个隐藏列，叫做扩展（extension），再在扩展列上收集统计信息与直方图。
过程模拟
11g创建测试用户和测试表。
create user zkm identified by oracle; grant dba to zkm; CREATE TABLE zkm.test ( c1 NUMBER(6), c2 NUMBER(8,2), c3 NUMBER(8,2) ); select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual; create index zkm.test on zkm.test(NVL(c3,0)); col column_name for a50 col DATA_DEFAULT for a50 set line 500 select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST';
09:46:38 SYS@testdb(1155)> create user zkm identified by oracle; User created. Elapsed: 00:00:00.03 09:46:47 SYS@testdb(1155)> grant dba to zkm; Grant succeeded. Elapsed: 00:00:00.01 09:46:51 SYS@testdb(1155)> CREATE TABLE zkm.test 09:47:01 2 ( 09:47:06 3 c1 NUMBER(6), 09:47:06 4 c2 NUMBER(8,2), 09:47:06 5 c3 NUMBER(8,2) 09:47:06 6 ); Table created. Elapsed: 00:00:00.01 09:50:19 SYS@testdb(1155)> desc zkm.test Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER(6) C2 NUMBER(8,2) C3 NUMBER(8,2) 09:50:20 SYS@testdb(1155)> select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST','ZKM') -------------------------------------------------------------------------------- CREATE TABLE "ZKM"."TEST" ( "C1" NUMBER(6,0), "C2" NUMBER(8,2), "C3" NUMBER(8,2) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENT S 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_F LASH_CACHE DEFAULT) TABLESPACE "USERS" Elapsed: 00:00:00.24 09:52:01 SYS@testdb(1155)> create index zkm.test on zkm.test(NVL(c3,0)); Index created. Elapsed: 00:00:00.01 09:52:48 SYS@testdb(1155)> col column_name for a50 09:53:01 SYS@testdb(1155)> col DATA_DEFAULT for a50 09:53:08 SYS@testdb(1155)> set line 500 09:53:13 SYS@testdb(1155)> select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST'; COLUMN_NAME DATA_DEFAULT VIRTUAL_C HIDDEN_CO -------------------------------------------------- -------------------------------------------------- --------- --------- C1 NO NO C2 NO NO C3 NO NO SYS_NC00004$ NVL("C3",0) YES YES Elapsed: 00:00:00.01
接下来创建扩展统计信息(extension statistics)。
09:55:41 SYS@testdb(1155)> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'ZKM',TABNAME => 'TEST',EXTENSION => '(c1,c2)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'ZKM',TABNAME=>'TEST',EXTENSION=>'(C1,C2)') ---------------------------------------------------------------------------------------- SYS_STUF3GLKIOP5F4B0BTTCFTMX0W Elapsed: 00:00:00.22 09:55:42 SYS@testdb(1155)> select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST'; COLUMN_NAME DATA_DEFAULT VIRTUAL_C HIDDEN_CO -------------------------------------------------- -------------------------------------------------- --------- --------- C1 NO NO C2 NO NO C3 NO NO SYS_NC00004$ NVL("C3",0) YES YES SYS_STUF3GLKIOP5F4B0BTTCFTMX0W SYS_OP_COMBINED_HASH("C1","C2") YES YES Elapsed: 00:00:00.00
在11g将用户zkm导出(过程略)。
create directory dir20210712 as '/home/oracle/daily/20210712'; expdp ' / as sysdba ' directory=dir20210712 dumpfile=zkm.dmp logfile=zkm.log cluster=n schemas=zkm
将文件zkm.dmp传至19c环境上。
创建directory后执行导入操作成功将报错模拟出来：
[oracle@testdb 20210712]$ impdp ' / as sysdba ' directory=dir20210712 dumpfile=zkm.dmp logfile=zkm.log cluster=n Import: Release 19.0.0.0.0 - Production on Mon Jul 12 10:17:29 2021 Version 19.11.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dir20210712 dumpfile=zkm.dmp logfile=zkm.log cluster=n Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "ZKM"."TEST" 0 KB 0 rows ORA-39083: Object type TABLE:"ZKM"."TEST" failed to create with error: ORA-00904: "SYS_STUF3GLKIOP5F4B0BTTCFTMX0W": invalid identifier Failing sql is: ALTER TABLE "ZKM"."TEST" MODIFY ("SYS_STUF3GLKIOP5F4B0BTTCFTMX0W" NUMBER GENERATED ALWAYS AS (SYS_OP_COMBINED_HASH("C1","C2")) VIRTUAL ) Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Jul 12 10:17:41 2021 elapsed 0 00:00:12
使用sqlfile有如下信息：
-- fixup virtual columns... ALTER TABLE "ZKM"."TEST" MODIFY ("SYS_STUF3GLKIOP5F4B0BTTCFTMX0W" NUMBER GENERATED ALWAYS AS (SYS_OP_COMBINED_HASH("C1","C2")) VIRTUAL ); -- done fixup virtual columns
19c查看dba_tab_cols，有：
col column_name for a50 col DATA_DEFAULT for a50 set line 500 select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST';
10:22:32 SYS@19ctestdb(30)> set line 500 10:22:38 SYS@19ctestdb(30)> col column_name for a50 10:23:00 SYS@19ctestdb(30)> col DATA_DEFAULT for a50 10:23:00 SYS@19ctestdb(30)> set line 500 10:23:00 SYS@19ctestdb(30)> select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST'; COLUMN_NAME DATA_DEFAULT VIRTUAL_C HIDDEN_CO -------------------------------------------------- -------------------------------------------------- --------- --------- C1 NO NO C2 NO NO C3 NO NO SYS_NC00004$ NVL("C3",0) YES YES Elapsed: 00:00:00.01
解决方法
11g迁移19c遇到这个问题，由于原因是扩展统计信息(extension statistics)引起的，因此忽略该报错。
手工在19c上创建拓展统计信息：
10:26:39 SYS@19ctestdb(30)> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'ZKM',TABNAME => 'TEST',EXTENSION => '(c1,c2)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>'ZKM',TABNAME=>'TEST',EXTENSION=>'(C1,C2)') ------------------------------------------------------------------------------------------ SYS_STUF3GLKIOP5F4B0BTTCFTMX0W Elapsed: 00:00:00.05 10:27:26 SYS@19ctestdb(30)> select COLUMN_NAME,DATA_DEFAULT,VIRTUAL_COLUMN,HIDDEN_COLUMN from dba_tab_cols where owner='ZKM' and table_name='TEST'; COLUMN_NAME DATA_DEFAULT VIRTUAL_C HIDDEN_CO -------------------------------------------------- -------------------------------------------------- --------- --------- SYS_STUF3GLKIOP5F4B0BTTCFTMX0W SYS_OP_COMBINED_HASH("C1","C2") YES YES C1 NO NO C2 NO NO C3 NO NO SYS_NC00004$ NVL("C3",0) YES YES Elapsed: 00:00:00.00
或者，等官方出正式解决方法比如补丁或者该参数啥的其他方式。
至此。