ORA-01653 unable to extend table

[Former Member]

Hi DB experts

When i was monitoring My server i found a dump.

Stating.

ORA-01653: unable to extend table SAPP22.ZINVAGING#$ by 1024 in tablespace PSAPP22USR

by 8192.

Though there is free space in the related tablespace it is not allocating

to that particular Table.

and it is not even allowing me to reorg the table.

What is action to be taken.

Regards

Kiran

[Former Member · Accepted solution]

Could be Insufficient space in harddisk,maxsize limitation for datafile.

If the harddisk has got enough space then there are 2 possible solutions for this problem:-

-

---

a.Setting the AUTOEXTEND ON for the datafile with MAXSIZE UNLIMITED

-

---

a.1) connect to the sqlplus as a SYSDBA or SYS user

a.2) execute the following query to know the path for the datafile:-

select * from DBA_DATA_FILES where TABLESPACE_NAME='<%TABLESPACE%>';

where TABLESPACE = name of the tablespace

For futher options check Note 682231 - [Oracle JDBC Driver][Oracle]ORA-01653:unable to extend table

also check if there are high no. of update termination you can solve the problem permanently by analyzing these terminations. You can use Transaction SM13 to analyze the update.

[Former Member · Accepted solution]

Hi Shaji

I am basically a BI Consultant..I am facing the same problem..I dont have idea abt all these Database Settings..

Can give me a step by step solution to resolve this issue..???

I saw your post on ORA-1653...even i face the same issue..

when I store in temporary database...

"DBSIF_SQL_ERROR..........."if I do it in Main memory

I get it as "NO MORE SPACE AVAILABLE"...

pls give where I can solve on my own,,,or only BASIS guy can do...

Edited by: SAP BI Learner on Apr 24, 2008 12:28 AM

[Former Member · Accepted solution]

If you haven't done before,I would advice you do get it done by a Basis consultant and learn.

[vince_laurent]

Dangerous to have autoextend set to on. Just wait till something goes awry and fills up ALL of your disk. My $0.03 is you give it more space and keep monitoring it. I am ASSUMING it is a user tablespace because it starts with a Z.... all the more reason to monitor it.

-vince

[Former Member]

when this error is occuring . when you run any specific process or it is happening for any process you run.

to know this you have check your dump and see in which programe does this error has occur and trace the transaction accordingly.

once you known the transaction analyz it to find the about of data is pushed

[Former Member]

Kiran,

within DB02 -> detail analysis -> obejct name <table*>

you can see the next extend.

and in DB02 -> current sizes -> free space analysis (select tablespace)

you can see that you migth have space but you don't have a continuous freespace.

Alter the tablespace to autoextend, alter datafile to autoextend or add a datafile.

cheers

[Former Member]

Hello

Was somebody doing a table reorg? I don't think ZINVAGING#$ is the real name of the table...

Regards, Michael

[Former Member]

Ok, i see now, you were indeed doing a table reorg. You had not sufficient space for the copy of the table. How big is it, and how much free space did you have?

Regards, Michael

[Former Member]

Dear Shaji.

This issue has been resolved by setting the Datafile in that particular to

autoextend "ON".

Regards

Kiran