OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-01652 (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01652 [message #579603]

Wed, 13 March 2013 10:14

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

Report message to a moderator

Re: ORA-01652 [message #579604 is a reply to message #579603]

Wed, 13 March 2013 10:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Does SQL utilize Bind variables?
Can you compare EXPLAIN PLAN between when it runs OK versus when it errors out?

Report message to a moderator

Re: ORA-01652 [message #579605 is a reply to message #579604]

Wed, 13 March 2013 10:29

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.

Report message to a moderator

Re: ORA-01652 [message #579607 is a reply to message #579605]

Wed, 13 March 2013 10:56

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

You haven't answered the question about bind variables.

Report message to a moderator

Re: ORA-01652 [message #579612 is a reply to message #579607]

Wed, 13 March 2013 11:26

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.

Report message to a moderator

Re: ORA-01652 [message #579614 is a reply to message #579612]

Wed, 13 March 2013 11:41

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then I suggest you change the code to use bind variables.

Report message to a moderator

Re: ORA-01652 [message #579618 is a reply to message #579614]

Wed, 13 March 2013 12:01

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 ?

Report message to a moderator

Re: ORA-01652 [message #579620 is a reply to message #579618]

Wed, 13 March 2013 12:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I asked about bind variables because I thought that they may be the source of the problem; not the solution.

Report message to a moderator

Re: ORA-01652 [message #579790 is a reply to message #579620]

Fri, 15 March 2013 05:25

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

Report message to a moderator

Previous Topic:	how to see whether table data is using compresssion
Next Topic:	ORA-1033 error initialization or shutdown in progress

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 01:29:18 CST 2025