Oracle Database Discussions

Hello all,

I'm trying to add a new partition to an existing partioned table but I need the new "table" partition to go to tablespace DATA_2011 and "index" partition to go to INDEX_2011. I tried the following but it errored out.

CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE DATA_2010,
PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE DATA_2010,
PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE DATA_2010);

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL
(PARTITION employees_part1 TABLESPACE INDEX_2010,
PARTITION employees_part2 TABLESPACE INDEX_2010,
PARTITION employees_part3 TABLESPACE INDEX_2010);


The following is what I'm trying execute but it fails...
alter table employees add PARTITION "EMPLOYEES_PART4" VALUES LESS THAN (41) tablespace DATA_2011 index tablespace INDEX_2011;

ERROR at line 1:
ORA-14020: this physical attribute may not be specified for a table partition



The following succeeds but in this case the index partition "EMPLOYEES_PART4" also gets assigned to DATA_2011 tablespace.

alter table employees add PARTITION "EMPLOYEES_PART4" VALUES LESS THAN (41) tablespace DATA_2011;


Any ideas..I'm currently looking into the documentation to find out if this would be possible?

Thanks for your time...