Managing Temp Tablespace

TEMPORARY TABLESPACES

What are Temporary Tablespaces?

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary Tablespaces for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

SQL> CREATE USER scott DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

SQL> ALTER USER scott TEMPORARY TABLESPACE temp2;

A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.

Temporary tablespaces are excluded from RMAN backups. This reqires them to be re-added at recovery time.

You cannot remove datafiles from a tablespace until you drop the entire tablespace. However, you can remove a TEMPFILE from a database. Look at this example:

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace:

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

Creating Temporary Tablespace

The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.

SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;  

Default Temporary Tablespaces

In Oracle 9i and above, you can define a Default Temporary Tablespace at database creation time, or by issuing an ALTER DATABASE statement:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

To see the default temporary tablespace for a database, execute the following query:

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Altering a Locally Managed Temporary Tablespace

Except for adding a tempfile, taking a tempfile offline, or bringing a tempfile online, as illustrated in the following examples, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace.

ALTER TABLESPACE lmtemp

   ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;

ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

Resizing Temp File

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

Dropping Temp file

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

Practicals:

desc dba_temp_files
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by FROM  dba_temp_files;

Calculate free space in TEMP tablespace

select tablespace_name, SUM(bytes_used), sum(bytes_free)

from v$temp_space_header

where tablespace_name = ‘TEMP’

group by tablespace_name;

#Segments using Sort Area

SELECT   b.TABLESPACE,

         b.segfile#,

         b.segblk#,

         b.blocks,

   b.blocks*16/1024 as MB,

         a.SID,

         a.serial#,

         a.status

FROM     v$session a,

         v$sort_usage b

WHERE    a.saddr = b.session_addr

ORDER BY b.TABLESPACE,

         b.segfile#,

         b.segblk#,

         b.blocks;

SELECT T.TABLESPACE_NAME,
NVL(ROUND(((SUM(U.BLOCKS)*P.VALUE)/1024/1024),
2),0) USED_MB,
T.TOT_MB,
NVL(ROUND(SUM(U.BLOCKS)*P.VALUE/1024/1024/T.TOT_MB * 100,2),0) "USED %"
FROM GV$SORT_USAGE U,
GV$PARAMETER P,
(SELECT TABLESPACE_NAME,
SUM(BYTES)/1024/1024 TOT_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) T
WHERE P.NAME = 'db_block_size'
AND U.TABLESPACE (+) = T.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME,
P.VALUE,
T.TOT_MB
ORDER BY 1,2;

Creating temp2 Tablespace:

SQL> CREATE TEMPORARY TABLESPACE temp2

  2  TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE

  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Making default Tablespace to Database:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

Dropping a existing temp file:

The first step is to obtain the name of the tempfile to drop. For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp2_01.dbf: 

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME   FILE_NAME                                 BYTES

----------------- -------------------------------- --------------

TEMP              /u02/oradata/TESTDB/temp01.dbf   13,107,200,000

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Creating again temp Tablespace and making default:

SQL> CREATE TEMPORARY TABLESPACE temp

  2  TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE

  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited

  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

Dropping temp Tablespace Again:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

11g Temp Usage View

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

------------------------------ --------------- --------------- ----------

TEMP                                  56623104        56623104   55574528

1 row selected.

11g Shrinking Temp Tablespace

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

Error:

Error : ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2

Views:

V$TEMP_SPACE_HEADER - Shows space used/free for each tempfile

V$TEMPFILE -  Information about all tempfiles, including tablespace number of owning tablespace.

DBA_FREE_SPACE, USER_FREE_SPACE  - Information about free extents within all (or user accessible) tablespaces.

V$TEMPSEG_USAGE  - Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

DBA_SEGMENTS, USER_SEGMENTS -  Information about segments within all (or user accessible) tablespaces.

DBA_EXTENTS, USER_EXTENTS - Information about data extents within all (or user accessible) tablespaces.

Dba_temp_free_space