Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

-1653: ORA-01653: unable to extend table

muttleychessOct 18 2006 — edited Jul 9 2007
Hi

When I was trying Insert in my table (TAB1) , return me error below:

1653: ORA-01653: unable to extend table TAB1 by 50 in tablespace ATTRIBUTE

What is this error ?

My tablespace is:
TABLESPACE ATTRIBUTE
PCTUSED 60
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 400K
NEXT 400K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 4
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

Message was edited by:
muttleychess

Comments

444756
TAB1 is a table segment. When you are inserting it is trying to take extents.

Please increase the tablespace size.
452030
Resize solve your problem
alter database datafile '<full_file_name>' resize <size>M;
muttleychess
I Edit original message: look the defintion of the tablespace
Satish Kandi
Please post the output of following query.

SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE
group by tablespace_name;

You are running out of space in the physical files allocated to your tablespace.

May be the Autoextend is off or they have reached maxsize with Autoextend on.
muttleychess
tks

1 ATTRIBUTE 198623232
Satish Kandi
Your insert is looking for 50 extents.

So by calculation your need is (400K*50) 20480000 bytes > 198623232 bytes available in the file.

you need to add more space to this tablespace.
Satish Kandi
Sorry..wrong calculation. ignore my post.
muttleychess
Hi

Can to be ?
PCTINCREASE 0

I am using

INSERT /*+ APPEND */ INTO MY TABLE SELECT ...ETC


This insert +/- 5000000 rows, Can to be because there is not commit ?

Is best to use a cursor ? :-(

Message was edited by:
muttleychess
Satish Kandi
PCTINCREASE is regarding calculation of next extent size to be allocated. Since it is set to 0, the next extent allocated to the table will always be of 400K.
502493
Is best to use a cursor ? :-(
I wouldn't use a cursor..
http://asktom.oracle.com/pls/ask/f?p=4950:8:9154101500537953706::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:73891904732164
insert and commit at the end! works fine.
muttleychess
But It out error, before commit

can I other hint ? ( nologging ) ,

How can I to resolve this error, without to use Cursor ?
Satish Kandi
What is the output of following query?

select count(extent_id) from dba_extents where tablespace_name='ATTRIBUTE';
muttleychess
tks

6566
502493
maybe it's the append hint causing the problem. a bit of a similar problem is discussed here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:15571223360424060549::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6273519667276
587034
Try this,

Alter table table_name storage(next_extent 32K);

kindly check the extend you want to set.


Regards,
Uday Patil
1 - 15
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 6 2007
Added on Oct 18 2006
#general-database-discussions
15 comments
63,746 views