Ask the Expert

"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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: