Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error for exchanging rang-list partitioned table with list partition table

SumuryJan 3 2012 — edited Jan 3 2012
when I exchange data range-list table to list table, throwing a error:
ORA-14631: the partition bounds do not match the subpartition bounds of the partition
(The command is : ALTER TABLE TEST_COL_P10 EXCHANGE PARTITION PD_TEST_COL_P10_200904 WITH TABLE PD_TEST_COL_P10_200904)

the range-list table ddl is like below:
CREATE TABLE "CS_ADMIN"."TEST_COL_P10"
( "COLUMN_001" VARCHAR2(3),
"COLUMN_002" VARCHAR2(10),
"COLUMN_003" VARCHAR2(67),
"COLUMN_004" VARCHAR2(40),
"COLUMN_005" VARCHAR2(240),
"COLUMN_006" VARCHAR2(30),
"COLUMN_007" VARCHAR2(3),
"COLUMN_008" NUMBER,
"COLUMN_009" VARCHAR2(25),
"COLUMN_010" NUMBER,
"COLUMN_011" DATE,
"COLUMN_012" DATE,
"CS100_COMPANY_CODE" VARCHAR2(4),
"CS101_BASE_CODE" VARCHAR2(10),
"CS102_UPTAKE_DATE" DATE,
"CS103_FILE_ID" VARCHAR2(10),
"CS104_UPDATE_KEY" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE04"
PARTITION BY RANGE ("COLUMN_012")
SUBPARTITION BY LIST ("CS101_BASE_CODE")
(PARTITION "PD_TEST_COL_P10_DEFAULT" VALUES LESS THAN (TO_DATE(' 1900-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE04"
( SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FX_A" VALUES ('FX_A')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FX_F" VALUES ('FX_F')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_SFX" VALUES ('SFX')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXIM" VALUES ('FXIM')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_NIFX" VALUES ('NIFX')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXMFG" VALUES ('FXMFG')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_TK" VALUES ('TK')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXEMS" VALUES ('FXEMS')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXK" VALUES ('FXK')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXSL" VALUES ('FXSL')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_FXSZ" VALUES ('FXSZ')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_CS" VALUES ('CS')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_DEFAULT_ZZZZ" VALUES ('ZZZZ')
TABLESPACE "APPS_TS_TABLE04") ,
PARTITION "PD_TEST_COL_P10_200904" VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE04"
( SUBPARTITION "PD_TEST_COL_P10_200904_ZZZZ" VALUES ('ZZZZ')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_CS" VALUES ('CS')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXSZ" VALUES ('FXSZ')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXSL" VALUES ('FXSL')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXK" VALUES ('FXK')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXEMS" VALUES ('FXEMS')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_TK" VALUES ('TK')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXMFG" VALUES ('FXMFG')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_NIFX" VALUES ('NIFX')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FXIM" VALUES ('FXIM')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_SFX" VALUES ('SFX')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FX_F" VALUES ('FX_F')
TABLESPACE "APPS_TS_TABLE04",
SUBPARTITION "PD_TEST_COL_P10_200904_FX_A" VALUES ('FX_A')
TABLESPACE "APPS_TS_TABLE04") )

the list table ddl is like below:

CREATE TABLE "CS_ADMIN"."PD_TEST_COL_P10_200904"
( "COLUMN_001" VARCHAR2(3),
"COLUMN_002" VARCHAR2(10),
"COLUMN_003" VARCHAR2(67),
"COLUMN_004" VARCHAR2(40),
"COLUMN_005" VARCHAR2(240),
"COLUMN_006" VARCHAR2(30),
"COLUMN_007" VARCHAR2(3),
"COLUMN_008" NUMBER,
"COLUMN_009" VARCHAR2(25),
"COLUMN_010" NUMBER,
"COLUMN_011" DATE,
"COLUMN_012" DATE,
"CS100_COMPANY_CODE" VARCHAR2(4),
"CS101_BASE_CODE" VARCHAR2(10),
"CS102_UPTAKE_DATE" DATE,
"CS103_FILE_ID" VARCHAR2(10),
"CS104_UPDATE_KEY" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01"
PARTITION BY LIST ("CS101_BASE_CODE")
(PARTITION "PD_TEST_COL_P10_DEFAULT_FX_A" VALUES ('FX_A')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FX_F" VALUES ('FX_F')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_SFX" VALUES ('SFX')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXIM" VALUES ('FXIM')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_NIFX" VALUES ('NIFX')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXMFG" VALUES ('FXMFG')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_TK" VALUES ('TK')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXEMS" VALUES ('FXEMS')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXK" VALUES ('FXK')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXSL" VALUES ('FXSL')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_FXSZ" VALUES ('FXSZ')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_CS" VALUES ('CS')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS ,
PARTITION "PD_TEST_COL_P10_DEFAULT_ZZZZ" VALUES ('ZZZZ')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TABLE01" NOCOMPRESS )

I wonder why they have same struct, why can't exchange?

Who can give some suggest.

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 31 2012
Added on Jan 3 2012
#general-database-discussions
2 comments
894 views