有關temp表空間的一點總結！

我一直都把oracle中的temp tbs理解成win下的虛擬記憶體和unix下的swap分割槽。temp使用不當會影響oracle資料庫的效能。

1、臨時表空間的用途

1）disk sort

在瞭解disk sort排序之前，先來看一段doc（When the WORKAREA_SIZE_POLICY parameter is set to MANUAL, the
maximum amount of memory allocated for a sort is defined by the
parameter SORT_AREA_SIZE. If the sort operation is not able to
completely fit into SORT_AREA_SIZE memory, then the sort is separated into phases.The temporary output of each phase is stored in temporary
segments on disk. The tablespace in which these sort segments are created is the users temporary tablespace.When Oracle writes sort operations to disk, it writes out partially sorted
data in sorted runs. After all the data has been received by the sort, Oracle merges the runs to produce the final sorted output. If the sort area
is not large enough to merge all the runs at once, then subsets of the runs
are merged in several merge passes. If the sort area is larger, then there
are fewer, longer runs produced. A larger sort area also means that the
sort can merge more runs in one merge pass.）

從上面doc看來，我自己的理解是排序始終是在記憶體裡完成的，如果要排序的資料量很大，在記憶體裡不能完成，oracle會分階段來排序，每次先排一部分，並且把排好序的資料臨時存放在使用者default temporary tablespace中的temp segment上，而臨時表空間對應的tempfile屬於disk檔案，這就是disk sort的由來。具體oracle是如何分階段來排序的，doc說的還算清楚，只是可能不太好理解。其實上面doc說的不正是排序時常提到的3種情況：optimail，one pass sort，mutli-pass sort
a）optimal

如圖1，我們知道排序最理想的方式是optimal，也就是需要排序的資料都在記憶體裡放的下，而且記憶體有足夠空間做排序。排序本身的原理可能是相當複雜的，但是大致的說法應該是排序時在記憶體需要維護一個樹狀的結構來完成排序，所以假如你有5M的資料需要排序，這時候你需要的記憶體會遠大於5M。

b）one pass sort

如圖2假如需要排序的是1到20，但記憶體一次只能排序5個資料，這時候不得不5個資料做一個排序，每排好一組就放在tempfile上，最後在磁碟上就存在4組資料，這時候If the sort area
is large enough to merge all the runs at once，那麼所做的sort就是one pass的。在這個特定的例子裡，large enough應該是指有能力一次在記憶體裡做4個資料的排序，及首先在1,2,12,11中能夠選出最小的，就是1，然後在接下來的6,2,12,11裡選出2, 然後在6,3,12,11中選出3，以此類推

c）mutli-pass sort
如圖3，在這個例子裡，如果記憶體小到一次只能排序3個資料，那onepass 就做不到了，按照和onepass類似的方法，先在tempfile得到7組資料，然後這時候因為the sort area
is not large enough to merge all the runs at once, then subsets of the runs
are merged in several merge passes.

因此需要把7組資料變成第二階段的3組，然後在把這3組資料排序。因為在磁碟上存了2次資料，所以叫multi-pass，記憶體越小，pass的次數越多，排序需要的時間也就越長。

2）global temporary table

oracle支援兩種型別的臨時表，之所以稱為global，是因為不論是哪個session建立的臨時表，該表對所有的session都是可見的，但是資料僅僅對建立臨時表的session可見。

oracle支援兩種型別的臨時表：

a）transaction-specific (the default)

doc中提到的transaction-specific其實就是指在建立臨時表時在語法中指定的on commit delete rows，這種型別的臨時表在commit之後也就是事務結束以後資料被刪除了：

SQL> create global temporary table gt1 as select * from dba_tables;

表已建立。

SQL> select count(*) from gt1;

COUNT(*)
----------
0

b）session-specific

這種臨時表指的就是在建立時使用on commit preseve rows子句建立的臨時表，它的特點是在事務結束以後資料並沒有立即被刪除，而是在session結束之後資料被刪除的：

SQL> create global temporary table gt3 on commit preserve rows as select * from
dba_tables;

表已建立。

SQL> select count(*) from gt3;

COUNT(*)
----------
1212

在另一個sqlplus視窗看看資料發現不可見：

SQL> select count(*) from gt3;

COUNT(*)
----------
0

SQL>

再回到建立gt3時的視窗：

SQL> insert into gt3 select *from dba_tables;

已建立1213行。

SQL> select count(*) from gt3;

COUNT(*)
----------
2425

SQL> rollback;

回退已完成。

SQL> select count(*) from gt3;

