need help to split partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: need help to split partition

  1. #1
    Join Date
    Mar 2001
    Posts
    82

    Arrow

    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)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2001
    Posts
    82
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Posts
    82
    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

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Mar 2001
    Posts
    82

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




  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Mar 2001
    Posts
    82
    Good looking out. That was a great help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width