-
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.
-
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
-
-
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?
-
you mean move not modify
alter table xxx move partition yyy tablespace zzzzz
-
thanks a lot.
I am learning a lot since the dba went on vacation.
-
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
-
If it is a local index, this partition was probably already built. Check dba_ind_partitions to see if it already exists.
Jeff Hunter
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|