COUNT(*)
----------
1212

SQL> truncate table gt3;

表被截斷。

SQL> select count(*) from gt3;

COUNT(*)
----------
0

SQL>

試驗發現global temporary table支援rollback，意味著對temporary table的操作是需要佔用undo的。臨時表也支援truncate，而doc上說的（DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table）有些莫名其妙。

我們在判斷臨時表到底是屬於那種型別時除了透過資料驗證之外，也可以查詢資料字典獲得：

SQL> select TABLE_NAME ,TEMPORARY , DURATION,tablespace_name,logging from dba_t
ables where table_name in ('GT1','GT2','GT3');

TABLE_NAME T DURATION TABLESPACE LOG
------------------------------ - --------------- ---------- ---
GT3 Y SYS$SESSION NO
GT2 Y SYS$TRANSACTION NO
GT1 Y SYS$TRANSACTION NO

DURATION欄位說明了臨時表的型別。

退出建立gt3時的session發現資料被清除了：

SQL> insert into gt3 select *from dba_tables;

已建立1213行。

SQL> commit;

提交完成。

SQL> select count(*) from gt3;

COUNT(*)
----------
1213

SQL> disconnect
從 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開
SQL> connect as sysdba
已連線。
SQL> select count(*) from gt3;

COUNT(*)
----------
0

臨時表和普通的heap表產生的日誌對比：

SQL> create table t1 tablespace users as select * from dba_objects where 1=2;

表已建立。

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 3248648

SQL> insert into t1 select * from dba_objects;

已建立11260行。

SQL> commit;

提交完成。

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 4780384

SQL> select 4780384 - 3248648 from dual;

4780384-3248648
---------------
1531736

再來看看同樣的操作臨時表產生的reodo：

SQL> create global temporary table gt1 on commit preserve rows as select * from
dba_objects where 1=2;

表已建立。

SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 1584124

SQL> insert into gt1 select * from dba_objects;

已建立11456行。

SQL> commit;

提交完成。

SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 1773124

SQL> select 1773124 - 1584124 from dual;

1773124-1584124
---------------
189000

相差的redo：

SQL> select 1531736 - 189000 from dual;

1531736-189000
--------------
1342736

SQL>

透過上面對比我們發現同樣的操作使用臨時表比普通的表要少產生很多日誌；而儘管臨時表顯示的是nologging，但是還是產生了一些日誌，這些日誌其實是由undo 產生的，因為臨時表支援rollback，意味著對臨時表執行dml操作是需要佔用undo的，而undo本身也是需要保護的，因此對undo的使用產生了一部分redo。

另外在建立臨時表時不能指定tablespace tablespace_name子句，臨時表只能使用使用者的default temporary tablespace：

SQL> create global temporary table gt2(id int) on commit preserve rows tablespac
e tmp;
create global temporary table gt2(id int) on commit preserve rows tablespace tmp

*
第 1 行出現錯誤:
ORA-14451: 不受支援的臨時表功能

瞭解了臨時表的種類以及臨時表的特性之後就可以根據需要靈活的使用臨時表。

2、臨時表空間的分類

上面提到disk sort會使用臨時表空間，根據這個用途，臨時表空間可以分為下面3種：

1）Permanent（其實就是指system表空間）

在dmt下，如果沒有為系統建立Tablespaces of Type TEMPORARY 型別的表空間，當然如果也不存在lmt管理的temporary tablespace，此時系統在disk sort時會使用system表空間，而system表空間屬於Permanent（永久）表空間，使用時會產生redo。簡單的做個測試：

SQL> select tablespace_name , extent_management,contents,logging from dba_tables
paces;

TABLESPACE EXTENT_MAN CONTENTS LOGGING
---------- ---------- --------- ---------
SYSTEM DICTIONARY PERMANENT LOGGING
UNDOTBS1 LOCAL UNDO LOGGING
SYSAUX LOCAL PERMANENT LOGGING
TEMP LOCAL TEMPORARY NOLOGGING
USERS LOCAL PERMANENT LOGGING

10g引入了default temporary tablespace，default temporary tablespace不能刪除，除非再指定一個temporary tablespace為default的或者system也可以作為default temporary tablespace
SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
第 1 行出現錯誤:
ORA-12906: 不能刪除預設的臨時表空間

SQL> alter database default temporary tablespace system;

資料庫已更改。

除了system表空間之外沒有哪種Permanent表空間可以作為臨時表空間用

SQL> alter database default temporary tablespace sysaux;
alter database default temporary tablespace sysaux
*
第 1 行出現錯誤:
ORA-12902: 預設的臨時表空間必須屬 SYSTEM 或 TEMPORARY 型別

