臨時表空間的空間使用情況查詢
以下轉自：http://www.itpub.net/thread-1481844-1-1.html 作者：kramer2009
|
這兩天把臨時表空間的知識點總結了一下。記下來分享一下。 首先從資料字典檢視說起 v$dba_temp_files v$sort_segment v$tempseg_usage dba_temp_free_space 上面這幾個檢視是我總結的在遇到Oracle臨時空間問題時能用到的資料字典資訊。 先看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，如下圖所示，22784-22656=128. SQL> select file_name, blocks , user_blocks from dba_temp_files; FILE_NAME BLOCKS USER_BLOCKS -------------------------------------------------------------------------------- -------------------- ----------- /u01/app/oracle/oradata/sales/temp01.dbf 22784 22656 /u01/app/oracle/oradata/sales/temp02.dbf 6400 6272 再看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 22656 2 256 2 再看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會引起歧義，會讓人以為這個檢視針對的是排序的開銷，但實際上不僅僅是排序，只要是對臨時表空間的消耗都會體現在這個檢視中。 dba_temp_free_space SQL> desc dba_temp_free_space Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) TABLESPACE_SIZE NUMBER ALLOCATED_SPACE NUMBER FREE_SPACE NUMBER 該檢視要注意的是ALLOCATED_SPACE 和 FREE_SPACE。前面兩個比較簡單，tablespace_name不用說了，而 tablespace_size 體現的是整個表空間的大小。如果把前面dba_temp_files 中BYTES列的值加起來，就是這個tablespace_size的值。 至於ALLOCATED_SPACE則要注意了，他體現的是分配出去的臨時空間，包括分配出去但仍然可用的以及分配出去但不可用的。我們通過下面四個語句來理解這個檢視。 SQL> select * from dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ -------------------------------------- ---------------------- ---------- TEMP 239075328 187695104 234881024 SQL> select file_name, blocks , user_blocks from dba_temp_files; FILE_NAME BLOCKS USER_BLOCKS -------------------------------------------------------------------------------- -------------------- ----------- /u01/app/oracle/oradata/sales/temp01.dbf 22784 22656 /u01/app/oracle/oradata/sales/temp02.dbf 6400 6272 SQL> select TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ，CURRENT_USERS from v$sort_segment; TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS CURRENT_USERS ----------------------- ---------------------- ----------- --------------------- 22656 2 256 2 對於tablespace_name沒什麼好說的。 對於tablespace_size，我們理解為是臨時表空間的總大小。驗證一下。 通過dba_temp_files檢視，我們知道了每個temp檔案的資料塊個數，22784和6400. 那麼總的大小是資料塊個數之和乘以資料塊的大小（本系統中是8192）。 （22784 + 6400） * 8192 = 239075328。 驗證成功。 對於ALLOCATED_SPACE我們理解為分配出去的空間。 驗證一下。 v$sort_segment中查到total_block 為22656. 而我們知道temp檔案每個檔案都被系統佔用了128個block（從dba_temp_file 檢視的 block ， user_block對比可以看出）。那麼被佔用的總的資料塊個數應該是128 *2 （兩個臨時檔案所以乘以2） 加上sort_segment中的22656. 128*2 + 22656=22912。 這是資料塊的個數，如果要得到BYTE為單位的大小還要乘以8192（資料塊尺寸）， 結果正好為ALLOCATED_SPACE。驗證成功。不過要注意的很重要的一點是，這裡雖然是allocate的空間但並非是不可用的。其中包含了一部分仍然可用的。比如sort_segment這個檢視中沒分配的extent。 對於FREE_SPACE，也許你會認為直接用 total size 減去 allocate的space就可以了。但如果這樣，那麼這列存在的意義就不大了。實際上這一列顯示的是 臨時空間中可用的空間。可用的意思是如果有排序操作或者說臨時表操作需要，我們仍然可以提供這麼大的空間。這一部分不是 total 與 allocate的差，實際上它應該是 total 減去 allocate 再加上allocate中可用的那部分。 驗證一下 ，total 減去 allocate 為239075328 - 187695104 = 51380224。而 allocate中可用部分的大小我們可以從v$sort_segment檢視中看出。可以看到僅僅使用了256個block。那麼剩餘的空間應該是v$sort_segment的總大小減去256 ，為22656-256 = 22400. 這裡只是資料塊的個數，要得到大小還要乘以8192 得 183500800。用這個數字加上之前的51380224正好為 234881024。 |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1123506/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle查詢表空間的空間佔用情況Oracle
- 查詢表空間使用情況
- Oracle查詢表空間使用情況Oracle
- ORACLE查詢所有表空間使用情況Oracle
- 查詢表空間使用情況的指令碼指令碼
- oracle 表空間，臨時表空間使用率查詢Oracle
- 查詢表的大小及表空間的使用情況
- 表空間使用情況查詢慢的處理
- Oracle查詢表空間使用情況（經典篇）Oracle
- 查詢表空間使用情況的簡單檢視
- 臨時表空間和回滾表空間使用率查詢
- Oracle下查詢臨時表空間佔用率Oracle
- ORCLE中ASM磁碟空間使用情況查詢ASM
- Oracle 查詢各表空間使用情況--完善篇Oracle
- 臨時表空間被佔滿的原因查詢
- CentOS 系統的磁碟空間佔用情況查詢CentOS
- oracle表及表空間使用情況Oracle
- 臨時表空間的增刪改查
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle undo 表空間使用情況分析Oracle
- 檢視oracle表空間使用情況Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- oracle的臨時表空間Oracle
- 臨時表空間的建立、刪除，設定預設臨時表空間
- oracle10g表空間使用情況快速查詢Oracle
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查：Oracle
- oracle臨時表空間的增刪改查Oracle
- 檢視Oracle的表空間的使用情況Oracle
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- sql檢視所有表空間使用情況SQL
- 檢視SQL SERVER表的空間使用情況SQLServer
- oracle表空間查詢Oracle
- 表空間大小查詢
- 表空間查詢資訊