OraFAQ Forum: Server Utilities » ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat

Home » RDBMS Server » Server Utilities » ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat (oracle 10g,windows 7 64 bit,)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581303]

Thu, 04 April 2013 05:49

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

hello while importing data i got this error in my log file.and i cannot import my data successfuly

in my log file error i found like this

ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

i can import my data using imp utility using this syntax

C:\Users\Administrator>imp tiger/****@tcs file=E:\DUMP\ts.
dmp log=E:\DUMP\ts.log fromuser=tiger121 touser=tiger statistics=none

this my user tiger default tablespace its uses and its a auto extend on and localy managed tablespace,and i have enough space on my drive also..can you hint me what can i do to resolve this error

[Updated on: Thu, 04 April 2013 05:52]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581304 is a reply to message #581303]

Thu, 04 April 2013 05:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle says
Quote:

ORA-01658: unable to create INITIAL extent for segment in tablespace string

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581305 is a reply to message #581304]

Thu, 04 April 2013 06:14

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

but my tablespaces segment space management auto so its not allocate spaces automatic little foot

my tablespaces details are under

SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_
tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
UNDOTBS1                       LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO
EXAMPLE                        LOCAL      AUTO

6 rows selected.

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581313 is a reply to message #581305]

Thu, 04 April 2013 07:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

my tablespaces segment space management auto

This is irrelevant to the problem.
There is no more room in the tablespace, you have to enlarge it.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581328 is a reply to message #581305]

Thu, 04 April 2013 08:09

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

x-oracle wrote on Thu, 04 April 2013 07:14

from dba_tablespaces;

DBA_DATA_FILES gives the relevant information.

Quote:

tablespace its uses and its a auto extend

Show us.

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581367 is a reply to message #581328]

Fri, 05 April 2013 01:50

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

Thanks for your reply

joy my datafile into

SQL> select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_
files where TABLESPACE_NAME='USERS';

FILE_NAME
--------------------------------------------------------------------------------

TABLESPACE_NAME                AUT INCREMENT_BY
------------------------------ --- ------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\TCS\USERS01.DBF
USERS                          YES          160

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME='TIGER';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TIGER                            USERS

SQL>

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581369 is a reply to message #581367]

Fri, 05 April 2013 02:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK and then?
Show us current size and max size.

Note: whatever you think Oracle is right and you have to add space.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581373 is a reply to message #581369]

Fri, 05 April 2013 02:18

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,MAXBYTES from dba_data_files where T
ABLESPACE_NAME='USERS';
♀FILE_NAME
--------------------------------------------------------------------------------
--------------------
TABLESPACE_NAME                     BYTES   MAXBYTES
------------------------------ ---------- ----------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\TCS\USERS01.DBF
USERS                          3.4360E+10 3.4360E+10

Michel i just wann ask you this users tablespaces its created by oracle when i create database so if i want to insert lots of data into this users tablespace and spaces are avaible into my computer hardisk so tablespace not allocate spaces automatic.becuase if we select autoexted enable so spaces are allowcated automatic into datafiles

Michel if i am wrong i am sorry for that.

[Updated on: Fri, 05 April 2013 02:26]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581374 is a reply to message #581373]

Fri, 05 April 2013 02:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So your tablespace reached is max size and so the error.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581377 is a reply to message #581374]

Fri, 05 April 2013 02:29

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

yeah i check size in properties of this USERS01.DBF its 31.9 GB but already 200 GB spaces avaible into my F drive where this datafile are there

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581381 is a reply to message #581377]

Fri, 05 April 2013 02:43

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

So create a new datafile or remove the max size from the current one.

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581382 is a reply to message #581381]

Fri, 05 April 2013 02:51

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

yeah thanks for you reply cookiesmonster can you guide me little bit how to remove this max size from this my users tablespaces

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581385 is a reply to message #581382]

Fri, 05 April 2013 03:10

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ALTER DATABASE DATAFILE ...
Complete syntax in Database SQL Reference.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581394 is a reply to message #581385]

Fri, 05 April 2013 06:18

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

michel i did this

SQL> ALTER DATABASE DATAFILE 'F:\oracle\product\10.2.0\oradata\tcs\USERS01.DBF'
  2  AUTOEXTEND on MAXSIZE UNLIMITED ;

Database altered.

but i am have the same problm still same error while importing data

IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

But when i check max size of this file its same as before but i already set unlimited for this users01.dbf file

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,MAXBYTES from dba_data_files where
2 TABLESPACE_NAME='USERS';

FILE_NAME
--------------------------------------------------------------------------------

TABLESPACE_NAME BYTES MAXBYTES
------------------------------ ---------- ----------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\TCS\USERS01.DBF
USERS 3.4360E+10 3.4360E+10

SQL>

[Updated on: Fri, 05 April 2013 06:29]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581399 is a reply to message #581394]

Fri, 05 April 2013 07:42

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Execute "set numwidth 20" and post the result of your command.
And add the result of:

select block_size from dba_tablespaces where TABLESPACE_NAME='USERS';

Also, FORMAT your output with code tags.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581415 is a reply to message #581399]

Sat, 06 April 2013 01:01

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

Thanks for you reply michel

my output its below

SQL> set numwidth 20
SQL> select block_size from dba_tablespaces where TABLESPACE_NAME='USERS';

          BLOCK_SIZE
--------------------
                8192

SQL>

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581417 is a reply to message #581415]

Sat, 06 April 2013 01:26

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I also wanted the output of:

select FILE_NAME,TABLESPACE_NAME,BYTES,MAXBYTES from dba_data_files where TABLESPACE_NAME='USERS';

Regards
Michel

[Updated on: Sat, 06 April 2013 01:26]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581418 is a reply to message #581417]

Sat, 06 April 2013 01:49

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Anyway, your file reached 32G which is the maximum size for a (small) file with 8KB block.
That is UNLIMITED=32G in this case.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581419 is a reply to message #581418]

Sat, 06 April 2013 02:36

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

michel my out put its below

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,MAXBYTES from dba_data_files where T
ABLESPACE_NAME='USERS';

FILE_NAME
--------------------------------------------------------------------------------

TABLESPACE_NAME                               BYTES             MAXBYTES
------------------------------ -------------------- --------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\TCS\USERS01.DBF
USERS                                   34359721984          34359721984

yeah i told you i check size of this USERS01.DBF file size its 32gb.so if my database base size are large so what can i do.may be its goes into Terabytes.and michel another my question its if i have rac database and asm instances so its also b same there 32gb datafile size. and what can i do in rac database if my this tablespace size are full.

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581420 is a reply to message #581419]

Sat, 06 April 2013 02:59

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Add a new file as Littlefoot told you in the very first answer.

Regards
Michel

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581422 is a reply to message #581420]

Sat, 06 April 2013 04:24

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

so michel only 32 gb data i can insert into one datafile.if my data are so much so what can i do..and can you just help me how to add datafile into rac database..i have no more idea on rac also so

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581424 is a reply to message #581422]

Sat, 06 April 2013 05:40

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

so michel only 32 gb data i can insert into one datafile.

Into a small file with 8K block size, yes.

Quote:

f my data are so much so what can i do..

Once again add a file.

Quote:

nd can you just help me how to add datafile into rac database..i have no more idea on rac also so

No difference with not-RAC: ALTER TABLESPACE.

I advice you to read:
Database Concepts
Database 2 Day DBA
Administrator's Guide

Regards
Michel

Report message to a moderator

Previous Topic:	Total estimation using BLOCKS method - datapump
Next Topic:	impdp not successfull

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:03:16 CST 2025