SQL> drop tablespace temp including contents and datafiles;

表空間已刪除。
SQL> select tablespace_name , extent_management,contents,logging from dba_tables
paces;

TABLESPACE EXTENT_MAN CONTENTS LOGGING
---------- ---------- --------- ---------
SYSTEM DICTIONARY PERMANENT LOGGING
UNDOTBS1 LOCAL UNDO LOGGING
SYSAUX LOCAL PERMANENT LOGGING
USERS LOCAL PERMANENT LOGGING

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 110271220

SQL> select group# , status , sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 130
2 CURRENT 131
3 INACTIVE 129

對t表（t是一個具有72w資料大約80m左右的表）進行排序：

select * from t order by object_id desc

SQL> select group# , status , sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 130
2 ACTIVE 131
3 CURRENT 132

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 113859320

SQL> select (113859320 - 110271220)/1024/1024 from dual;

(113859320-110271220)/1024/1024
-------------------------------
3.42187881

我們發現日誌進行了切換，而且生成了大約3.5m的redo。

檢視disk sort使用sytem 表空間的情況：

SQL> select policy ,work_area_size , actual_mem_used , number_passes , tempseg_s
ize , tablespace from v$sql_workarea_active;

POLICY WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE TABLES
------------ -------------- --------------- ------------- ------------ ------
AUTO 2412544 2675712 1 86833152 SYSTEM

清楚的看到使用了system表空間進行了disk sort，使用Permanent tablespaces（就是指system表空間）進行disk sort排序的缺點是：

Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts. This is because of the following reasons:

• The ST-enqueue is used for allocation and de-allocation of each extent allocated to a sort segment.
• Sort-segments are not reused. Each process performing a disk sort creates then drops it's own sort segment. In addition, a single sort operation can require the allocation and deallocation of many extents, and each extent allocation requires the ST-enqueue.

簡單的驗證一下sort segment的分配和回收情況：

排序時system的空間使用情況：

SQL> select round((a.bytes - b.bytes)/a.bytes*100,2) used_space_pct
2 from dba_data_files a, dba_free_space b
3 where a.file_id = b.file_id
4 and a.file_id=1;

USED_SPACE_PCT
--------------
98.57

排序後system的空間使用情況：

SQL> select round((a.bytes - b.bytes)/a.bytes*100,2) used_space_pct
2 from dba_data_files a, dba_free_space b
3 where a.file_id = b.file_id
4 and a.file_id=1;

USED_SPACE_PCT
--------------
70.96

也就是說排序之後sort segment立即進行了釋放，而在為sort segment分配和回收extents都會產生ST-enqueue ( Space transaction enqueue)，這一點可以在排序時觀察到，一般不太容易觀察到：

SQL> select * from v$lock where type='ST';

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ----
------ ----------
6D22DDE4 6D22DDF8 26 ST 0 0 6 0
0 0

另外使用system表空間進行disk sort時的temp segment是在排序時建立的，而排序之後立即進行了刪除，也就是說這個排序段不能重複利用。簡單做個測試：

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未選定行

--在plsql developer對錶t實行排序：

select * from t order by object_id desc ;

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

SEGMENT_NA SEGMENT_TY TABLESPACE HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ---------- ---------- ----------- ------------ ---------- ----------
1.109061 TEMPORARY SYSTEM 1 109061 95136 19

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未選定行

SQL>

對比排序前後temp segment的情況，顯然temp segment是在排序時建立的，排序之後立即釋放了，如果下次需要排序，還需要重新分配extent，重新建立temp segment，而分配或者回收extnet都會產生ST-equence.而ST-equence正是dmt的缺陷。

2）Tablespaces of Type TEMPORARY

在沒有lmt之前oracle沒有tempfile的概念，因此建立temp tbs的語法是：

SQL> create tablespace temp datafile 'E:ORACLEPRODUCT10.2.0ORADATADMTTEMP0
1.DBF' SIZE 10M AUTOEXTEND ON TEMPORARY;

表空間已建立。

在10g環境透過上面語法建立的表空間temp的extent管理方式是dictionary，而segment space management 是auto，這顯然是有問題的；而它的contents是TEMPORARY，說明它是臨時表空間，同時也要注意是logging，意味著使用它也要產生redo

SQL> select tablespace_name,contents , logging , extent_management,segment_space
_management from dba_tablespaces;

