need help to split partition

**randy** · 02-07-2002 04:52 PM

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)

**marist89** · 02-07-2002 05:11 PM

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....

**randy** · 02-07-2002 05:34 PM

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

**marist89** · 02-07-2002 11:54 PM

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)

**randy** · 02-08-2002 10:27 AM

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

**marist89** · 02-08-2002 11:07 AM

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

**randy** · 02-08-2002 11:40 AM

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')

**marist89** · 02-08-2002 12:00 PM

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

Code:

ops$jeffh@dev901.us> @test1



Table dropped.



Elapsed: 00:00:00.09



Table created.



Elapsed: 00:00:00.07

ops$jeffh@dev901.us> 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')))

ops$jeffh@dev901.us> 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

ops$jeffh@dev901.us> 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

ops$jeffh@dev901.us> 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

ops$jeffh@dev901.us> spool off

**randy** · 02-08-2002 12:15 PM

Good looking out. That was a great help

Thread: need help to split partition

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions