need help to split partition

I have partitioned my table from SEPT. 1998 - SEPT. 2002. I did not specify the maxvalue
when I created the partition.
Assuming I accidentally dropped my JAN2001(i.e. XFL_JAN2001) partition. Now I would like to split the partition for XFL_DEC2000 partition in order to add XFL_JAN2001 partition. XFL_DEC2000 contains data.
How do I perform this partition split. Below is what I have in mind. Can any one help?

alter table football
split partition XFL_DEC2000
into (
partition XFL_DEC2000 tablespace DEC2000_TABS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)

Quote:

Originally posted by randy
alter table football
split partition XFL_DEC2000
into (
partition XFL_DEC2000 tablespace DEC2000_TABS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)

Assuming you have partitioned on a date:

Code:

alter table football split partition XFL_DEC2000 at (to_date('01/01/2001','mm/dd/yyyy')) into ( partition XFL_DEC2000 tablespace DEC2000_TBS, partition XFL_JAN2001 tablespace JAN2001_TBS) parallel (degree 4)

I'm also not sure about the parallel clause, you'd have to try it....

It did not work.

alter table football
split partition XFL_DEC2000
at (to_date('01/01/2001','mm/dd/yyyy')) into (
partition XFL_DEC2000 tablespace DEC2000_TBS,
partition XFL_JAN2001 tablespace JAN2001_TBS)
parallel (degree 4)

XFL_DEC2000
*
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound

Right, right, right. I wasn't even looking at what you were trying to do. You must split the higher partition since the values are "LESS THAN".

Code:

alter table football split partition XFL_FEB2001 at (to_date('01/01/2001','mm/dd/yyyy')) into ( partition XFL_JAN2001 tablespace JAN2001_TBS, partition XFL_FEB2001 tablespace FEB2001_TBS)

It did not work again.

SQL> alter football
2 split partition XFL_FEB2001
3 at (to_date('01/01/2001','mm/dd/yyyy')) into (
4 partition XFL_JAN2001 tablespace JAN2001_TBS,
5 partition XFL_FEB2001 tablespace FEB2001_TBS);
split partition XFL_FEB2001
*
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound

OK, I'll need to see your partitioning and table structure.

desc football

set long 2000
col partition_name for a30
col high_value for a40
select partition_name, high_value
from dba_tab_partitions
where table_name = 'FOOTBALL'
order by partition_name

SQL> desc football
Name Null? Type
----------------------------------------- -------- ----------------------------
PLAYER_LAST_NAME NOT NULL VARCHAR2(40)
PLAYER_FIRST_NAME NOT NULL VARCHAR2(40)
AGENT_NAME VARCHAR2(50)
SIGN_DATE NOT NULL DATE
TEAM_NAME NOT NULL VARCHAR2(40)

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_APR2001 TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_AUG2000 TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_AUG2001 TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------

XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_DEC2001 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_JUN2000 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
N')

XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_JUL2000 TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_JUL2001 TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-M

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_JUN2001 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
XFL_MAY2001 TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_NOV2000 TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_NOV2001 TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------

XFL_OCT2000 TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_OCT2001 TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

XFL_SEP2000 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------
N')

XFL_SEP2001 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')

Oh, yes, you have to split at 02/01/2001 since the values are less than...

Code:

[email protected]> @test1 Table dropped. Elapsed: 00:00:00.09 Table created. Elapsed: 00:00:00.07 [email protected]> l 1 create table football ( 2 f1 varchar2(10), 3 dt date, 4 f2 number(10)) 5 partition by range (dt) 6 ( 7 partition xfl_dec2000 values less than (to_date('01/01/2001','mm/dd/yyyy')), 8 partition xfl_feb2001 values less than (to_Date('03/01/2001','mm/dd/yyyy')), 9* partition xfl_mar2001 values less than (to_Date('04/01/2001','mm/dd/yyyy'))) [email protected]> select partition_name, high_value 2 from user_tab_partitions 3 / PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ Elapsed: 00:00:00.02 [email protected]> alter table football 2 split partition xfl_feb2001 at (to_date('02/01/2001','mm/dd/yyyy')) 3 into (partition xfl_jan2001, partition xfl_feb2001); Table altered. Elapsed: 00:00:00.07 [email protected]> select partition_name, high_value 2 from user_tab_partitions 3 / PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ XFL_DEC2000 TO_DATE(' 2001-01-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') XFL_FEB2001 TO_DATE(' 2001-03-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') XFL_MAR2001 TO_DATE(' 2001-04-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ XFL_JAN2001 TO_DATE(' 2001-02-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS', 'N LS_CALENDAR=GREGORIAN') Elapsed: 00:00:00.02 [email protected]> spool off

Good looking out. That was a great help