TABLESPACE CONTENTS LOGGING EXTENT_MAN SEGMEN
---------- --------- --------- ---------- ------
SYSTEM PERMANENT LOGGING DICTIONARY MANUAL
UNDOTBS1 UNDO LOGGING LOCAL MANUAL
SYSAUX PERMANENT LOGGING LOCAL AUTO
TEMP TEMPORARY LOGGING DICTIONARY AUTO
USERS PERMANENT LOGGING LOCAL AUTO

SQL> alter database default temporary tablespace temp;

資料庫已更改。

SQL> select * from t order by object_id desc ;
select * from t order by object_id desc
*
第 1 行出現錯誤:
ORA-10615: Invalid tablespace type for temporary tablespace

出現錯誤的原因在於：在dmt下不可能存在segment space management auto的tbs，這應該算是10g orace的一個漏洞吧，儘管在10g下使用dmt已經很少了，而使用這種方式建立的temp tbs就更少了，但是應該瞭解oracle的歷史產物。

SQL> alter database default temporary tablespace system;

資料庫已更改。

SQL> drop tablespace temp ;

表空間已刪除。

重新建立一下臨時表空間，指定為segment space management manual：
SQL> create tablespace temp datafile 'E:ORACLEPRODUCT10.2.0ORADATADMTTEMP0
1.DBF' SIZE 10M reuse AUTOEXTEND ON TEMPORARY segment space management manual;

表空間已建立。

SQL> alter database default temporary tablespace temp;

資料庫已更改。

--再次在plsql developer執行如下語句發現沒有錯誤

select * from t order by object_id desc

而在排序的過程中發現日誌組進行了非常頻繁的切換，意味著在排序期間產生了大量的redo，而且建立了temp segment：

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
ACTIVE 1
CURRENT 2
ACTIVE 3

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
ACTIVE 1
ACTIVE 2
CURRENT 3

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

SEGMENT_NA SEGMENT_TY TABLESPACE HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ---------- ---------- ----------- ------------ ---------- ----------
5.2 TEMPORARY TEMP 5 2 78419 15683

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
CURRENT 1
ACTIVE 2
ACTIVE 3

重啟例項之後發現排序使用的臨時段被刪除了，這種表空間的特點是臨時段被重建之後可以一直被重用，直到例項重啟之後臨時段被刪除。

SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 71304548 bytes
Database Buffers 92274688 bytes
Redo Buffers 2945024 bytes
資料庫裝載完畢。
資料庫已經開啟。

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未選定行

3）Temporary Tablespaces

使用"Tablespaces of Type TEMPORARY"這種型別的臨時表空間產生的temp segment的缺點是除了dmt在分配和回收extent所存在的ST-equence問題之外，另一個顯而易見的缺點是在例項重啟之後temp segment也被drop掉了，下次使用temp segment時還需要建立，也就是說temp segment也沒有被很好的重複利用。在lmt出現之後，oracle引入了lmt管理方式的臨時表空間，它的建立語法隨之也發生了變化：

SQL> create temporary tablespace tmp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp01.dbf' size 10m autoextend on;

表空間已建立。

從建立語法上看首先明確指出了表空間的型別是temporary ；其次引入了tempfile，不再是datafile了：

SQL> select tablespace_name,contents , logging,extent_management,segment_space_m
anagement,allocation_type from dba_tablespaces where tablespace_name like 'T%';

TABLESPACE CONTENTS LOGGING EXTENT_MAN SEGMEN ALLOCATIO
---------- --------- --------- ---------- ------ ---------
TEMP TEMPORARY LOGGING DICTIONARY MANUAL USER
TMP TEMPORARY NOLOGGING LOCAL MANUAL UNIFORM

還有一個需要說明的地方是在lmt下建立的這種型別的臨時表空間分配extent時只能是uniform而不能是我們熟悉的autoallocate，default的建立語法是uniform，而且size 是1m。而另外兩個系統用的表空間system,undo只能是autoallocate。

SQL> create temporary tablespace tmp1 tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp02.dbf' size 10m autoextend on uniform size 2m;

表空間已建立。

SQL> create temporary tablespace tmp2 tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp03.dbf' size 10m autoextend on autoallocate;
create temporary tablespace tmp2 tempfile 'E:ORACLEPRODUCT10.2.0ORADATADMT
tmp03.dbf' size 10m autoextend on autoallocate

*
第 1 行出現錯誤:
ORA-25139: CREATE TEMPORARY TABLESPACE 的選項無效

--設定tmp為default temporary tablespace，doc上提到的temporary

