ORACLE臨時表空間使用率過高的原因及解決方法
轉載地址：
在資料庫的日常學習中，發現資料庫的預設臨時表空間temp使用情況達到了30G，使用率達到了100%；待調整為32G後，使用率還是為100%，導致磁碟空間使用緊張。根據臨時表空間的主要是對臨時資料進行排序和快取臨時資料等特性，待重啟資料庫後，
temp會自動釋放。於是想透過重啟資料庫的方式來緩解這種情況，但是重啟資料庫之後，發現臨時表空間temp的使用率還是100%，一點沒變。雖然執行中應用暫時沒有報什麼錯誤，但是這在一定程度上存在一定的隱患，有待解決該問題。由於臨時表空間主要使用在以下幾種情況：
1、order by or group by (disc sort佔主要部分)；
2、索引的建立和重建立；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些異常也會引起TEMP的暴漲。
Oracle臨時表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的：
1.
沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。
針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。
總結：
1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自定義查詢的軟體中。
3.仔細規劃表索引。如果臨時表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是
permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。檢視有哪些使用者和SQL導致TEMP增長的兩個重要檢視：v$
sort_usage和v$sort_segment。
透過查詢相關的資料，發現解決方案有如下幾種：
一、重建臨時表空間temp
Temporary tablespace是不能直接drop預設的臨時表空間的，不過我們可以透過以下方法達到。
檢視目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\ORCL\TEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
1.建立中轉臨時表空間
create temporary tablespace TEMP1 TEMPFILE
'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND
ON NEXT 1M
MAXSIZE UNLIMITED;
2.改變預設臨時表空間為剛剛建立的新臨時表空間temp1
alter database default temporary tablespace temp1;
3.刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
4.重新建立臨時表空間
create temporary tablespace TEMP TEMPFILE
'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND
ON NEXT 1M MAXSIZE
UNLIMITED;
5.重置預設臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
6.刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
以上的方法只是暫時釋放了臨時表空間的磁碟佔用空間，是治標但不是治本的方法，真正的治本的方法是找出資料庫中消耗資源比較大的sql語句，然後對其進行最佳化處理。下面是查詢在sort排序區使用的執行耗時的SQL：
Select
se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as
Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and
s.hash_value=su.sqlhash and s.address=su.sqladdr order by
se.username,se.sid;
或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID;
注：如果原臨時表空間無使用者使用(select
tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks
from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳。
我們可以刪除該表空間：如果原臨時表空間還有使用者在使用，你是刪除不了這個表空間的！在一次生產環境的臨時表空間切換中，原臨時表空間始終有使用者在上面，即使我關閉了前臺程式，也還是有使用者，新的臨時表空間已經沒有使用者在使用了。我估計使用者程式已經死在原臨時表空間了，後來只有重新啟動資料庫才能把原來舊的臨時表空間給刪除。
二、修改引數（這個方案緊適用於8i及8i以下的版本）
修改一下TEMP表空間的storage引數，讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
三、Kill session
1、使用如下語句a檢視一下認誰在用臨時段
SELECT se.username, se.SID, se.serial#, se.sql_address,
se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS
from
v$session se, v$sort_usage su WHERE se.saddr = su.session_addr
2、kill正在使用臨時段的程式
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空間回縮一下
SQL>Alter tablespace TEMP coalesce;
注：這處方法只能針對字典管理表空間(Dictionary Managed
Tablespace)。於本地管理表空間（LMT:Local Managed
Tablespace），不需要整理的。9i以後只能建立本地管理的表空間。
CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT
MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL;
四、使用診斷事件，也是相對有效的一種方法
1、查詢事件程式碼
SQL>select ts#, name from sys.ts$ ;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDOTBS2
2、執行清理操作
SQL>alter session set events 'immediate trace name
DROP_SEGMENTS level 4';
說明：temp表空間的TS# 為 3, So TS#+ 1= 4。
oracle臨時表空間過大的原因
Oracle
臨時表空間主要是用來做查詢和存放一些快取的資料的，磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁碟空間的（記憶體）的分配上，Oracle使用的是貪心演算法，如果上次磁碟空間消耗達到1GB，那麼臨時表空間就是1GB，如果還有增長，那麼依此類推，臨時表空間始終保持在一個最大的上限。Oracle臨時表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的。
1.
沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。
針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。
總結：
1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自定義查詢的軟體中。
3.仔細規劃表索引。
如果臨時表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。檢視有哪些使用者和SQL導致TEMP增長的兩個重要檢視：
v$sort_usage和v$sort_segment
對於非LMT管理方式的TEMP表空間，最簡單的方法是Metalink給出的一個方法：
修改一下TEMP表空間的storage引數，讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp default storage(pctincrease 1);
SQL>alter tablespace temp default storage(pctincrease 0);
而對於LMT管理方式的TEMP表空間，需要重新建立一個新的臨時表空間，將所有使用者的預設臨時表空間指定到新的表空間上，然後offline舊的臨時表空間，並drop掉。具體步驟如下：
首先查詢使用者的預設臨時表空間:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12
11:11:43 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP2
SYSTEM TEMP2
OUTLN TEMP2
EYGLE TEMP2
CSMIG TEMP2
TEST TEMP2
REPADMIN TEMP2
......
13 rows selected.
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------------
/opt/oracle/oradata/conner/temp02.dbf
/opt/oracle/oradata/conner/temp03.dbf
重建新的臨時表空間並進行切換:
SQL> create temporary tablespace temp tempfile
'/opt/oracle/oradata/conner/temp1.dbf' size 10M;
Tablespace created.
SQL> alter tablespace temp add tempfile
'/opt/oracle/oradata/conner/temp2.dbf' size 20M;
Tablespace altered.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
SYSTEM TEMP
OUTLN TEMP
EYGLE TEMP
CSMIG TEMP
TEST TEMP
REPADMIN TEMP
.......
13 rows selected.
如果原臨時表空間無使用者使用(select
tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks
from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳。
我們可以刪除該表空間:(如果原臨時表空間還有使用者在用，你是刪除不了這個表空間的！在一次生產環境的臨時表空間切換中，原臨時表空間始終有使用者在上面，即使我關閉了前臺程式，也還是有使用者，新的臨時表空間已經沒有使用者在使用了。我估計使用者程式已經死在原臨時表空間了。後來只有重新啟動資料庫才能把原來舊的臨時表空間給刪除。）
SQL> drop tablespace temp2;
Tablespace dropped.
SQL>
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------
/opt/oracle/oradata/conner/temp1.dbf
/opt/oracle/oradata/conner/temp2.dbf
SQL> select file_name,tablespace_name,bytes/1024/1024
MB,autoextensible
2 from dba_temp_files
3 /
FILE_NAME TABLESPACE_NAME
MB AUTOEXTENSIBLE
-------------------------------------- --------------------
---------- --------------
/opt/oracle/oradata/conner/temp2.dbf TEMP
20 NO
/opt/oracle/oradata/conner/temp1.dbf TEMP
10 NO
drop tablespace temp including contents and datafiles;
--將表空間的內容和資料檔案一起刪除。
下面是查詢在sort排序區使用的執行耗時的SQL:
Select
se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as
Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and
s.hash_value=su.sqlhash and s.address=su.sqladdr
order by se.username,se.sid
或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID
在資料庫的日常學習中，發現資料庫的預設臨時表空間temp使用情況達到了30G，使用率達到了100%；待調整為32G後，使用率還是為100%，導致磁碟空間使用緊張。根據臨時表空間的主要是對臨時資料進行排序和快取臨時資料等特性，待重啟資料庫後，
temp會自動釋放。於是想透過重啟資料庫的方式來緩解這種情況，但是重啟資料庫之後，發現臨時表空間temp的使用率還是100%，一點沒變。雖然執行中應用暫時沒有報什麼錯誤，但是這在一定程度上存在一定的隱患，有待解決該問題。由於臨時表空間主要使用在以下幾種情況：
1、order by or group by (disc sort佔主要部分)；
2、索引的建立和重建立；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些異常也會引起TEMP的暴漲。
Oracle臨時表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的：
1.
沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。
針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。
總結：
1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自定義查詢的軟體中。
3.仔細規劃表索引。如果臨時表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是
permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。檢視有哪些使用者和SQL導致TEMP增長的兩個重要檢視：v$
sort_usage和v$sort_segment。
透過查詢相關的資料，發現解決方案有如下幾種：
一、重建臨時表空間temp
Temporary tablespace是不能直接drop預設的臨時表空間的，不過我們可以透過以下方法達到。
檢視目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\ORCL\TEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
1.建立中轉臨時表空間
create temporary tablespace TEMP1 TEMPFILE
'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND
ON NEXT 1M
MAXSIZE UNLIMITED;
2.改變預設臨時表空間為剛剛建立的新臨時表空間temp1
alter database default temporary tablespace temp1;
3.刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
4.重新建立臨時表空間
create temporary tablespace TEMP TEMPFILE
'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND
ON NEXT 1M MAXSIZE
UNLIMITED;
5.重置預設臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
6.刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
以上的方法只是暫時釋放了臨時表空間的磁碟佔用空間，是治標但不是治本的方法，真正的治本的方法是找出資料庫中消耗資源比較大的sql語句，然後對其進行最佳化處理。下面是查詢在sort排序區使用的執行耗時的SQL：
Select
se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as
Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and
s.hash_value=su.sqlhash and s.address=su.sqladdr order by
se.username,se.sid;
或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID;
注：如果原臨時表空間無使用者使用(select
tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks
from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳。
我們可以刪除該表空間：如果原臨時表空間還有使用者在使用，你是刪除不了這個表空間的！在一次生產環境的臨時表空間切換中，原臨時表空間始終有使用者在上面，即使我關閉了前臺程式，也還是有使用者，新的臨時表空間已經沒有使用者在使用了。我估計使用者程式已經死在原臨時表空間了，後來只有重新啟動資料庫才能把原來舊的臨時表空間給刪除。
二、修改引數（這個方案緊適用於8i及8i以下的版本）
修改一下TEMP表空間的storage引數，讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
三、Kill session
1、使用如下語句a檢視一下認誰在用臨時段
SELECT se.username, se.SID, se.serial#, se.sql_address,
se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS
from
v$session se, v$sort_usage su WHERE se.saddr = su.session_addr
2、kill正在使用臨時段的程式
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空間回縮一下
SQL>Alter tablespace TEMP coalesce;
注：這處方法只能針對字典管理表空間(Dictionary Managed
Tablespace)。於本地管理表空間（LMT:Local Managed
Tablespace），不需要整理的。9i以後只能建立本地管理的表空間。
CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT
MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL;
四、使用診斷事件，也是相對有效的一種方法
1、查詢事件程式碼
SQL>select ts#, name from sys.ts$ ;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDOTBS2
2、執行清理操作
SQL>alter session set events 'immediate trace name
DROP_SEGMENTS level 4';
說明：temp表空間的TS# 為 3, So TS#+ 1= 4。
oracle臨時表空間過大的原因
Oracle
臨時表空間主要是用來做查詢和存放一些快取的資料的，磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁碟空間的（記憶體）的分配上，Oracle使用的是貪心演算法，如果上次磁碟空間消耗達到1GB，那麼臨時表空間就是1GB，如果還有增長，那麼依此類推，臨時表空間始終保持在一個最大的上限。Oracle臨時表空間暴漲的現象經過分析可能是以下幾個方面的原因造成的。
1.
沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。
針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。
總結：
1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自定義查詢的軟體中。
3.仔細規劃表索引。
如果臨時表空間是temporary的，空間不會釋放，只是在sort結束後被標記為free的，如果是permanent的，由SMON負責在sort結束後釋放，都不用去手工釋放的。檢視有哪些使用者和SQL導致TEMP增長的兩個重要檢視：
v$sort_usage和v$sort_segment
對於非LMT管理方式的TEMP表空間，最簡單的方法是Metalink給出的一個方法：
修改一下TEMP表空間的storage引數，讓Smon程式觀注一下臨時段，從而達到清理和TEMP表空間的目的。
SQL>alter tablespace temp default storage(pctincrease 1);
SQL>alter tablespace temp default storage(pctincrease 0);
而對於LMT管理方式的TEMP表空間，需要重新建立一個新的臨時表空間，將所有使用者的預設臨時表空間指定到新的表空間上，然後offline舊的臨時表空間，並drop掉。具體步驟如下：
首先查詢使用者的預設臨時表空間:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12
11:11:43 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP2
SYSTEM TEMP2
OUTLN TEMP2
EYGLE TEMP2
CSMIG TEMP2
TEST TEMP2
REPADMIN TEMP2
......
13 rows selected.
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------------
/opt/oracle/oradata/conner/temp02.dbf
/opt/oracle/oradata/conner/temp03.dbf
重建新的臨時表空間並進行切換:
SQL> create temporary tablespace temp tempfile
'/opt/oracle/oradata/conner/temp1.dbf' size 10M;
Tablespace created.
SQL> alter tablespace temp add tempfile
'/opt/oracle/oradata/conner/temp2.dbf' size 20M;
Tablespace altered.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
SYSTEM TEMP
OUTLN TEMP
EYGLE TEMP
CSMIG TEMP
TEST TEMP
REPADMIN TEMP
.......
13 rows selected.
如果原臨時表空間無使用者使用(select
tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks
from v$sort_segment;),如果是檔案系統可以看看檔案的時間戳。
我們可以刪除該表空間:(如果原臨時表空間還有使用者在用，你是刪除不了這個表空間的！在一次生產環境的臨時表空間切換中，原臨時表空間始終有使用者在上面，即使我關閉了前臺程式，也還是有使用者，新的臨時表空間已經沒有使用者在使用了。我估計使用者程式已經死在原臨時表空間了。後來只有重新啟動資料庫才能把原來舊的臨時表空間給刪除。）
SQL> drop tablespace temp2;
Tablespace dropped.
SQL>
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------
/opt/oracle/oradata/conner/temp1.dbf
/opt/oracle/oradata/conner/temp2.dbf
SQL> select file_name,tablespace_name,bytes/1024/1024
MB,autoextensible
2 from dba_temp_files
3 /
FILE_NAME TABLESPACE_NAME
MB AUTOEXTENSIBLE
-------------------------------------- --------------------
---------- --------------
/opt/oracle/oradata/conner/temp2.dbf TEMP
20 NO
/opt/oracle/oradata/conner/temp1.dbf TEMP
10 NO
drop tablespace temp including contents and datafiles;
--將表空間的內容和資料檔案一起刪除。
下面是查詢在sort排序區使用的執行耗時的SQL:
Select
se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as
Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and
s.hash_value=su.sqlhash and s.address=su.sqladdr
order by se.username,se.sid
或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28371090/viewspace-1184444/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 臨時表空間使用率過高的原因及解決方案Oracle
- 臨時表空間使用率過高的解決辦法
- 臨時表空間過大的解決方法
- ORACLE 臨時表空間滿了的原因解決方案Oracle
- ORACLE 臨時表空間使用率過高分析Oracle
- oracle臨時表空間過大的原因&&處理Oracle
- oracle 表空間，臨時表空間使用率查詢Oracle
- undo表空間使用率過高解決
- oracle的臨時表空間使用率99.9%Oracle
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 轉：Oracle 臨時表空間過大問題解決Oracle
- 臨時表空間已滿的解決方法
- 【實驗】RESIZE方法解決臨時表空間過大問題
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- oracle的臨時表空間Oracle
- 解決Oracle臨時表空間佔滿的問題Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- oracle的臨時表空間解決問題的步驟Oracle
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- 臨時表空間和回滾表空間使用率查詢
- Oracle 臨時表空間的概念Oracle
- ORACLE臨時表空間的清理Oracle
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- Oracle Temp 臨時表空間Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- ORA-01114錯誤原因及解決方法（臨時表空間壞掉、或者滿了）
- 【TEMP】臨時表空間的工作原理及維護方法
- oracle 11g sysaux表空間使用率非常高的問題解決OracleUX
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- oracle臨時表空間相關Oracle
- Oracle TEMP臨時表空間概念Oracle
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle