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,)
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS IMP-00017: following stat [message #581303] Thu, 04 April 2013 05:49 Go to next message
x-oracle
Messages: 345
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 Go to previous messageGo to next message
Littlefoot
Messages: 19687
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
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
joy_division
Messages: 4528
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.
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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>
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your tablespace reached is max size and so the error.

Regards
Michel
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
So create a new datafile or remove the max size from the current one.
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER DATABASE DATAFILE ...
Complete syntax in Database SQL Reference.

Regards
Michel
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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>
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a new file as Littlefoot told you in the very first answer.

Regards
Michel
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 Go to previous messageGo to next message
x-oracle
Messages: 345
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
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 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Total estimation using BLOCKS method - datapump
Next Topic: impdp not successfull
Goto Forum:
  


Current Time: Tue Oct 21 23:04:19 CDT 2014

Total time taken to generate the page: 0.10965 seconds