Home » RDBMS Server » Server Administration » ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS (ORACLE 11g windowsxp)
ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519415] Thu, 11 August 2011 23:44 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full


SQL> alter table LINUX_CATA.TST_FOLDERS move   tablespace LINUX_CATA;

Table altered.

SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move   tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move   tablespace LINUX_CATA
                       *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA


SQL> alter table LINUX_CATA.TST_TEST_TYPES move   tablespace LINUX_CATA;
alter table LINUX_CATA.TST_TEST_TYPES move   tablespace LINUX_CATA
                       *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA


Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519464 is a reply to message #519415] Fri, 12 August 2011 06:46 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

oerr ora 01658
01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"
// *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


Babu

[Updated on: Fri, 12 August 2011 06:47]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519467 is a reply to message #519415] Fri, 12 August 2011 07:16 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
I can't simulate your problem:
orcl> create tablespace small datafile 'small1.dbf' size 1m extent management local uniform size 64k;

Tablespace created.

orcl> create table t1(c1 char(1000)) tablespace small;

Table created.

orcl> begin
  2  for i in 1..1000 loop
  3  insert into t1 values('a');
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table JON.T1 by 8 in tablespace SMALL
ORA-06512: at line 3


orcl> select * from dba_free_space where tablespace_name='SMALL';

no rows selected

orcl> alter table t1 move tablespace users;

Table altered.

orcl> select * from dba_free_space where tablespace_name='SMALL';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SMALL                                   8          8     983040        120            8

orcl>
Can you perhaps simplify your example and construct another test?
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519639 is a reply to message #519415] Tue, 16 August 2011 00:36 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hey your error ORA-01658 concerns the need to make the tablespace bigger, now this can be accomplished either by extending your file, or adding another one.

There are two solution for your problem:
Solution 1: You can Resize Tablespace by using "ALTER DATABASE Command"

alter database datafile '' resize M;

Solution 2: You can add a new Datafile to your tablespace

alter tablespace tablespace_name add datafile '' size M autoextend M maxsize M|off>;


As you says that if you add the datafile the problem is resolved, you can try checking your tablespace using "DBA_TABLESPACES" view for you source database. Read statistics(values) of NEXT_EXTENT and INITIAL_EXTENT from there if there is no value for the NEXT_EXTENT then you have to increase the size of your datafile.

Regards
Deepak

[Updated on: Tue, 16 August 2011 00:42]

Report message to a moderator

Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519640 is a reply to message #519639] Tue, 16 August 2011 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The main problem is NOT in the size of the tablespace, the main problem is: "why the space is getting consumed in the source?".
And as John showed we can't simulate the problem.
So unless OP provides us more information we can't help him as we can't know what is in his environment that can lead to this error.

Regards
Michel
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519684 is a reply to message #519640] Tue, 16 August 2011 05:45 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
I have solved the issue by rebuilting the indexes in the source Tablespace . Still I am confussed how the space got consumed in the source
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519694 is a reply to message #519684] Tue, 16 August 2011 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So now we will be able to not know.

Regards
Michel
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #583861 is a reply to message #519415] Mon, 06 May 2013 03:59 Go to previous messageGo to next message
mohit_aren
Messages: 2
Registered: May 2013
Location: India
Junior Member

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

CREATE USER egis IDENTIFIED BY egis;
GRANT CONNECT TO egis;

alter TABLESPACE USERS ADD DATAFILE 'RTMS.dbf' size 1000000
REUSE AUTOEXTEND ON NEXT
1000000

ALTER USER egis
IDENTIFIED BY egis
DEFAULT TABLESPACE users
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #583862 is a reply to message #583861] Mon, 06 May 2013 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question

What is your point?

Regards
Michel
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #583868 is a reply to message #583862] Mon, 06 May 2013 05:56 Go to previous messageGo to next message
mohit_aren
Messages: 2
Registered: May 2013
Location: India
Junior Member
It helped me when create table was failing earlier
Re: ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #583869 is a reply to message #583868] Mon, 06 May 2013 06:16 Go to previous message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum - always nice to see a new member whose first message is trying to help. Keep it up! And I hope you learn as well. I do - a lot.

But do read the messages before replying: this thread is over a year old.
Previous Topic: DBMS_SCHEDULER
Next Topic: Migration Assistance
Goto Forum:
  


Current Time: Sat Oct 20 18:27:40 CDT 2018