tablespace一旦建立，該表空間中就被建立了一個temp segment，而且該臨時段一直存在並且可以重複利用，例項重啟之後也不會被刪除直到臨時表空間被刪除它也隨之被刪除，但是似乎不能很好的驗證doc的說法，因為在建立tmp表空間之後，透過查詢dba_segments並沒有發現temp segment，而且又做了一個disk sort，依然沒有在dba_segments中發現任何temp segment。oracle透過使用這種臨時表空間所產生的temp segment在dba_segments中似乎體現不出來了？暫時解釋不清，也是我的疑問！

SQL> alter database default temporary tablespace tmp;

資料庫已更改。
SQL> select segment_name, segment_type, tablespace_name , extents , header_file,
header_block from dba_segments where segment_type like upper('%temp%');

未選定行

SQL> col operation_type format a10
SQL> select operation_type,policy,tempseg_size , tablespace from v$sql_workarea_
active;

OPERATION_ POLICY TEMPSEG_SIZE TABLESPACE
---------- ------------ ------------ ------------------------------
SORT (v2) AUTO 13631488 TMP

SQL>

使用這種臨時表空間的好處是消除了dmt方式下透過不斷的建立、回收temp segment而需要分配或者回收extent所產生的ST-equenue,同時也減少了不必要的redo。

3、臨時表空間組

臨時表空間組是10g引入的概念，目的是提高同一使用者併發session對臨時段的請求。我們知道一個臨時表空間中只存在一個臨時段，當一個session在使用臨時段時，其他session再請求臨時段時需要等到擁有該臨時段的session使用完畢之後才能使用，造成這一問題的根源在於一個使用者只能使用一個臨時表空間。而臨時表空間組的出現大大改善了同一使用者併發session對臨時段的爭奪，因為一個臨時表空間組可以包括多了臨時表空間，而使用者的default temporary tablespace又可以是臨時表空間組。其實更直接的說就是臨時表空間組的出現使使用者能夠使用多個臨時表空間了。下面做個簡單的測試：

SQL> create temporary tablespace temp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtemp01.dbf' size 10m autoextend on;

表空間已建立。
SQL> create temporary tablespace tmp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp01.dbf' size 10m reuse autoextend on;

表空間已建立。

SQL> select * from dba_tablespace_groups;

未選定行

SQL> alter tablespace temp tablespace group gp1;

表空間已更改。

SQL> alter tablespace tmp tablespace group gp1;

表空間已更改。

SQL> alter database default temporary tablespace gp1;

資料庫已更改。

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP

SQL> alter user xys temporary tablespace gp1;

使用者已更改。

SQL> connect
已連線。
SQL> create table t as select * from dba_objects;

表已建立。

SQL> insert into t select * from t;

已建立11260行。

SQL> insert into t select * from t;

已建立22520行。

SQL> insert into t select * from t;

已建立45040行。

SQL> commit;

提交完成。

SQL> create table tt as select * from t;

表已建立。

分別開啟兩個plsql developer以使用者xys登入對錶t和tt同時進行排序，之後透過如下查詢監視對臨時表空間的使用情況，發現來自同一使用者xys的不同session同時排序時使用了同一臨時表空間組內的不同臨時表空間，這樣大大減少了之前同一使用者只能使用一個臨時表空間而產生的請求臨時段的等待時間：
SQL> select operation_type ,sql_id , tablespace,tempseg_size,number_passes from
v$sql_workarea_active;

OPERATION_ SQL_ID TABLESPACE TEMPSEG_SIZE NUMBER_PASSES
---------- ------------- ---------- ------------ -------------
SORT (v2) 3n26jhzw1fvr1 TEMP 10485760 1
SORT (v2) 7z5ttxyv6c604 TMP 10485760 1

SQL> select sql_text from v$sql where sql_id in (select sql_id from v$sql_workar
ea_active);

SQL_TEXT
--------------------------------------------------------------------------------

select * from t order by object_name desc
select * from t order by object_id desc

另外需要說明的是臨時表空間組本身不能建立，只要把存在的臨時表空間加入按照命名規則命名的臨時表空間組中就可以了，臨時表空間組隨之被建立，就象上面操作的那樣：

SQL> alter tablespace temp tablespace group gp1;

表空間已更改。

SQL> alter tablespace tmp tablespace group gp1;

表空間已更改。

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP

gp1隨著temp和tmp的加入被建立隨著temp和tmp的脫離而被刪除：

SQL> alter tablespace temp tablespace group '';

表空間已更改。

SQL> alter tablespace tmp tablespace group '';

表空間已更改。

SQL> select * from dba_tablespace_groups;

未選定行

SQL>

4、pga

5、sort_area_size