131 积分	1 好友	13 主题

发消息

ORA-42016

1^#

发表于 2012-3-18 17:23:48 | 查看: 6223| 回复: 6

在进行在线重定义的时候总是出现这个错误
SQL>  EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW',  DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW',  DBMS_REDEFINITION.CONS_USE_PK); END;

*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1

表结构如下：
SQL> desc t1;
Name                                     Null? Type
----------------------------------------- -------- ----------------------------
ID                                     NOT NULL VARCHAR2(60)
A                                                 VARCHAR2(60)
B                                                 VARCHAR2(60)
CREATE_TIME                                     TIMESTAMP(6)
D                                                 NUMBER(2)
E                                                 VARCHAR2(400)
OUTER_ID                                        VARCHAR2(100)

SQL> desc t1_new;
Name                                     Null? Type
----------------------------------------- -------- ----------------------------
ID                                     NOT NULL VARCHAR2(60)
A                                                 VARCHAR2(60)
B                                                 VARCHAR2(60)
CREATE_TIME                                     TIMESTAMP(6)
D                                                 NUMBER(2)
E                                                 VARCHAR2(400)
OUTER_ID                                        VARCHAR2(100)
其中t1_new进行了分区
，请教一下这是啥原因啊

分享0

收藏0 回复只看该作者道具举报

aaaaaaaa2000

2^#

发表于 2012-3-18 17:52:44

SQL> select count(*) from t1_new;

COUNT(*)
----------
0

SQL>
新表没有数据

回复只看该作者道具举报

aaaaaaaa2000

3^#

发表于 2012-3-18 17:54:22

SQL> select DBMS_METADATA.GET_DDL('TABLE','T1_NEW') from dual;DBMS_METADATA.GET_DDL('TABLE','T1_NEW') CREATE TABLE "CLM"."T1_NEW" ( "ID" VARCHAR2(60) NOT NULL ENABLE, "A" VARCHAR2(60), "B" VARCHAR2(60), "CREATE_TIME" TIMESTAMP (6), "D" NUMBER(2,0), "E" VARCHAR2(400), "OUTER_ID" VARCHAR2(100) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(DBMS_METADATA.GET_DDL('TABLE','T1_NEW') BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY RANGE ("ID") (PARTITION "P_T1_NEW" VALUES LESS THAN ('201204') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" NOCOMPRESS , PARTITION "P_T2_NEW" VALUES LESS THAN ('201205') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255DBMS_METADATA.GET_DDL('TABLE','T1_NEW') STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

这是新表的创建语句

回复只看该作者道具举报

aaaaaaaa2000

4^#

发表于 2012-3-18 18:06:37

SQL> select DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1_NEW') from dual;

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1_NEW')
--------------------------------------------------------------------------------

  CREATE TABLE "CLM"."T1_NEW"
( "ID" VARCHAR2(60) NOT NULL ENABLE,
      "A" VARCHAR2(60),
      "B" VARCHAR2(60),
      "CREATE_TIME" TIMESTAMP (6),
      "D" NUMBER(2,0),
      "E" VARCHAR2(400),
      "OUTER_ID" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1_NEW')
--------------------------------------------------------------------------------
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
(PARTITION "P_T1_NEW"  VALUES LESS THAN ('201204')
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P_T2_NEW"  VALUES LESS THAN ('201205')
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1_NEW')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)

SQL> select DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1') from dual;

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1')
--------------------------------------------------------------------------------

  CREATE TABLE "CLM"."T1"
( "ID" VARCHAR2(60) NOT NULL ENABLE,
      "A" VARCHAR2(60),
      "B" VARCHAR2(60),
      "CREATE_TIME" TIMESTAMP (6),
      "D" NUMBER(2,0),
      "E" VARCHAR2(400),
      "OUTER_ID" VARCHAR2(100),
      CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

DBMS_METADATA.GET_DDL(SCHEMA=>'CLM',OBJECT_TYPE=>'TABLE',NAME=>'T1')
--------------------------------------------------------------------------------

SQL>

新表旧表的创建语句

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW');---执行这个没有问题
没有报错

回复只看该作者道具举报

action825

5^#

发表于 2012-3-18 23:40:46

你的参数用错了,

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW',  DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T1', 'T1_NEW',  DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(UNAME=>USER,ORIG_TABLE=>'T1',INT_TABLE=>'T1_NEW',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

PROCEDURE START_REDEF_TABLE
Argument Name                Type                   In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME                         VARCHAR2             IN
ORIG_TABLE                   VARCHAR2             IN
INT_TABLE                   VARCHAR2             IN
COL_MAPPING                   VARCHAR2             IN    DEFAULT
OPTIONS_FLAG                BINARY_INTEGER       IN    DEFAULT
ORDERBY_COLS                VARCHAR2             IN    DEFAULT
PART_NAME                   VARCHAR2             IN    DEFAULT

默认情况下第4个参数是 COL_MAPPING, 所以你原来的写法会报错。
需要指定下参数就可以了。

回复只看该作者道具举报

aaaaaaaa2000

6^#

发表于 2012-3-19 15:13:52

明白了明白了，多谢多谢解答啊！！！！

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-3-19 19:44:36

ODM FINDING:

[oracle@vrh8 ~]$ oerr ora 42016
42016, 0000, "shape of interim table does not match specified column mapping"
// *Cause:  The number of columns, or the type or the length semantics of a
//       column, in the interim table did not match the specified
//       column mapping.
// *Action: Ensure that the interim table matches the column mapping by
//       either modifying the column mapping string or altering the
//       interim table's column definition(s).

DBMS_REDEFINITION.START_REDEF_TABLE 的默认语法：

  -- NAME:    start_redef_table - start the online re-organization
  -- INPUTS: uname       - schema name
  --          orig_table - name of table to be re-organized
  --          int_table - name of interim table
  --          col_mapping  - select list col mapping
  --          options_flag - flag indicating user options to use
  --          orderby_cols - comma separated list of order by columns
  --                         followed by the optional ascending/descending
  --                         keyword
  --          part_name - name of the partition to be redefined
  PROCEDURE start_redef_table(uname       IN VARCHAR2,
                           orig_table IN VARCHAR2,
                           int_table IN VARCHAR2,
                           col_mapping  IN VARCHAR2 := NULL,
                           options_flag IN BINARY_INTEGER := 1,
                           orderby_cols IN VARCHAR2 := NULL,
                           part_name IN VARCHAR2 := NULL);

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册