Temporary Tablespaces

以你之姓@ 2022-03-31 05:40 259阅读 0赞

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.

1. 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;

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;

SQL> alter tablespace temp2 shrink space;

Tablespace altered.

SQL> select \* from dba\_temp\_free\_space;

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.