4 TABLESPACE(7): Temporary Tablespace

Temporary tablespace

Temporary table is used for operation like sorting and grouping function etc

Each user would be assign for one temporary table space. If no temporary table space is assigned. The user would use the default temporary table space..

List User and its default temporary tablespace

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile from DBA_USERS;

How to find the file location for the TEMP tablespace?

The information can be found at V$TEMPFILE and DBA_TEMP_FILE

select * from V$TEMPFILE;

select * from DBA_TEMP_FILES;

How to find out the current usage for the temp tablespace?

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

Create the user temporary table space

Below exercise is to create the temporary tablespace and assign to the user.

Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 100M;

col tablespace_name format a20

col "FILE_NAME" format a20

select DT.TABLESPACE_NAME,DT.contents,DT.STATUS,DT.SEGMENT_SPACE_MANAGEMENT, DT2.FILE_NAME,DT2.BYTES

from DBA_TABLESPACES DT, DBA_TEMP_FILES  DT2

where DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME;

create user po3 identified by oracle;

select username,temporary_tablespace from dba_users where username='PO3';

alter user po3 temporary tablespace myusertemp01;

select username,temporary_tablespace from dba_users where username='PO3';

Drop temp tablespace

If we don’t use “INCLUDING CONTENTS AND DATAFILES”, the tablespace would ONLY be drop from data dictionary but the file still exist on the operation. We then need to use OS level command to remove it.

-- Clean env

drop user PO3;

drop tablespace myusertemp01 INCLUDING CONTENTS AND DATAFILES;

temp table space group

Tempgroup

tempgroup is another layer of the logical grouping for the multiple temp tablespace. The purpose for this is to have more then one temp tablespace file to reduce the IO contention or if the temp tablespace has reach the max file system size then we have to add the another file on the different directory to increase the temp tablespace size.

In this example, we create 2 tablespace and assign them to tempgroup_a. If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.

select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 50M;

Create temporary tablespace myusertemp02 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp02.dbf' size 50M;

--  If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.

ALTER TABLESPACE myusertemp01 TABLESPACE GROUP tempgroup_a;

ALTER TABLESPACE myusertemp02 TABLESPACE GROUP tempgroup_a;

select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

create user po3 identified by oracle;

select username,temporary_tablespace from dba_users where username='PO3';

alter user po3 temporary tablespace tempgroup_a;

select username,temporary_tablespace from dba_users where username='PO3';

Remove the Tempgroup

Use alter tablespace to remove the tablespace from tempgroup. When there is no tablespace left in the tempgroup, tempgroup would be remove automatically.

ALTER TABLESPACE myusertemp01 TABLESPACE GROUP '';

ALTER TABLESPACE myusertemp02 TABLESPACE GROUP '';

select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

select username,temporary_tablespace from dba_users where username='PO3';

Even the TEMPGROUP_A does not exist anymore, but user PO3 still be able to run the query because at this point, the user PO3 is using the default tablespace.

Shrink temporary tablespace

KEEP keyword would shrink the tablespace to the keep size.

Without the keep keyword, it would shrink to its min size.

Create temporary tablespace myusertemp01 TEMPFILE size 100M;
alter tablespace myusertemp01 add tempfile size 100M;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';

alter tablespace myusertemp01 shrink space keep 50M;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';
alter tablespace myusertemp01 shrink space;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';