Q

"ORA-1652:unable to extend temp segment by 128 in tablespace TEMP" error

I am getting the error, "ORA-1652:unable to extend temp segment by 128 in tablespace TEMP." The tablespace is the Default Temporary Tablespace with autoextend clause, and the hard disk space is almost full. Could you please suggest what the problem might be?

When you perform a large sort and that sort operation cannot be performed in memory, then the sort is broken into pieces and those pieces are stored on disk. The location on disk is your TEMP tablespace. This ORA-1652 error is telling you that this tablespace does not have enough free space. You can add more space to the TEMP tablespace by either letting the tempfile (or datafile) extend more, or by adding another tempfile (or datafile)....

First, query DBA_TEMP_FILES to see if you are using tempfiles for the TEMP tablespace. If so, then add another tempfile with a command similar to the following:

ALTER TABLESPACE temp ADD TEMPFILE '/my_directory/temp02.dbf' SIZE 200M;

If the TEMP tablespace's files are seen in DBA_DATA_FILES instead, then substitute the word DATAFILE for TEMPFILE above.

This was first published in November 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close