Ask the Expert

Help with ORA-01652 error -- unable to extend temp segment

I am using BaaN ERP package in Oracle8i platform. During run of some tools session I got a fatal error and I realize that the problem is due to Oracle database. I then cheked the Oracle alert file and a message is reflected as below: "ORA-1652: Unable to extend tmp segment by 7 in tablespace"

I then created another data tablespace with 2,000 MB. I also checked temporary tablespace where 1,000 MB space available, but the problem remains same. What is the solution ?

    Requires Free Membership to View

The solution most likely lies in the remainder of the error message which you did not include. The ORA-1652 error message indicates exactly which tablespace is having the problem. You need to make sure that this tablespace has enough space. And sometimes, when you query DBA_FREE_SPACE for that tablespace, it might show that you have enough, but the process can still issue the above message. This is most likely due to fragmented free space. You have lots of little holes of free space, but none big enough to hold the entire extent that is being allocated. You have a few options in this case. One, simply add more space to the tablespace. This option is the quickest and easiest but doesn't defragment your free space. Two, find the object that is trying to acquire the next extent and modify it's next extent to be smaller than it currently is. Three, defragment your free space. This will involve moving the objects out of the tablespace and then moving them back in.

This was first published in January 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: