Temp Tablespace

These are used for sorting operations, index creation and for creating global temporary tablespaces. When we don't have enough space in PGA oracle uses temp tablespace for processing joins also.

To check existing temporary tablespaces:

select tablespace_name, contents, status
from dba_tablespaces where contents = 'TEMPORARY';

select tablespace_name, file_name, bytes/1024/1024 "mb", status
from dba_temp_files;

To change the size of an existing tempfile:

alter database tempfile '/u01/prod/temp01.dbf' resize 30m;

select tablespace_name, file_name, bytes/1024/1024 "mb", status
from dba_temp_files;

To create a temporary tablespace:

create temporary tablespace temp1
tempfile '/u01/prod/temp02.dbf'
size 30m
autoextend on;

select tablespace_name, file_name, bytes/1024/1024 "mb", status

from dba_temp_files;

For some users, we can assign temp and for some temp1.

To see which one is the default temp tablespace:

select * from database_properties 

where property_name like '%TABLESPACE%';

To change the default temporary tablespace of a database:

alter database default temporary tablespace temp1;

select * from database_properties 
where property_name like '%TABLESPACE%';

Temporary tablespace groups:

It's a group of temporary tablespaces. Allow the oracle database to write to them simultaneously.

Instead of a single temp tablespace, we can assign a temp tablespace group to a user.

It allows a single SQL operation to use multiple temporary tablespaces for sorting.

When we create a temporary tablespace and assign it to a group, the group will be created. It gets deleted when we remove the last temp file associated with it.

As oracle can write to multiple temp tablespaces at a time, congestion can be reduced.

To Check existing Temporary tablespaces:

desc dba_tablespace_groups;

select * from dba_tablespace_groups;

Creating temporary tablespace group

create temporary tablespace temp3
tempfile '/u01/prod/temp03.dbf'
size 30m
tablespace group tempgrp;

select * from dba_tablespace_groups;

To check the existing temporary tablespaces in the database:

select tablespace_name, file_name from dba_temp_files;

Adding / Removing tablespaces from a group:

select * from dba_tablespace_groups;

alter tablespace temp1 tablespace group tmpgrp;

alter tablespace temp1 tablespace group '';

select * from dba_tablespace_groups;

Changing member of a tablespace group:

alter tablespace temp1 tablespace group tmpgrp2;

To check the current default temporary tablespace:

select * from database_properties
where property_name like '%TABLESPACE%';

Assigning a tablespace group as the default temporary tablespace

alter database default temporary tablespace tmpgrp;

select * from database_properties
where property_name like '%TABLESPACE%';

Views for temporary tablespace

v$tempfile

select * from v$tempfile;

select name, creation_time, ts#, enabled, status, bytes, blocks
from v$tempfile;

dba_temp_files

select * from dba_temp_files;

select file_name, tablespace_name, bytes, blocks, status, autoextensible
from dba_temp_files;

dba_tablespace_groups

select * from dba_tablespace_groups;

select group_name, tablespace_name 
from dba_tablespace_groups;