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)代表当时项目过程中解决了的。

第一个就是这篇博文说的问题,那时候还以为是虚拟列(-_-||)。

 

posted @ 2020-07-01 15:23  PiscesCanon  阅读(271)  评论(0编辑  收藏  举报