Q
Problem solve Get help with specific problems with your technologies, process and projects.

Questions about ORA-1652 error

I would like to ask you a question regarding a previous question ("ORA-1652: unable to extend temp segment by 1280 in tablespace TEMP") posted on your site. With regard to this bug what version was this behavior fixed in? Also, how would not releasing temp segments affect future connections?

Hello Brian. Firstly I read a lot of your posts and they have been a great help to me over the years.

I would like to ask you a question regarding a previous question ("ORA-1652: unable to extend temp segment by 1280 in tablespace TEMP") posted on your site. In reply to this question you say ". . . Oracle 8i and 9i when using Locally Managed Tablespaces (LMTs) for TEMP tablespaces when the applications maintain persistent connections, that the instance does not release unused temporary segments for future use by another process."

  1. With regard to this bug what version was this behavior fixed in?
  2. How would not releasing temp segments affect future connections?

My reason for asking is that we are on 9i SE v9.2.0.6 for Solaris, and without warning or being able to replicate at will we have two issues that could be caused by the same underlying issue: a) ORA-100: no data found, where data is clearly in the schema, and b) sometimes I see data written to the incorrect schema (were we have more than one schema which is almost the same on the same instance (develoment box)).

We cannot replicate this behavior at will, but if I turn on session tracing or flush shared_pool the problem goes away! Today I noticed a couple of occurances of the ORA-1652 error for both TEMP and other instance tablespaces in my alert.log over the last few weeks. Could these be linked?

I would appreciate whatever feedback you may have to offer.

There are a number of bugs associated with the TEMP tablespace not releasing space. You can look these up on Metalink if you desire. When Oracle first introduced Locally Managed Tablespaces and the CREATE TABLESPACE command, then under certain conditions, unused temp segments would not get reused, causing your temp tablespace to fill up. I'm not exactly sure when these were fixed, but I do know that the bugs were fixed before the 9.2.0.6 release you are currently running. It may be possible that you do not have enough space allocated to your TEMP tablespace if you are getting the ORA-1652 errors. I would increase the size of your TEMP tablespace. I would also consider utilizing automated work area management. I have a white paper on my Web site titled "Tuning PGA in Oracle 9i" which discusses this topic. It also sounds like you do not have enough space in some of your other tablespaces if you are receiving the ORA-1652 for non-TEMP tablespaces.

Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close