Temporary Tablespace – Common Ora Errors

Temporary tablepspace is a very dynamic tablespace which gets filled up quickly, due to SQL operations that inludes sorting like CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS. Though this tablespace is not backed up or used for recovery, this is a very important tablespace for day to day activities. Below given are few error scenarios related to temporary tablespace.

First, lets see a common space issue error in Temporary tablepsace.

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

We need to add space to temporary tablespace.We can either add tempfile,resize or make it autoextend.

-Adding tempfile
ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<full path and filename>’ SIZE <integer> <k|m>;

– Resize the datafile:
ALTER DATABASE DATAFILE ‘<full path and file name>’ RESIZE <integer> <k|m>;

– Enable autoextend:
ALTER DATABASE DATAFILE ‘<full path and file name>? AUTOEXTEND ON MAXSIZE UNLIMITED;

Also, try coaleascing the tablespace. If extents are adjacent, this may help in releasing free contigoues space.

ALTER TABLESPACE temp COALEASCE;

Lets see anlother scenario, where a long sorting query was run in the database which has consumed all the space in temporary tablespace. The normal size of the temporary tablespace is 512MB.The bad query, increased the size of this tablespace  to over 13GB. But space consumption has reduced and I would like to reclaim that space.

SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

TABLESPACE_NAME   FILE_NAME                                        BYTES
—————– ——————————–             ————–
TEMP            TESTDB/temp01.dbf                               104857600

ALTER DATABASE TEMPFILE  TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

ORA-25153: Temporary Tablespace is Empty

This is the second important error, ORA-25153: Temporary Tablespace is Empty. There should atleast one tempfile left in the tablespace. Here since only one tempfile, Oracle does not allow us to drop it and throws this error.

In that case add a tempfile, and then drop.

Article by Divya