DBMS_METADATA.GET_DDL查出不存在的列SYS_C00014_20070116:47:09$
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.0 - 64bit Production on Wednesday, 01 July, 2020 17:32:34 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 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: 7 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."T" 4.695 MB 50330 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 17:32:36
不需要实际导入，加入参数sqlfile即可验证，
[oracle@oracle ~]$ impdp \'/ as sysdba\' directory=dir dumpfile=t.dmp logfile=i_t.log sqlfile=t.sql Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 01 July, 2020 17:33:50 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 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 17:33:52 [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(255), "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) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 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)代表当时项目过程中解决了的。
第一个就是这篇博文说的问题，那时候还以为是虚拟列（-_-||）。
