【Database】Oracle10g臨時表空間的管理和優化
v$dba_temp_files
v$sort_segment
v$tempseg_usage=v$sort_usage
V$TEMP_SPACE_HEADER
上面這幾個檢視是我總結的在遇到Oracle臨時空間問題時能用到的資料字典資訊。
1,先看v$dba_temp_files
這個檢視顯示了資料庫中臨時檔案的具體資訊。要注意的有4列
SQL> desc dba_temp_files;
Name Type
Nullable Default Comments
--------------- ------------- -------- -------
---------------------------------------------------
FILE_NAME VARCHAR2(513) Y
Name of the database temp file
FILE_ID NUMBER
Y
ID of the database temp file
TABLESPACE_NAME VARCHAR2(30)
Name of the tablespace to which the file belongs
BYTES
NUMBER Y
Size of the file in
bytes
BLOCKS
NUMBER Y
Size of the file in ORACLE blocks
STATUS VARCHAR2(7) Y
File status:
"AVAILABLE"
RELATIVE_FNO NUMBER Y
Tablespace-relative
file number
AUTOEXTENSIBLE VARCHAR2(3) Y
Autoextensible
indicator: "YES" or "NO"
MAXBYTES NUMBER
Y
Maximum size of the file in bytes
MAXBLOCKS NUMBER
Y
Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y
Default increment
for autoextension
USER_BYTES NUMBER
Y
Size of the useful portion of file in bytes
USER_BLOCKS NUMBER
Y
Size of the useful portion of file in ORACLE blocks
這四列中， BYTES , BLOCKS 顯示的是臨時檔案有多少BYTE大小，包含多少個資料塊。而USER_BYTES,USER_BLOCKS是可用的BYTE和資料塊個數。因此，我們可以知道臨時檔案中有一部分是被系統佔用的，大概可以理解成檔案頭資訊，在我的db中，這一部分大小是128個block，如下圖所示，3243776-3243648=128. 4194302-4194176=126
SQL> select file_name, blocks ,
user_blocks from dba_temp_files;
FILE_NAME BLOCKS USER_BLOCKS
------------------------------------------------------------------ --------- -----------------
+TCSF_DG/tcsf2/tempfile/temp.293.720318237 3243776 3243648
+TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 4194302 4194176
2,再看v$sort_segment檢視
這個檢視從字面翻譯雖然叫做排序段，但實際上不僅僅是排序，只要是消耗了臨時表空間的操作，比如建立臨時表等，都會在這個檢視中顯示。但如果一個操作沒有佔用臨時表空間（比如開銷很小的排序，在記憶體中完成，或者一個臨時表在建立時通過tablespace子句指定到了一個永久表空間中),那麼這個檢視就不會被更新。
但有一個問題是，我們指定正常的segment是一個segment用來承載一個物件如表或者index，再或者一個物件的分割槽，如分割槽表的分割槽，但我們的sort_segment似乎不是這樣的，一個sort_segment可以供多個物件或者說操作使用。
如圖，一個sort_segment, 有兩個extent被佔用，同時有兩個user在使用。因為這個測試是我自己進行的，所以我清楚的知道，兩個user 一個在建立臨時表，另一個在建立一個大表的索引。這根本就是兩件不相干的事，但用的是同一個sort_segment.
不過這個問題不重要，因為我們通過這個檢視主要知道多少個使用者在使用臨時表空間，已經使用了多少這就夠了。
SQL> select tablespace_name ,TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS
,CURRENT_USERS from v$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS
------------------------------- ------------ ------------ -----------
CURRENT_USERS
-------------
TEMP 3240832 0 0
0
TCSF_TEMP 2217216 0 0
0
3,再看v$tempseg_usage
這個檢視可以詳細的顯示那些使用者在使用臨時空間，使用了多少，其session的id，SQL的ID等。但有一個很困惑的問題是其中的SQL_ID列顯示的資訊不準確。根據我的試驗，它顯示的應該是目標會話的當前SQL的SQL_ID，而非消耗臨時表空間的SQL_ID。比如說你在一個會話中發出了一條SQL消耗了很大的臨時空間，然後你又發出了一條不相干的SQL，這時如果我select * from v$tempseg_usage，查到的SQL_ID對應的SQL是第二條，也就是最新的那條。
另外一條要注意的是，這個檢視與 v$sort_usage是一樣的，只不過後者改了個名字而已。大概oracle是這麼考慮的，如果叫sort_usage會引起歧義，會讓人以為這個檢視針對的是排序的開銷，但實際上不僅僅是排序，只要是對臨時表空間的消耗都會體現在這個檢視中。
4,V$TEMP_SPACE_HEADER（Oracle10g）
SQL> desc V$TEMP_SPACE_HEADER
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
FILE_ID NUMBER
BYTES_USED NUMBER
BLOCKS_USED NUMBER
BYTES_FREE NUMBER
BLOCKS_FREE NUMBER
RELATIVE_FNO NUMBER
該檢視要注意的是BLOCKS_USED 和 BLOCKS_FREE。前面兩個比較簡單，tablespace_name不用說了，而file_id 體現的是檔案編號。如果把前面dba_temp_files 中blocks的值與BLOCKS_USED+ BLOCKS_FREE比較，剛好相等，這就是這個臨時檔案總塊數。
監控臨時表空間
一,檢視臨時表空間的使用情況(會重用,只有不夠時才又重新請求分配)V$TEMP_SPACE_HEADER
SQL> select * from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
---------------------- ---------- --------------- ---------------- ------------- ---------------- ----------------
TEMP 1 2.6573E+10 3243776 0 0 1
TCSF_TEMP 2 3.4287E+10 4185470 72351744 8832 1
SQL> select
TABLESPACE_NAME,file_id,(bytes_used+bytes_free)/1024/1024 "size_total(mb)",
bytes_used/1024/1024 "size_allocated(mb)",bytes_free/1024/1024 "size_unallocated(mb)",blocks_used,blocks_free,relative_fno
from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID size_total(mb) size_allocated(mb) size_unallocated(mb) BLOCKS_USED BLOCKS_FREE RELATIVE_FNO
-------------------- ---------- -------------- ------------------ -------------------- ----------- ----------- ------------
TEMP 1 25342 25342 0 3243776 0 1
TCSF_TEMP 2 32767.9844 32698.9844 69 4185470 8832 1
SQL> select file_name, blocks , user_blocks from dba_temp_files;
FILE_NAME BLOCKS USER_BLOCKS
------------------------------------------------------------------ --------- -----------------
+TCSF_DG/tcsf2/tempfile/temp.293.720318237 3243776 3243648
+TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 4194302 4194176
二,檢視正在使用的sort_segment,V$SORT_SEGMENT
SQL> select tablespace_name,segment_block,used_blocks,free_blocks,current_users,total_blokcs from v$sort_segment;
TABLESPACE_NAME SEGMENT_BLOCK USED_BLOCKS FREE_BLOCKS CURRENT_USERS TOTAL_BLOCKS
--------------------------- ------------------- ----------------- ---------------- ------------------- -----------------
TEMP 0 0 3240832 0 3240832
TCSF_TEMP 0 0 2217216 0 2217216
對於tablespace_size，我們理解為是臨時表空間的總大小。驗證一下。
通過dba_temp_files檢視，我們知道了temp表空間temp檔案的資料塊個數：3243776，tcsf_temp表空間temp檔案的資料塊個數：4194302. 那麼總的大小是資料塊個數之和乘以資料塊的大小（本系統中是8192）。
3243776 * 8192 = 26573012992。 驗證成功。
對於ALLOCATED_SPACE我們理解為分配出去的空間。
驗證一下。
v$sort_segment中查到total_block 為3240832.
而我們知道temp檔案每個檔案都被系統佔用了128個block（從dba_temp_file 檢視的 blocks ， user_blocks對比可以看出）。那麼被佔用的總的資料塊個數應該是128 加上sort_segment中的3240832. 128 + 3240832=3240960。
這是資料塊的個數，如果要得到BYTE為單位的大小還要乘以8192（資料塊尺寸）。不過要注意的很重要的一點是，這裡雖然是allocate的空間但並非是不可用的。其中包含了一部分仍然可用的。比如sort_segment這個檢視中沒分配的extent。
三,檢視哪個使用者的哪個語句在使用sort_segment,V$SORT_USAGE=v$tempseg_usage
SQL> create global temporary table test(id number,name char(2)) on commit delete rows;
Table created.
SQL> insert into test values(1,'2');
1 row created.
SQL> select * from v$tempseg_usage;
USERNAME USER SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK#
------------------------------- --------- --------- ---------- ----------
EXTENTS BLOCKS SEGRFNO#
---------- ---------- ----------
SYS SYS 070000010F9F0520
39564 070000010D68BE30 2802432354 0f8vysqmhmfb2
TEMP TEMPORARY DATA 201 3234313
1 128 1
SQL> select a.username, a.SESSION_NUM,b.sql_text, a.TABLESPACE
from v$tempseg_usage a, v$sqlarea b
where a.SQLHASH = b.hash_value;
USERNAME SESSION_NUM
------------------------------ -----------
SQL_TEXT
--------------------------------------------------------------------------------
TABLESPACE
-------------------------------
SYS 39564
select * from v$tempseg_usage
TEMP
可以看出找出的sql_text並不是確切使用temprary tablespace的sql語句
方法一、【實驗】RESIZE方法解決臨時表空間過大問題
1.資料庫版本資訊
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0
- Production
2.檢視臨時表空間資訊
SQL> col file_name for a40
SQL> col tablespace_name for a10
SQL> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;
TABLESPACE FILE_NAME M
-------------------------------------------------- -------
TEMP +TCSF_DG/tcsf2/tempfile/temp.293.720318237 25342
TCSF_TEMP +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 32767.9844
3.檢視臨時表空間資料檔案佔用作業系統的空間
p550a:/home/oracle$export
ORACLE_SID=+ASM1
p550a:/home/oracle$asmcmd
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/temp.293.720318237
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y temp.293.720318237
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y tcsf_temp.305.720321581
4.關鍵的一部就在這裡！RESIZE
it！這一步未必修改成功
SQL> alter database tempfile '+TCSF_DG/tcsf2/tempfile/temp.293.720318237' resize 1024m;
Database altered.
5.確認已經修改成功
SQL> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;
TABLESPACE FILE_NAME M
-------------------------------------------------- -------
TEMP +TCSF_DG/tcsf2/tempfile/temp.293.720318237 1024
TCSF_TEMP +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581 32767.9844
p550a:/home/oracle$export
ORACLE_SID=+ASM1
p550a:/home/oracle$asmcmd
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/temp.293.720318237
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y temp.293.720318237
ASMCMD> ls -l +TCSF_DG/tcsf2/tempfile/tcsf_temp.305.720321581
Type Redund Striped Time Sys Name
TEMPFILE UNPROT COARSE APR 12 03:00:00 Y tcsf_temp.305.720321581
6.OK，修改成功。
另外還有一種修改臨時表空間大小的方法，詳見《【實驗】重建臨時表空間解決臨時表空間過大問題》
方法二、【實驗】重建臨時表空間解決臨時表空間過大問題
今天在測試庫中建立大表索引後臨時表空間自動擴充套件到了10G，我採用了重建臨時表空間的方式處理了一下，記錄如下：
1.建立中轉臨時表空間
create temporary
tablespace
temp1 tempfile '/oracle/oradata/secooler/temp02.dbf' size 512m reuse autoextend on next 1m maxsize unlimited;
2.改變預設臨時表空間為剛剛建立的新臨時表空間temp1
alter database default temporary tablespace temp1;
驗證使用者的臨時表空間為temp1
Select username,temporary_tablespace from dba_users;
3.刪除原臨時表空間
drop tablespace temp including contents and datafiles;
4.重建臨時表空間
create temporary tablespace temp tempfile '/oracle/oradata/secooler/temp01.dbf'
size 512m reuse autoextend
on next 1m maxsize unlimited;
5.重置預設臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
驗證使用者的臨時表空間為temp
Select username,temporary_tablespace from dba_users;
6.刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-731942/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能優化：收縮臨時表空間Oracle優化
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 一次HASH JOIN 臨時表空間不足的分析和優化思路優化
- Oracle10g新特新：臨時表空間組Oracle
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- oracle的臨時表空間Oracle
- 臨時表空間的建立、刪除，設定預設臨時表空間
- oracle清理和重建臨時表空間Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案（續）
- Oracle修改預設表空間和預設臨時表空間Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- Oracle 臨時表空間的概念Oracle
- ORACLE臨時表空間的清理Oracle
- 臨時表空間和回滾表空間使用率查詢
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- oracle10G新特性之臨時表空間組的應用Oracle
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- Oracle Temp 臨時表空間Oracle
- Oracle10g新特性：臨時表空間組(temporary tablespace group)Oracle
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 臨時表空間的空間使用情況查詢
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle10g缺少tempfile（臨時表空間)_offline相關表空間測試筆記Oracle筆記
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- oracle 臨時表空間基本常識和操作Oracle
- oracle臨時表空間相關Oracle
- MySQL InnoDB臨時表空間配置MySql
- Oracle TEMP臨時表空間概念Oracle
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle