Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    32

    Unanswered: how to add partition in existing table

    I am trying to add a new partition in existing table let's say tempabc
    and i try to use this command
    alter table tempabc add partition <partition name> values less than ('C%')

    but it cant work

    why i no need specify the column used in the table for partition

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It works for me:
    Code:
    SQL> create table tempabc (col1 varchar2(10), col2 int )
      2  partition by range (col1)
      3  ( partition p1 values less than ('A%')
      4  );
    
    Table created.
    
    SQL> alter table tempabc
      2  add partition p2 values less than ('C%');
    
    Table altered.
    Of course, the table has to have been created as a partitioned table in the first place for you to be able to add extra partitions to it.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    AS Tony alludes to, if you have a current NON-PARTITIONED table, you need to:

    1. create a PARTITIONED table
    2. load the NON-PARTITIONED data into the PARTITIONED table
    3. drop the NON-PARTITIONED table
    4. rename PARTITIONED table to whatever is appropriate

    or:
    1. export table and data
    2. drop table
    3. create new partitioned table the way you want it
    4. import table data into new partitioned table (this should also create all the necessary indexes and constraints from your original table)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Oct 2014
    Posts
    7
    Thank you for the*informative*posts.

  5. #5
    Join Date
    Nov 2014
    Posts
    1
    Quote Originally Posted by alfredfong View Post
    I am trying to add a new partition in existing table let's say tempabc
    and i try to use this command
    alter table tempabc add partition <partition name> values less than ('C%')

    but it cant work

    why i no need specify the column used in the table for partition
    Better late than never I guess...

    In Oracle, if a table is not partitioned, you can create a partition on a column such as:

    ALTER TABLE tempabc PARTITION BY <column_name> (
    PARTITION <partition name> VALUES LESS THAN ('C%')
    );

    And if you needed to add another partition later, then your command work work:

    alter table tempabc add partition <partition name> values less than ('D%');

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Better late than never I guess...
    Please continue to post solutions to threads that are more than 10 years old since there is unfulfilled need for more answers by you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by meckstss View Post
    In Oracle, if a table is not partitioned, you can create a partition on a column such as:

    ALTER TABLE tempabc PARTITION BY <column_name> (
    PARTITION <partition name> VALUES LESS THAN ('C%')
    );
    No, you can not do that.
    If the table is not partitioned, you can not add a partition.
    You have to create a new partitioned table and copy the data from the new to the old.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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