DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: moving data into correct partition

  1. #1
    Join Date
    Apr 2002
    Posts
    291

    moving data into correct partition

    Hi Gurus
    I'm running Oracle 9i on Linux 7.1. I got a 4 tables with 75,000 records. Long back these tables were created with pratitions 2800,2900 and maxvalue. Then after we got a project with data ranging 3200. This data went into maxvalue partition. Now i want to create some more partitions 3000,3100,3200 and move this data from maxvalue into appropriate new partitions. Is there any way to do this , with out dropping the tables and recreating them.

    Many thanks in advance...


    Thanks
    PNRDBA

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well you gotta search for the alter table split partition..clause with that you dont have to do drop the existing partition..

    I am posting Sanjays code once again why did this not work for you

    code:--------------------------------------------------------------------------------SQL>create table test (c1 number) partition by range (c1)
    2 ( partition p10 values less than (10),
    3 partition p20 values less than (20),
    4 partition p30 values less than (30),
    5* partition pmax values less than (maxvalue))
    SQL> /

    Table created.

    SQL> select high_value from dba_tab_partitions where table_name = 'TEST';

    HIGH_VALUE
    --------------------------------------------------------------------------------
    10
    20
    30
    MAXVALUE

    SQL> alter table test add partition p40 values less than (40);
    alter table test add partition p40 values less than (40)
    *
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition

    SQL> alter table test split partition pmax at (40) into (partition p40, partition pmax);

    Table altered.

    SQL> select high_value from dba_tab_partitions where table_name = 'TEST';

    HIGH_VALUE
    --------------------------------------------------------------------------------
    10
    20
    30
    MAXVALUE
    40--------------------------------------------------------------------------------


    HTH


    __________________
    Sanjay G.
    OCP 8i DBA



    regards
    Hrishy

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    here is my comprehensive test result..where I have not dropped any partition neither there is data loss

    SQL> drop table test
    2 /

    Table dropped.

    SQL> create table test (c1 number) partition by range (c1)
    2 ( partition p10 values less than (10),
    3 partition p20 values less than (20),
    4 partition p30 values less than (30),
    5 partition pmax values less than (maxvalue))
    6 /

    Table created.

    SQL> insert into test
    2 values(31)
    3 /

    1 row created.

    SQL> insert into test
    2 values(41)
    3 /

    1 row created.

    SQL> alter table test split partition pmax at (40) into (partition p40, partition pmax);

    Table altered.

    SQL> select * from test
    2 /

    C1
    ----------
    31
    41

    SQL> select * from test partition(pmax)
    2 /

    C1
    ----------
    41

    SQL>

    regards
    Hrishy

    P.S:do i understand your question right ?

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