Q

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 ?
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close