Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Add partition fails. Please help!
Hi!
I tried to drop and add a partition and I get Oracle error :
Unable to create initial extend on table space ,..
This is what I am trying to do :
create table test
(
col1 number(3) not null,
col2 number(3) not null
)
partition by range (col2)
local
(
partition p1 values less than (10)
tablespace ts1,
partition p2 values less than (20)
tablespace ts2
);
create index idx1 on test(col2)
local
(
partition i1 tablespace idx1,
partition i2 tablespace idx2
);
Now, if I drop partition p2, with the command,
alter table test drop partition p2;
it works and the partition is deleted.
If I try to add a new partition in the same tablespace ts1, with the command,
alter table test add partition p3 values less than (30) tablespace ts1;
I get the error, unable to create initial extent xxM on tablespace ts1.
Tablespace ts1 does not contain any other data and was dedicated to partition p2 of the test table.
Even 'alter tablespace ts1 coalesce' doesn't free up the tablespace.
Dropping and recreating the table works but that is not a solution!
Somebody please help!
Thanks a million,