【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
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 臨時表空間和回滾表空間使用率查詢
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- oracle臨時表空間相關Oracle
- 4.2.1.8規劃臨時表空間
- 刪除臨時表空間組
- MySQL InnoDB臨時表空間配置MySql
- 消除臨時表空間暴漲的方法
- Oracle10g的回收站(recyclebin)和自由空間管理Oracle
- 2.5.7 建立預設臨時表空間
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- oracle 臨時表空間的增刪改查Oracle
- SQLServer如何釋放tempdb臨時表空間SQLServer
- 臨時表空間被佔滿的原因查詢
- 資料庫優化之臨時表優化資料庫優化
- MYSQL造資料佔用臨時表空間MySql
- 檢視oracle臨時表空間佔用率的檢視Oracle
- 臨時表空間ORA-1652問題解決
- Oracle 查詢佔用臨時表空間大的歷史會話和SQLOracle會話SQL
- oracle建立使用者，表空間，臨時表空間，分配許可權步驟詳解Oracle
- 表空間和資料檔案的管理
- 臨時表空間使用率過高的解決辦法
- 查詢過去一段時間內某條sql使用的臨時表空間大小SQL
- 12C關於CDB、PDB 臨時temp表空間的總結
- Oracle表空間的管理方式（LMT、DMT）--本地和字典管理Oracle
- Oracle 12C RMAN備份佔用大量臨時表空間Oracle
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- 時間和空間的完美統一！阿里雲時空資料庫正式商業化阿里資料庫
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- 乾貨分享｜優炫資料庫管理之表空間資料庫
- Jenkins臨時空間不足處理辦法Jenkins
- openGauss中如何管理表空間
- Oracle OCP(49)：表空間管理Oracle
- 坑系列 — 時間和空間的平衡
- 硬碟空間的管理和分割槽硬碟
- [待整理]oracle10g刪除(釋放)資料檔案/表空間流程Oracle
- 劍指offer-Go版實現 第五章：優化時間和空間效率Go優化