一、臨時表空間簡介

在執(zhí)行SQL時，經常會遇到排序操作，當結果集無法放在內存中時，Oracle就會使用臨時表空間來排序。臨時表空間中不能創(chuàng)建持久性對象，用戶唯一能創(chuàng)建的就是臨時表，而且隨著用戶會話退出，臨時表也會被刪除。

當Oracle安裝完成時，默認就已經創(chuàng)建了1個臨時表空間TEMP，且所有未顯式指定使用其他臨時表空間的用戶，都會使用這個臨時表空間，使用下面的SQL可以查詢數據庫的默認臨時表空間：

select property_name, property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';

臨時表空間的底層使用的是臨時文件（tempfile），通常采用本地管理策略（Locally Management），臨時表空間不會生成redo日志，根據其服務的實例數量還可以分為：

• 本地臨時表空間，通常保存在本地磁盤，只能給一個實例訪問
• 共享臨時表空間：通常保存在共享存儲上，可以被多個實例同時訪問

二、臨時表空間管理

雖然Oracle初始已經建立了一個臨時表空間，但用戶也可以根據自身需求對臨時表空間進行定制。

2.1 創(chuàng)建臨時表空間

使用create temporary tablespace語句創(chuàng)建臨時表空間，語法和創(chuàng)建普通表空間類似，不同點在于其需要指定temporary和tempfile關鍵字。

示例：創(chuàng)建一個臨時表空間temptbs01，文件大小20M，reuse關鍵字指示如果文件已存在則重用：

create temporary tablespace temptbs01 
tempfile '/u01/app/oracle/oradata/PROD/temptbs01.dbf' size 20m reuse;

如果開啟了OMF特性，可以不指定文件屬性，只提供表空間名稱即可：

create temporary tablespace temptbs02;

創(chuàng)建好的臨時表空間及其臨時文件信息可以通過dba_temp_files查看：

select tablespace_name, file_name,status, autoextensible from dba_temp_files;

2.2 修改臨時表空間

可以用alter tablespace語句修改表空間屬性，例如添加，刪除臨時文件，修改在線/離線狀態(tài)，調整臨時文件大小等。

示例：為temptbs01添加一個數據文件，大小10M，自動擴展：

alter tablespace temptbs01 
add tempfile '/u01/app/oracle/oradata/PROD/temptbs01_2.dbf' size 10M 
autoextend on next 10m; 

示例：將上面添加的數據文件刪除：

alter tablespace temptbs01 
drop tempfile '/u01/app/oracle/oradata/PROD/temptbs01_2.dbf';

示例：修改temptbs01臨時文件的在線/離線狀態(tài)（不能修改臨時表空間的在線/離線狀態(tài)）：

alter database tempfile '/u01/app/oracle/oradata/PROD/temptbs01.dbf' offline;
alter database tempfile '/u01/app/oracle/oradata/PROD/temptbs01.dbf' online;

示例：修改臨時文件的大小，將temptbs01的臨時文件修改為30m:

alter database tempfile '/u01/app/oracle/oradata/PROD/temptbs01.dbf' resize 30m;

2.3 查看空間使用情況

查詢dba_temp_free_space視圖可以查看當前臨時表空間的使用情況，free_space字段指示了可用空間：

select * from dba_temp_free_space;

2.4 收縮臨時表空間

對于文件可以自動擴展的臨時表空間，當空間不夠時，Oracle會自動擴展文件大小，一個很大的任務，就可以導致臨時表空間消耗很多磁盤。如果日常用不到這么大的表空間，可以手動收縮以回收磁盤空間。

回收表空間是一個在線操作，正在使用的會話可以正常分配空間，不受影響。

示例：用alter tablespace … shrink space …; 可以回收可用空間，keep子句指示盡量收縮到25m：

alter tablespace temptbs01 shrink space keep 25m;

或：用alter tablespace … shrink tempfile …; 指定收縮某個臨時文件：

alter tablespace temptbs01 shrink tempfile '/u01/app/oracle/oradata/PROD/temptbs01.dbf' keep 20m;

2.5 刪除臨時表空間

使用alter tablespace … drop…;語句可以刪除臨時表空間，你可以選擇是否保留臨時文件，如果保留臨時文件，下次再次指定同名文件時需要用reuse關鍵字重用文件。

示例：刪除臨時表空間temptbs01及其臨時文件，省略including contents and datafiles子句則會保留臨時文件：

drop tablespace temptbs01 including contents and datafiles;

三、使用臨時表空間組

臨時表空間組是一個邏輯概念，它由1或多個臨時表空間組成，可以作為一個整體分配給數據庫或用戶使用。在高并發(fā)環(huán)境，多個臨時表空間可以更好的減少爭用現象，并且Oracle的并行執(zhí)行特性也可以利用多個臨時表空間提升執(zhí)行性能。

臨時表空間組不需要顯式創(chuàng)建，只需要使用alter tablespace … tablespace group …; 將某個臨時表空間加入組即可（創(chuàng)建表空間時也可加入）。

第一步，將temptbs01加入組group1，這會隱式創(chuàng)建group1：

alter tablespace temptbs01 tablespace group group1;

第二步（可選），可以繼續(xù)將其他臨時表空間加入組，組成員的數量沒有限制：

alter tablespace temptbs02 tablespace group group1;

通過dba_tablespace_groups可以看到目前group1中已經有2個表空間：

select * from dba_tablespace_groups;

第三步：將組指定為數據庫默認臨時表空間（alter database）或指定給用戶（alter user）：

alter database default temporary tablespace group1;
alter user hr temporary tablespace group1;

使用alter database指定空的組名可以將臨時表空間移出組，當最后一個表空間移出組時，組自動刪除（先取消引用）：

alter tablespace temptbs01 tablespace group '';

以上就是Oracle臨時表空間管理和優(yōu)化操作的詳細內容，更多關于Oracle臨時表空間的資料請關注腳本之家其它相關文章！