We help IT Professionals succeed at work.

Oracle scheduled job error (ORA-01652)

toooki
toooki asked
on
Medium Priority
781 Views
Last Modified: 2012-05-11
I have a scheduled Oracle job (11gR2) that runs once a day at 8pm.. The job fails with this error showing in log:

"ORA-01652: unable to extend temp segment by 64 in tablespace TEMP"

The job is a stored procedure in the Oralce database. If I run the same procedure manually:

begin
myPackageName.myProcedureName;
end;

The above takes less than 10 minutes to run and I get no error on the IDE and the procedure successfully does its work (insert records in some tables with some complex queries).

I am not the DBA. The DBA just says me to look into the code and there is nothing wrong in database. And they say they assigned 90GB in the TEMP tablespace..

Other scheduled jobs are running without error. How do I fix this problem? job details IDE
Comment
Watch Question

Commented:
There is no disk space or temp tablespace reached maximum size.

Author

Commented:
I cannot do what a DBA could do. They are not listening.
How do I know that I am not taking too much temp tablespace in my scheduled job query?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you all.

I set the job to run at 6PM and waited. The job run without error and took 10minutes. There could be some issues at 8PM time. Actually before the job was showing failed with the run duration of about 4hours+.

The query I wrote is complex and I could not reduce the complexity...will look into that if somehow the job starts failing again in coming days.
 
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Good call ajexpert!

Author

Commented:
Many thanks all.
The scheduled job run successfully today as well. so assume that the issue is fixed.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.