Home » RDBMS Server » Server Administration » ORA-01652 (Oracle 11g)
ORA-01652 [message #579603] Wed, 13 March 2013 10:14 Go to next message
dileep.p
Messages: 4
Registered: March 2013
Junior Member
I am getting ORA-01652 temp space issue.
I read several posts before posting this and no luck.
We are submitting the job from java web based UI where it builds dynamic sql to submit the job.
The issue is not repeatable for the same input parameters i.e for the same sql.
Some times it will run just fine with out issues for the same input params and sql.
Only way I can reproduce the issue when i submit the job twice and some times,
both fails and some times one success and one fail, but not both success any time.
The job usually returns few millions of records and takes about 1.5 hrs to run.
I tried to extract the generated sql and run from pl/sql developer from two different sessions
at the same time and it will just run with out issues in both sessions. I repeated this
couple of times and success every time. Only issues when i submit from UI. There
seems some thing going in the java code the way it submits, but not able to figure out.
Appreciate if some one can help and throw their ideas.

We have plenty of temp space 300G. We monitored the temp space and it only takes about 4G when
it ran with out issues. We also monitored when the job fails as it keeps growing till it hit
300GB and throws the table space error.

We are using 11G version. Year ago we migrated from 10 to 11. But when the app developed, it is 9g.

Dileep
Re: ORA-01652 [message #579604 is a reply to message #579603] Wed, 13 March 2013 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Does SQL utilize Bind variables?
Can you compare EXPLAIN PLAN between when it runs OK versus when it errors out?
Re: ORA-01652 [message #579605 is a reply to message #579604] Wed, 13 March 2013 10:29 Go to previous messageGo to next message
dileep.p
Messages: 4
Registered: March 2013
Junior Member
SQL is auto generated by java program based on the fields that are choosen on the UI(including select fields, sort, joins ).
Explain plan is same in both cases. However some specific sql's always throws the error. IN this case if we remove the sort option from the UI it seems it is not taking any temp space and runs just fine.
Re: ORA-01652 [message #579607 is a reply to message #579605] Wed, 13 March 2013 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
You haven't answered the question about bind variables.
Re: ORA-01652 [message #579612 is a reply to message #579607] Wed, 13 March 2013 11:26 Go to previous messageGo to next message
dileep.p
Messages: 4
Registered: March 2013
Junior Member
ok, Since it uses java code to build the dynamic sql, so while building the sql statement it should be using java bind variables for assignment. But when the sql is passed from java to database, sql statement does not has any bind variables and has its values. Not sure if i answered this correctly as i am java developer. i.e the end sql executed does not has any bind variables.
Re: ORA-01652 [message #579614 is a reply to message #579612] Wed, 13 March 2013 11:41 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then I suggest you change the code to use bind variables.
Re: ORA-01652 [message #579618 is a reply to message #579614] Wed, 13 March 2013 12:01 Go to previous messageGo to next message
dileep.p
Messages: 4
Registered: March 2013
Junior Member

that was complex alogarithm generates the sql. since the sql is dynamic, introducing the sql bind variables may not be easy i think.
Do you think not using bind variables might be issue ? Is there any approach can think of ?
Re: ORA-01652 [message #579620 is a reply to message #579618] Wed, 13 March 2013 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
I asked about bind variables because I thought that they may be the source of the problem; not the solution.
Re: ORA-01652 [message #579790 is a reply to message #579620] Fri, 15 March 2013 05:25 Go to previous message
krishsidd
Messages: 2
Registered: March 2012
Location: India
Junior Member
As a short term suggestion - check with DBA if it is possible to increase the temp tablespace size and run the job.

but in long term, you might need to redesign your queries to prevent from eating temp by sorting 1.5 mill records at time.
If there is no other optimization possible then at least limit the number of records to be processed to a lower value where the job will run successful as per it's infrastructure and of course, you need to run the job multiple times, till all the required data is processed.
Hope this helps...
Previous Topic: how to see whether table data is using compresssion
Next Topic: ORA-1033 error initialization or shutdown in progress
Goto Forum:
  


Current Time: Tue Sep 30 19:56:28 CDT 2014

Total time taken to generate the page: 0.10680 seconds