DBMS_METADATA.GET_DDL查出不存在的列SYS_C00014_20070116:47:09$
前言
很久很久以前，有多久呢？
有多久了，等等我看下截图的日期（溜︿(￣︶￣)︿）。
哦，很久很久以前，当时差不多是2018年3月31日下午4点多。
当时是在做一个比较大的项目，在淮安驻场期间，出现的一个数据库比较诡异的问题，截图在如下：
dbms_metadata.get_ddl查出来一个奇葩的列，但是desc却是看不到。
至于为什么会发现这个问题呢，当时做数据库迁移，有的数据库是10g迁移（数据泵）11g的。
结果这张表在11g库中多了一个SYS_C00010_13070615:27:42$的列，在源库就是没看到。
当时一行人找不到原因，甚至拍照截图到QQ的惜分飞总群里问，没人鸟。于是对这张表单独处理了。
所幸现在找到原因了，并且可以模拟出来现象，了了一桩心愿。
环境模拟
数据库版本
SYS@zkm> select * from v$version where rownum=1; BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
构造表T
SYS@zkm> create user scott identified by oracle; User created. SYS@zkm> grant dba to scott; Grant succeeded. SYS@zkm> conn scott/oracle
Connected.
SCOTT@zkm> create table t as select * from dba_objects; Table created. SCOTT@zkm>
需要对T表进行在线重定义，因为这个问题就是由于在线重定义的BUG导致的。
在线重定义表
创建中间表
SCOTT@zkm> create table t_tmp as select * from t where 1=0; Table created.
验证T2是否用于重定义（因没有主键，采用rowid实现）
SCOTT@zkm> exec dbms_redefinition.can_redef_table(user, 't', dbms_redefinition.cons_use_rowid); PL/SQL procedure successfully completed.
执行表的在线重定义
SCOTT@zkm> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_TMP','OWNER OWNER, OBJECT_NAME OBJECT_NAME, SUBOBJECT_NAME SUBOBJECT_NAME, OBJECT_ID OBJECT_ID, DATA_OBJECT_ID DATA_OBJECT_ID, OBJECT_TYPE OBJECT_TYPE, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, TIMESTAMP TIMESTAMP, STATUS STATUS, TEMPORARY TEMPORARY, GENERATED GENERATED, SECONDARY SECONDARY',DBMS_REDEFINITION.cons_use_rowid); PL/SQL procedure successfully completed.
同步数据（可选）
SCOTT@zkm> exec dbms_redefinition.sync_interim_table(user, 'T', 'T_TMP'); PL/SQL procedure successfully completed.
结束在线重定义过程
SCOTT@zkm> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_TMP'); PL/SQL procedure successfully completed.
删除中间表
SCOTT@zkm> drop table t_tmp purge; Table dropped.
这个时候，可以发现多了一个列出现“SYS_C00014_20070117:26:51$”。
SCOTT@zkm> select column_name,hidden_column from user_tab_cols where table_name='T'; COLUMN_NAME HID
------------------------------ ---
OWNER NO
OBJECT_NAME NO
SUBOBJECT_NAME NO
OBJECT_ID NO
DATA_OBJECT_ID NO
OBJECT_TYPE NO
CREATED NO
LAST_DDL_TIME NO
TIMESTAMP NO
STATUS NO
TEMPORARY NO COLUMN_NAME HID
------------------------------ ---
GENERATED NO
SECONDARY NO
SYS_C00014_20070117:26:51$ YES
并且，dbms_metadata.get_ddl以及desc的结果也如同一开始的截图一样奇葩。
SCOTT@zkm> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "SYS_C00014_20070117:26:51$" VARCHAR2(255),
SCOTT@zkm> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)至此，问题已经完全模拟出来。
我们在使用数据泵导出表，看是否连同该unused列也一并导出。
导出，
[oracle@oracle ~]$ expdp \'/ as sysdba\' directory=dir dumpfile=t.dmp logfile=t.log tables=scott.t Export: Release 10.2.0.1. - 64bit Production on Wednesday, July, :: Copyright (c) , , Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1. - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' directory=dir dumpfile=t.dmp logfile=t.log tables=scott.t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T" 4.695 MB rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/t.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at ::
不需要实际导入，加入参数sqlfile即可验证，
[oracle@oracle ~]$ impdp \'/ as sysdba\' directory=dir dumpfile=t.dmp logfile=i_t.log sqlfile=t.sql Import: Release 10.2.0.1. - 64bit Production on Wednesday, July, :: Copyright (c) , , Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1. - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": '/******** AS SYSDBA' directory=dir dumpfile=t.dmp logfile=i_t.log sqlfile=t.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at :: [oracle@oracle ~]$ cat t.sql
-- CONNECT SYS
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."T"
( "SYS_C00014_20070117:26:51$" VARCHAR2(),
"OWNER" VARCHAR2(),
"OBJECT_NAME" VARCHAR2(),
"SUBOBJECT_NAME" VARCHAR2(),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(),
"STATUS" VARCHAR2(),
"TEMPORARY" VARCHAR2(),
"GENERATED" VARCHAR2(),
"SECONDARY" VARCHAR2()
) PCTFREE PCTUSED INITRANS MAXTRANS NOCOMPRESS LOGGING
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
不容易啊...
相关的mos文档为：DBMS_METADATA.GET_DDL Generates Incorrect DDL With UNUSED Columns (Doc ID 737601.1)
这个bug号为Bug 6005996，并且也有补丁，不过补丁只有10.2.0.2.0以及10.2.0.3.0。
所以，除了有补丁的打补丁，或者升级到10.2.0.4.0以上。
另外，也可以删除unused列。
SCOTT@zkm> alter table t drop unused columns;
Table altered.
SCOTT@zkm> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2
SCOTT@zkm> select column_name,hidden_column from user_tab_cols where table_name='T';
COLUMN_NAME HID
------------------------------ ---
OWNER NO
OBJECT_NAME NO
SUBOBJECT_NAME NO
OBJECT_ID NO
DATA_OBJECT_ID NO
OBJECT_TYPE NO
CREATED NO
LAST_DDL_TIME NO
TIMESTAMP NO
STATUS NO
TEMPORARY NO
COLUMN_NAME HID
------------------------------ ---
GENERATED NO
SECONDARY NO
13 rows selected.题外话
当时遇到几个奇葩问题，记录在一张纸上，其中(Y)代表当时项目过程中解决了的。
第一个就是这篇博文说的问题，那时候还以为是虚拟列（-_-||）。