TEMPORARY Tablespaces and TEMPFILES

What are Temporary Tablespaces:

Temporary tablespaces are used to manage space for database sortoperations and for storing global temporary tables. For example, ifyou join two large tables, and Oracle cannot do the sort in memory(see SORT_AREA_SIZE initialisation parameter), space will beallocated in a temporary tablespace for doing the sort operation.Other SQL operations that might require disk sorting are: CREATEINDEX, 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 thedatabase to prevent them from allocating sort space in the SYSTEMtablespace. This can be done with one of the followingcommands:

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

SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Note that a temporary tablespace cannot contain permanentobjects and therefore doesn't need to be backed up.

What are TEMPFILES?

Unlike normal data files, TEMPFILEs are not fully initialised(sparse). When you create a TEMPFILE, Oracle only writes to theheader and last block of the file. This is why it is much quickerto create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database's control file. Thisimplies that one can just recreate them whenever you restore thedatabase, or after deleting them by accident. This opensinteresting possibilities like having different TEMPFILEconfigurations between permanent and standby databases, orconfigure TEMPFILEs to be local instead of shared in a RAC(RAC是Oracle Real ApplicationCluster的简写，官方中文文档一般翻译为“真正应用群集”，它一般由两台或者两台以上同构计算机及共享存储设备构成，可提供强大的资料库处理能力，现在是Oracle10g Grid应用的重要组成部分) environment.

One cannot remove datafiles from a tablespace until you drop theentire tablespace. However, one can remove a TEMPFILE from adatabase. Look at his example:

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

If you remove all tempfiles from a temporary tablespace, you mayencounter error: ORA-25153: Temporary Tablespace is Empty. Use thefollowing statement to add a TEMPFILE to a temporarytablespace:

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

Except for adding a tempfile, as illustrated in the aboveexample, you cannot use the ALTER TABLESPACE statement for alocally managed temporary tablespace (operations like rename, setto read only, recover, etc. will fail).

How does one create Temporary Tablespaces?

Oracle provides various ways of creating TEMPORARY tablespaces(mainly to provide backward compatibility). One should use the mostrecent method available:

- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE tempDATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE...;

Oracle 8i and 9i example:

SQL> CREATE TEMPORARY TABLESPACE temp

      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of theSORT_AREA_SIZE parameter.

Oracle 9i example using OMF (Oracle Managed Files):

SQL> CREATE TEMPORARY TABLESPACEtemp;

Default Temporary Tablespaces:

In Oracle 9i and above, one can define a Default TemporaryTablespace at database creation time, or by issuing an "ALTERDATABASE" statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACEtemp;

The default Default Temporary Tablespace is SYSTEM. Eachdatabase can be assigned one and only one Default TemporaryTablespace. Using this feature, a Temporary Tablespace isautomatically assigned to users. The following restrictions applyto default temporary tablespaces:

- The Default Temporary Tablespace must be of typeTEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until youcreate another one.

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

SQL> SELECT * FROM DATABASE_PROPERTIESwhere PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

All new users that are not explicitly assigned a TEMPORARYTABLESPACE, will get the Default Temporary Tablespace as itsTEMPORARY TABLESPACE. Also, when you assign a TEMPORARY tablespaceto a user, Oracle will not change this value next time you changethe Default Temporary Tablespace for the database.

Other Considerations:

Some performance considerations for temporary tablespaces:

