• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

Oracle scheduled job error (ORA-01652)

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
0
toooki
Asked:
toooki
2 Solutions
 
morguloCommented:
There is no disk space or temp tablespace reached maximum size.
0
 
toookiAuthor 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?
0
 
slightwv (䄆 Netminder) Commented:
The error is showing TEMP1 as the tablespace.  Verify that is the one with 90G assigned.

You should check the SQL in the procedure.  Look at the ones selecting from large tables using ORDER BY or a lot of joins.

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
ajexpertCommented:
What I can think of is:

Since your procedure is running fine, I believe, during 8 PM window, there could be lot of processes which could eventually filling out TEMP tablespace.

If this job does not have any dependancy then
Can you try to schedule this job at some other time (preferably when you tried to execute the SP and it ran fine)

HTH
0
 
toookiAuthor 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.
 
0
 
slightwv (䄆 Netminder) Commented:
Good call ajexpert!
0
 
toookiAuthor Commented:
Many thanks all.
The scheduled job run successfully today as well. so assume that the issue is fixed.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now