Oracle scheduled job error (ORA-01652)

Posted on 2011-04-21
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:


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
Question by:toooki
    LVL 5

    Expert Comment

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

    Author Comment

    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?
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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.

    LVL 14

    Accepted Solution

    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)


    Author Comment

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

    Expert Comment

    by:slightwv (䄆 Netminder)
    Good call ajexpert!

    Author Comment

    Many thanks all.
    The scheduled job run successfully today as well. so assume that the issue is fixed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Read about achieving the basic levels of HRIS security in the workplace.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now