-
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2
When i create a table oracle gives below mention error
Though there is enough space available in the data file of USERS2 tablespace.
I have oracle8i workgroup on windows-NT
datafile status of USERS2 tablespace from enterprise manager
size (M) used(M)
data1.dbf 650 512.932
data2.dbf 500 272.018 <-enough space here
data3.dbf 500 431.406 <-enough space here
create table abc
(acno char(1) )
tablespace users2 storage (initial 1m)
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2
What could be the solution of this problem ?????
Thanks in advance.
from Anuj Pathak
Hero Financial Services Ltd.
-
It seems your tablespace is realy badly fragmented! It is not sufficient to have enough free space in the datafile, this space has to be *contiguous*. Run the following query to see sizes of your free chunks in that tablespace:
select file_id, bytes as free_chunk from dba_free_space
where tablespace_name = 'USERS2'
order by bytes desc;
Try also coalescing adjascent free chunks in bigger chunks by
ALTER TABLESPACE ZSERS2 COALESCE;
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
hi jmodic
I tried yr suggestions performed steps and output i am giveing u below.
it creates the temp table with single field and initial extend 20k
but actual table like fdscheme with initial 20k does not create
Is there any other way to solve tablespace fregmentation
from Anuj Pathak
Hero Financial Services Ltd.
SQL> alter tablespace users2 coalesce ;
Tablespace altered.
SQL>
SQL> select file_id, bytes as free_chunk from dba_free_space
2 where tablespace_name = 'USERS2'
3 order by bytes desc;
FILE_ID FREE_CHUNK
--------- ----------
12 104857600
17 104855552
8 90478592
12 68157440
15 68157440
8 53114880
12 52424704
12 13617152
15 2713600
15 1054720
8 102400
8 30720
12 rows selected.
SQL> create table abc (acno char(7)) tablespace users2 storage (initial 20k);
Table created.
SQL> CREATE TABLE FDSCHEME (
2 SCHEMECD CHAR(3) CONSTRAINT PK_SCH PRIMARY KEY,
3 SCHEMENAME VARCHAR2(30) NOT NULL,
4 INTRATE NUMBER(5,2),
5 COMPOUND NUMBER(1),
6 IP_MON NUMBER(2),
7 PERIOD_S NUMBER(2) NOT NULL,
8 PERIOD_E NUMBER(2) NOT NULL,
9 USERID NUMBER(3),
10 CRE_DT DATE
11 )
12 tablespace users2 storage (initial 20k)
13 /
CREATE TABLE FDSCHEME (
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2
-
THis realy is a strange one. Sorry, I have no idea what could be wrong here. Anyone else?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
If i do the following will it work
1. export of the tablespace
2. drop all the tables from the tablespace
3. import the exported dmp file again
from Anuj
Hero Financial Services Ltd.
-
Have you tried by decreasing the initial extent value? But make sure that it is quite enough to hold the data. I faced the same problem , then I calculated the exact initial extent size required and it works.
Thanks,
Rajesh
-
Is it OK to change the size of extents in the existing tablespace ?
I have a export dmp which I want to import to my tablespace.
But I want to use my own extent sizes and not the one from the dmp. Is there any way to do this ??
Sonali
-
I forgot to mention I want to reduce the size of extents.
Sonali
-
Hi
you can import with rows=n
then alter the storage parameters, if you have many objects then you probably better build a script with dynamic sql to do the alter table/index etc etc.
after just import again with ignore=y
-
a long shot but you may want to run DBV (DB Verify) to see if there is a corrupt block on the disk.
I think DB should be shutdown in order to run DBV
- Rajeev
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
|