Temporary Tablespaces-蒲公英云

In this Document
Purpose
Scope and Application
DB 11.1: Temporary Tablespaces [Video]

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 24-Jan-2012***
Purpose

This video note is intended to help the customers and support engineers to see the 11g new features on temporary tablespaces.

We will focus on three major features:

Temporary tablespace shrink
DBA_TEMP_FREE_SPACE
New Tablespace option when creating temporary tables

Scope and Application

These features are available in all editions of 11g database software.

Temporary tablespace shrink

In 11g there is the option available to ONLINE shrink the temporary tablespaces. It can be shrunk to a lower limit that we can specify using KEEP clause, or, if not specified, then it will be shrunk as much as possible as long as the other storage attibutes are satisfied. Due to being an online operation, the sessions and queries running at that time are not affected.

When no KEEP clause is specified, it will free and give back to the OS all the extents that are not used at that moment, but are already allocated.

We have the option of shrinking the whole temporary tablespace or we can only shrink a specific temp file.
The following syntax can be used to achieve this:

ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

NOTE:
The ALTER DATABASE TEMPFILE RESIZE command generally fails with ORA-03297 if the tempfile contains used data beyond the requested RESIZE value. As opposed to ALTER TABLESPACE SHRINK, the ALTER DATABASE command does not try to deallocate sort extents after they are allocated.

Following is an example of SHRINK commands:
SQL> create temporary tablespace temp2 tempfile ‘test2.dbf’ size 50M;

Tablespace created.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-——————————————- ———————- ———————- —————
TEMP 20971520 4194304 19922944
TEMP2 52428800 1048576 51380224

SQL> alter tablespace temp2 shrink space keep 40M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-——————————————- ———————- ———————- —————
TEMP 20971520 4194304 19922944
TEMP2 42991616 1048576 41943040

SQL> alter tablespace temp2 shrink tempfile ‘D:\APP\SANTONIE\PRODUCT\11.1.0\DB_1\DATABASE\TEST2.DBF’ keep 30M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-——————————————- ———————- ———————- —————
TEMP 20971520 4194304 19922944
TEMP2 32497664 1040384 31457280

SQL> alter tablespace temp2 shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-——————————————- ———————- ———————- —————
TEMP 20971520 4194304 19922944
TEMP2 2088960 1040384 1048576

  2. DBA\_TEMP\_FREE\_SPACE

This view is new in 11g and it contains information about temporary segments usage, grouped by each tablespace.

Columns description:

TABLESPACE_NAME VARCHAR2(30) NOT NULL
Name of the tablespace

TABLESPACE_SIZE NUMBER
Total size of the tablespace, in bytes

ALLOCATED_SPACE NUMBER
Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse

FREE_SPACE NUMBER
Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated

By using columns 2,3, and 4 we can find out the amount of allocated and used space, the amount of allocated but free for reuse space and the amount of unallocated space.

  3. New TABLESPACE option when creating temporary tables

Prior to 11g, when we created a temporary table, we could not specify a temporary tablespace for it. It was created in the default temporary tablespace of the user which created it.

Now, we have the option to specify a different temp tablespace in which the table can be created, other than the users default temp tablespace. Also, the indexes attached to this temporary table will be created in the specified tablespace.

This can be very useful when we have different temporary tables and we wish a different extent setting for them.
To view the tablespace to which the temporary table belongs to, we can query DBA_TABLES.

Following is an example of this command:

SQL> alter user test temporary tablespace TEMP2;

User altered.

SQL> create global temporary table tmp_table (a number) on commit delete rows tablespace TEMP;

Table created.