I have an issue where I have a tablespace called BIO_4M_DAT that is sized at 3 GB (out of which 13 MB is used - > 0.43% full), hence it is almost empty. Also, since there is no more space on the disk drives, I cannot increase the size of this tablespace from 3GB.
My database block size is 8K
and this tablepsace BIO_4M_DAT has
EXTENT_MANAGEMENT set to LOCAL
UNIFORM ALLOCATION 4KB
I am just trying to create a table with the following statement :
create table BIO_CHEM
col1 integer not null,
col2 number(28) not null,
col3 varchar(12) not null,
col4 integer not null,
) partition by hash(col2) partitions 1024
tablespace BIO_4M_DAT pctfree 0
storage (initial 4M next 4M pctincrease 0)
But create fails saying
ORA-01658: unable to create INITIAL extent for segment in tablespace BIO_4M_DAT
What could be taking up so much space that is draining the 3 GB ? Is there a work-around I can use to get this create table run successfully, perhaps by changing any create table parameters ?
Any help, suggestions on this is appreciated.
I don't know partitioning exactly, but your statement (and oracle doc about this) tells me, that you will create 1024 partitions each with initial extent of 4M. You will need 4GB in the tablespace. So degrease the amount or the size of your partitions. Even if you take a initial size of 3M and the tablespace is fully used by this table a partition can't contain more data than 3M
Thanks much for your response. I tried creating the table even with an initial extent size of 1M and next extent size of 1M but still it doesn't see to fit in 3GB. Is there anything else I can try out ?
Your highwater mark for the tablespace is probably in the upper blocks ...
Find the objects in the tablespace in the upper blocks and move them ..
indexes ... rebuild ... tables - alter table move / export, import ...
The attached script willl map the tablespace blocks ... You can
coalesce the tablespace to make the free space contiguous ... The
free space at the end of the tablespace can be removed with
alter tablespace xxx datafile 'c:\....' resize xx;
You might just try the alter tablespace xxxx coalesce first, but if you need
to scale down the tablespace, look at the free space and block mapping.