Partitioned Table

[lesstjm]

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.

[marist89]

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.

[lesstjm]

I will give it a try.

[lesstjm]

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?

[pando]

you mean move not modify

alter table xxx move partition yyy tablespace zzzzz

[lesstjm]

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

[lesstjm]

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

[marist89]

If it is a local index, this partition was probably already built. Check dba_ind_partitions to see if it already exists.

[pando]

you dont have to add an index partition since local indexes are added automatically when you add/split a partition