Why can't I add sub-partition to range-list sub partitioned table?

[cheguvera]

Hi,

I am on Oracle 10.1 on Solaris.
I have a range-list subpartitioned table. I want to add a subpartition to all partitions.
But Oracle do not allow that to me, because I have a subpartition of DEFAULT value. I tried few options to do that, as shown below.

CREATE TABLE REP_1 (
PARTITION_KEY VARCHAR2(25 BYTE) NOT NULL,
LOAD_KEY VARCHAR2(25) NOT NULL,
TRANSACTION_SOURCE_KEY VARCHAR2(6) NOT NULL,
TRANSACTION_TYPE VARCHAR2(1) NOT NULL,
TRANSACTION_KEY VARCHAR2(50) NOT NULL,
TRANSACTION_DATE_TIME DATE NOT NULL,
TRANSACTION_OPEN_YN VARCHAR2(1) DEFAULT 'Y' NOT NULL)
PARTITION BY RANGE (PARTITION_KEY)
SUBPARTITION BY LIST (TRANSACTION_SOURCE_KEY)
SUBPARTITION TEMPLATE
(SUBPARTITION AA VALUES ('AALON', 'AANYK'),
SUBPARTITION TT VALUES ('TTHKG', 'TTIND', 'TTJNB', 'TTKOR'),
SUBPARTITION FF VALUES ('FFTYO', 'FFNYK', 'RANLON'),
SUBPARTITION YST VALUES ('YSTNYK'),
SUBPARTITION OTHER VALUES (DEFAULT)
)
(
PARTITION DAILY_001 VALUES LESS THAN ('DAILY_001-')
( SUBPARTITION DAILY_001_AA VALUES ('AALON', 'AANYK')
,SUBPARTITION DAILY_001_TT VALUES ('TTHKG', 'TTIND', 'TTJNB', 'TTKOR')
,SUBPARTITION DAILY_001_FF VALUES ('FFTYO', 'FFNYK', 'RANLON')
,SUBPARTITION DAILY_001_YST VALUES ('YSTNYK')
,SUBPARTITION DAILY_001_OTHER VALUES (DEFAULT)
),
PARTITION DAILY_002 VALUES LESS THAN ('DAILY_002-')
( SUBPARTITION DAILY_002_AA VALUES ('AALON', 'AANYK')
,SUBPARTITION DAILY_002_TT VALUES ('TTHKG', 'TTIND', 'TTJNB', 'TTKOR')
,SUBPARTITION DAILY_002_FF VALUES ('FFTYO', 'FFNYK', 'RANLON')
,SUBPARTITION DAILY_002_YST VALUES ('YSTNYK')
,SUBPARTITION DAILY_002_OTHER VALUES (DEFAULT)
),
PARTITION DAILY_003 VALUES LESS THAN ('DAILY_003-')
( SUBPARTITION DAILY_003_AA VALUES ('AALON', 'AANYK')
,SUBPARTITION DAILY_003_TT VALUES ('TTHKG', 'TTIND', 'TTJNB', 'TTKOR', 'TTLON')
,SUBPARTITION DAILY_003_FF VALUES ('FFTYO', 'FFNYK', 'RANLON')
,SUBPARTITION DAILY_003_YST VALUES ('YSTNYK')
,SUBPARTITION DAILY_003_OTHER VALUES (DEFAULT)
)
);

SQL> alter table rep_1 modify partition DAILY_001
2 add subpartition DAILY_001_X values ('XXXX') ;
alter table rep_1 modify partition DAILY_001
*
ERROR at line 1:
ORA-14621: cannot add subpartition when DEFAULT subpartition exists

***** Here Oracle Error & Messages manual suggests that split the default partition

SQL> alter table rep_1 split subpartition DAILY_001_OTHER values ( DEFAULT )
2 INTO
3 ( SUBPARTITION DAILY_001_X values ('XXXX') ,
4 SUBPARTITION DAILY_001_OTHER values (DEFAULT)
5 );
( SUBPARTITION DAILY_001_X values ('XXXX') ,
*
ERROR at line 3:
ORA-14160: this physical attribute may not be specified for a table
subpartition

***** A splitting like this is not allowed

SQL> alter table rep_1 split subpartition DAILY_001_OTHER values ( DEFAULT )
2 INTO
3 ( SUBPARTITION DAILY_001_X ,
4 SUBPARTITION DAILY_001_OTHER
5 );
alter table rep_1 split subpartition DAILY_001_OTHER values ( DEFAULT )
*
ERROR at line 1:
ORA-14320: DEFAULT cannot be specified for ADD/DROP VALUES or SPLIT

****** This is also not possible

The only option now for me is to DROP the DEFAULT subpartition and add 2 subpartitions. One for my new list value and another for default.
But in this approach, when I drop the existing DEFAULT subpartition, I loose the data in there.

Whats the way out of this puzzle ???

Thanks and Regards

 

[Advocate]

have you tried metalink ?


regards, david - no such thing as problems - only solutions.

 

[karluk]

Your syntax is wrong on the "alter table ... split subpartition" statements. The following changes should work:

alter table rep_1 split subpartition DAILY_001_OTHER values ( 'XXXX' )
 INTO
 ( SUBPARTITION DAILY_001_X,
 SUBPARTITION DAILY_001_OTHER
)

 

[Advocate]

oops ...

regards, david - no such thing as problems - only solutions.