ORA-01658: unable to create INITIAL extent for segment in tablespace for the source TS [message #519415] |
Thu, 11 August 2011 23:44 |
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 #519467 is a reply to message #519415] |
Fri, 12 August 2011 07:16 |
John Watson
Messages: 8962 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 |
|
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
|
|
|
|
|
|
|
|
|
|