Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-01652 error when running query to delete large amount of data

I have been getting an error as follows when a query is running to delete a huge amount of data:

ORA-01652: unable to extend temp segment by 1598 in tablespace 
ORA_TEMPSPACE

I have increased the tempspace from 40 megabytes to 500 megabytes, and even after that I am seeing these errors. I have a few questions on this:

  1. What is the cause for this error?
  2. Does the hard disk space also matter here? If there is no space in the hard disk, does the tablespace still fail to extend, even though it has been resized ?
  3. What are the possible things that need to be checked on the database server before extending it further?

It sounds like you need more space in your TEMP tablespace. If you have set your TEMP tablespace's datafiles to automatically extend when more space is needed, then verify that the disk volume is not full. Otherwise, you can add another datafile to your TEMP tablespace.

If your TEMP tablespace is created with TEMPFILES (this can be verified by querying the DBA_TEMP_FILES view), then there is a bug in Oracle 9i where the temporary sort segments in the TEMP tablespace were not being released to be reused by new queries. This bug meant that space was slowly being lost in the TEMP tablespace. This bug is not present in Oracle 10.1.0.3. So one suggestion is to upgrade your database. If that is not possible, then consider recreating your TEMP tablespace without tempfiles.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close