當執行排序操作時，伺服器程序會将臨時資料放到PGA工作區。當PGA工作區不足以存放臨時資料時，伺服器程序會建立臨時段，并将這
些臨時資料存放到臨時段裡。
可以建立多個臨時表空間，但預設的臨時表空間也隻能有一個，預設臨時表空間不能offline和drop。如果未指定預設的臨時表空間
oracle将會使用system作為臨時表空間，不建議使用system作為臨時表空間。
1、指定預設的臨時表空間
09:00:53 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/lx02/temp01.dbf' size 100m reuse;
Tablespace altered.
09:01:14 SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf TEMP
09:01:17 SQL> select file#,name ,bytes/1024/1024 from v$tempfile;
FILE# NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf 100
09:01:22 SQL>
2、建立臨時表空間
09:04:18 SQL> create temporary tablespace tmp01
09:05:42 2 tempfile '/u01/app/oracle/oradata/lx02/tmp01.dbf' size 10m
09:06:03 3 extent management local uniform size 128k;
Tablespace created.
09:06:17 SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/lx02/temp01.dbf
2 /u01/app/oracle/oradata/lx02/tmp01.dbf
09:06:32 SQL>
3、檢視預設的臨時表空間
09:06:52 SQL> col PROPERTY_VALUE for a30
09:06:59 SQL> col description for a50
09:07:04 SQL> r
1* select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME LX02 Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
4、使用者指定臨時表空間
20:55:00 SQL> alter user scott temporary tablespace tmp01;
User altered.
5、切換預設的臨時表空間
09:07:05 SQL> alter database default temporary tablespace tmp01;
Database altered.
09:07:34 SQL> select * from database_properties
09:07:39 2 ;
DEFAULT_TEMP_TABLESPACE TMP01 Name of default temporary tablespace
6、臨時表空間租
（1）建立臨時表空間組
09:07:41 SQL> alter tablespace temp tablespace group tmpgrp;
09:09:33 SQL> alter tablespace tmp01 tablespace group tmpgrp;
09:09:38 SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP
TMPGRP TMP01
（2）将臨時表空間組射程預設臨時表空間，可以實作負載均衡
09:09:52 SQL> alter database default temporary tablespace tmpgrp;
09:10:10 SQL> select * from database_properties;
DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace
09:10:17 SQL>
（3）檢視臨時表空間資訊
21:03:08 SQL> col name for a50
21:03:10 SQL> r
1* select file#,name,bytes from v$tempfile
FILE# NAME BYTES
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/prod/temp01.dbf 20971520
2 /u01/app/oracle/oradata/prod/tmp01.dbf 10485760
3 /u01/app/oracle/oradata/prod/tmp02.dbf 10485760
21:03:11 SQL> desc dba_temp_files;
Name Null? Type
----------------------------------------------------------------- -------- -----------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
CUUG
更多oracle視訊教程請點選：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6
![Oracle的表空間遷移[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)