Partitioned Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Partitioned Table

  1. #1
    Join Date
    Jan 2001
    Posts
    515
    How do you add a partition to and already partitioned table? I want to add a Janrurary partition to an already partitioned table and also add a new partition to the index.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Depends, do you have a partition defined with MAXVALUE? If so, you have to split the maxvalue partition:
    ALTER TABLE xyz SPLIT PARTITION part_max AT (to_date('02/01/2002','mm/dd/yyyy')) INTO ( PARTITION part_jan, PARTITION part_max);

    Otherwise, you can just add a partition:
    ALTER TABLE xyz ADD PARTITION part_jan VALUES LESS THAN (to_date('02/01/2002','mm/dd/yyyy'))

    If you are using local indexes, they will be maintained with the addition of a partition. http://technet.oracle.com/docs/produ...2a.htm#2054899
    for reference.
    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
    Jan 2001
    Posts
    515

    thanks

    I will give it a try.

  4. #4
    Join Date
    Jan 2001
    Posts
    515

    One more question

    I used ALTER TABLE xyz ADD PARTITION part_jan VALUES LESS THAN (to_date('02/01/2002','mm/dd/yyyy')) and it worked fine. But I want to assign this partition to a specific tablespace. I tried this but it didn't work.

    alter table xyz modify partition part_jan
    tablespace abc;

    *
    ERROR at line 3:
    ORA-14049: invalid ALTER TABLE MODIFY PARTITION option


    How would I assign it to a tablespace?


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you mean move not modify

    alter table xxx move partition yyy tablespace zzzzz

  6. #6
    Join Date
    Jan 2001
    Posts
    515

    thanks a lot.

    I am learning a lot since the dba went on vacation.

  7. #7
    Join Date
    Jan 2001
    Posts
    515

    alter index

    Now I am trying to alter the index and I am getting another error.

    alter index value_date_idx
    add partition x_icm_jan2002 values less than
    ( to_date('2002-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
    tablespace index_hsbc_icm_jan2002;

    add partition x_icm_jan2002 values less than
    *
    ERROR at line 2:
    ORA-02243: invalid ALTER INDEX or ALTER SNAPSHOT option


  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If it is a local index, this partition was probably already built. Check dba_ind_partitions to see if it already exists.
    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
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont have to add an index partition since local indexes are added automatically when you add/split a partition

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