- Always use temporary tablespaces instead of permanent contenttablespaces for sorting (no logging and uses one large sort segmentto reduce recursive SQL and ST space management enqueuecontention).
- Ensure that you create your temporary tablespaces as locallymanaged instead of dictionary managed (Use sort space bitmapinstead of sys.fet$ and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup andrecovery time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks toalleviate possible disk contention and to speed-up sortingoperations (user processes can read/write to it directly).

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE andDBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT andV$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporarytablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

 

TABLESPACE_NAME                BYTES_USED BYTES_FREE

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

TEMP                             52428800   52428800

 

　　　　　TEMPORARY表空间和文件

　　　　　　　　　　　　　　　　　　　　　　　

TEMPORARY 表空间是用来排序操作的，比如你操作几个大表，ORACLE没有足够的内存（sort_area_size）来排序,那么ORACLE就会分配 TEMPORARY表空间来排序。一些涉及排序的操作有CREATEINDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION,INTERSECT, MINUS, Sort-Merge joins等。

 

DBA应该给每个用户都设置一个默认TEMPORARY TABLESPACE，以防止用SYSTEM表空间排序。TEMPORARYTABLESPACE不包括永久性表空间，因此不需要备份。

 

TEMPORARY FILE也和其他数据文件不同，ORACLE在建立TEMPORARYFILE的时候只是标记文件的头和尾，这就是为什么建立TEMPORARY FILE比数据文件快的原因。

 

和数据文件的区别1：

在STANDBY结构下甚至PERMANENT和STANDBY数据库可以有不同的TEMPFILE，RAC结构下也可以每个节点配置各自的TEMPFILE。

 

和数据文件的区别2：

一个DATA表空间包含多个DATA FILE的情况下，不能单独删除其中的一个DATAFILE。但TEMPFILE可以。

SQL> select file_name||''||tablespace_name from dba_temp_files
  2  /

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\SVW\TEMP01.DBF TEMP

SQL> create temporary tablespacetemptest tempfile 'D:\ORACLE\ORADATA\SVW\TEMPtest.dmp' size10M
  2  /

Tablespace created.

 

  1* alter tablespace temptest addtempfile 'D:\ORACLE\ORADATA\SVW\TEMPTEST01.DMP' SIZE 10m
SQL> /

Tablespace altered.

SQL>  selectfile_name||' '||tablespace_name from dba_temp_files
  2  /

FILE_NAME||''||TABLESPACE_NAME
----------------------------------------------------------------
D:\ORACLE\ORADATA\SVW\TEMP01.DBF TEMP
D:\ORACLE\ORADATA\SVW\TEMPTEST.DMP TEMPTEST
D:\ORACLE\ORADATA\SVW\TEMPTEST01.DMP TEMPTEST

 

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

SQL> ALTER DATABASE TEMPFILE'D:\ORACLE\ORADATA\SVW\TEMPTEST01.DMP' DROP INCLUDINGDATAFILES;

Database altered.

SQL> select file_name||''||tablespace_name from dba_temp_files
  2  /

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\SVW\TEMP01.DBF TEMP
D:\ORACLE\ORADATA\SVW\TEMPTEST.DMP TEMPTEST

 

甚至你可以删除TEMPORARY TABLESPACE下的所有 TEMPFILE

SQL> ALTER DATABASE TEMPFILE'D:\ORACLE\ORADATA\SVW\TEMPTEST.DMP' DROP INCLUDING DATAFILES;

Database altered.

SQL> select file_name||''||tablespace_name from dba_temp_files
  2  /

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\SVW\TEMP01.DBF TEMP

再增加TEMPFILE  

SQL> alter tablespace temptest addtempfile 'D:\ORACLE\ORADATA\SVW\TEMPTEST.DMP' size 10M;

Tablespace altered.

 

各版本语句的比较

<Oracle 7.3

     CREATE TABLESPACE temp DATAFILE ...; -

Oracle 7.3 &8.0

     CREATE TABLESPACE temp DATAFILE ...TEMPORARY;

>Oracle 8i and above

     CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;

 

注意,如果加UNIFORM SIZE参数的话那么需要设置成SORT_AREA_SIZE才能达到最高性能.

 

相关语句

CREATE USER scott DEFAULT TABLESPACE data TEMPORARYTABLESPACE temp;

ALTER USER scott TEMPORARY TABLESPACE temp

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACEtemp;

 

SELECT * FROM DATABASE_PROPERTIES wherePROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE fromV$TEMP_SPACE_HEADER;

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

 

相关表

V$TEMPFILE     

DBA_TEMP_FILES    

V$SORT_SEGMENT

V$SORT_USAGE
V$TEMP_SPACE_HEADER