Managing TEMP Tablespace

In Oracle database, temporary tablespace (also known as temp segment) and UNDO tablespace (also known as UNDO segment) are two very important things.

As I decided to write important stuffs time to time I do with database, it's time to write few important things on TEMP tablespace. Today, I had to remember TEMP tablespace related stuffs again - one of my friends was facing problem with BLOB updates for long running transactions in his system.

There is no database guy who never saw this error message - ORA-1652: unable to extend temp segment by .. this was the case with my friend. He is not a database guy - so here I go. There was not enough temp space to support his long running transactions which were storing LOB data. So I made that auto extendable.



What is TEMP Tablespace?

It is a special tablespace, needed for oracle database to store temporary data/segments to support other operations. One database can have at most one active temp tablespace at a time, but of course temp tablespace can have one or more data files like other tablespaces.

Temporary segments are created in temp tablespace during -

* Large sort operation, when PGA_AGGREGATE_TARGET can't accommodate with the size. These include ORDER BY, GROUP BY, DISTINCT, MERGE JOIN, HASH JOIN or CREATE INDEX.

* When temporary table is used

* To store temporary LOB data

How to increase temp size

SQL> SELECT tablespace_name, file_name, bytes/1024/1024 MB, AUTOEXTENSIBLE
FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME MB AUT
----------------- -------------------------------- ------------ ------------
TEMP /data/orcl/temp01.dbf 100 NO


SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' RESIZE 500M;

If the file is not auto extensible, if we have enough disk space, we could set auto extend on and max size unlimited for that.

SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;


How to shrink/reclaim space

There might be cases, one long running query or lob segment might increase the temp tablespace size significantly but we don't want to keep temp tablespace strict to that size as we may not need such large file in general.

The idea is to create another temporary temp tablespace (say temp2) and temporarily assign it to the database. Finally recreate the desired temp tablespace and reassign it and drop the temp things.


SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/data/orcl/temp2.dbf' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;


SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;


SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/data/orcl/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

If database is not accessible by users, then we could do it in the following shortcut way -

SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' DROP INCLUDING DATAFILES;


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/orcl/temp01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;

Monitoring TEMP segments

I use this script to identify 'who' is eating up temp space and why -

set linesize 200
col sid_serial format a10
col sql_text format a40
col username format a10
col program format a25


SELECT sq.sql_text, ROUND(((b.blocks*p.value)/1024/1024),2)||'MB' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p,
v$sql sq
WHERE a.saddr = b.session_addr
AND b.sql_id = sq.sql_id
AND p.name = 'db_block_size'
AND b.tablespace = 'TEMP'
ORDER BY b.tablespace, b.blocks;