快捷導(dǎo)航

Oracle中如何查看表空間使用率

更新時間：2023年07月29日 09:36:48 作者：王紹樺

這篇文章主要介紹了Oracle中如何查看表空間使用率問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

Oracle查看表空間使用率

查看用戶默認表空間

SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='DB_USER_NAME';

查看表空間文件路徑、大小、已使用、使用率

SELECT
  B.FILE_NAME 物理文件名,
  B.TABLESPACE_NAME 表空間名稱,
  B.BYTES/1024/1024 大小M,
  (B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,
  SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
ORDER BY B.TABLESPACE_NAME;

查看表空間是否開啟自動擴展

SELECT FILE_NAME AS 數(shù)據(jù)文件,TABLESPACE_NAME AS 表空間名稱,AUTOEXTENSIBLE AS 自動擴展,STATUS AS 狀態(tài),MAXBYTES AS 可擴展最大值,USER_BYTES AS 已使用大小,INCREMENT_BY AS 自動擴展增量 FROM DBA_DATA_FILES;

創(chuàng)建表空間并開啟自動擴展

CREATE SMALLFILE TABLESPACE TABLE_SPACE_NAME DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

修改表空間大小

ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF' RESIZE 200M;

刪除表空間和數(shù)據(jù)文件

DROP TABLESPACE TABLE_SPACE_NAME INCLUDING CONTENTS AND DATAFILES;

查看臨時表空間信息

SELECT * FROM DBA_TEMP_FILES

Oracle臨時表空間使用率

今天開發(fā)反映說臨時表空間不夠要求添加臨時表空間，添加完成（添加了30G的臨時表空間）又報臨時表空間的不足，開發(fā)又要求是添加，有添加了10G，監(jiān)控臨時表空間的使用情況，又撐滿了，又添加了30G,最后臨時表空間又撐滿了，最后批處理還是沒有執(zhí)行，SQL語句的執(zhí)行是一次性全部執(zhí)行完成，最后的解決辦法是開發(fā)使用游標(biāo)，一萬條數(shù)據(jù)一提交或者定量提交，下午監(jiān)控臨時表空間的使用情況發(fā)現(xiàn)使用率 74.91%。不禁有些疑問到底是那些用戶在使用臨時表空間，使用臨時表空間的大小有多大，

在做一些什么操作，SQL語句是什么。

首先要說明一點的是表空間的使用惰性，如果你一下使用幾十個G的臨時表空間，如果SQL語句執(zhí)行失敗，臨時表空間的釋放需要時間，不會一下全部釋放，這個時候我們檢查臨時表空間的使用率發(fā)現(xiàn)使用率已經(jīng)是99%，如果你擴容臨時表空間，擴容30G，開發(fā)又開始執(zhí)行sql，你會發(fā)現(xiàn)臨時表空間使用率蹭蹭的網(wǎng)上增最后又達到了99%，開發(fā)的sql又沒有執(zhí)行過去，還是報臨時表空間不足，而臨時表空間釋放有需要時間，如果開發(fā)的非常著急需要執(zhí)行sql。所以建議dba一次擴容臨時表，擴容大點，

說一下今天處理問題的過程

1.查詢臨時表空間的使用率

select c.tablespace_name,
       to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb,
       to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb,
       to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb,
       to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         GROUP by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;

2.查詢那些用戶在使用

select a.username,
       a.sql_id,
       a.SEGTYPE,
       b.BYTES_USED/1024/1024/1024||'G',
       b.BYTES_FREE/1024/1024/1024  
from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

解釋username 正在執(zhí)行sql的用戶名

sql_id：正在執(zhí)行的sql的的sql_id
segtype：正在執(zhí)行的SQL語句做的是什么操作
BYTES_USED：正在執(zhí)行sql語句使用的臨時表空間的大小
BYTES_FREE：剩余多少臨時表空間

大家可以看到這個臨時表空間的有6個數(shù)據(jù)文件，查詢的結(jié)果顯示按照每個臨時的數(shù)據(jù)文件使用了多少，還剩余多少，又又有一個問題是第一用戶DBSNMP用戶已經(jīng)把臨時表空間占滿了那么第二個用戶還能使用這個六個數(shù)據(jù)文件的臨時表空間么，個人理解是六個數(shù)據(jù)文件中有第一個用戶在使用，也有第二個用戶在使用。

查詢實例中時候是否有大字段在使用臨時表空間：

select * ?from V$TEMPORARY_LOBS;

關(guān)于數(shù)據(jù)字典V$TEMP_SPACE_HEADER官方文檔的解釋：

V$TEMP_SPACE_HEADER 顯示每個LOCALLY MANAGED臨時表空間的每個文件的聚合信息，包括當(dāng)前正在使用的空間量以及空間頭中標(biāo)識的空閑量。

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(30)	Name of the temporary tablespace
FILE_ID	NUMBER	Absolute file number
BYTES_USED	NUMBER	How many bytes are in use
BLOCKS_USED	NUMBER	How many blocks are in use
BYTES_FREE	NUMBER	How many bytes are free
BLOCKS_FREE	NUMBER	How many blocks are free
RELATIVE_FNO	NUMBER	The relative file number for the file

關(guān)于V$TEMPSEG_USAGE的官方文檔的解釋：

V$TEMPSEG_USAGE 描述臨時段使用情況。

柱	數(shù)據(jù)類型	描述
USERNAME	VARCHAR2(30)	請求臨時空間的用戶
USER	VARCHAR2(30)	此列已過時并維護以便向后兼容。此列的值始終等于中的值 USERNAME 。
SESSION_ADDR	RAW(4 \| 8)	會話地址
SESSION_NUM	NUMBER	會話序列號
SQLADDR	RAW(4 \| 8)	SQL語句的地址
SQLHASH	NUMBER	SQL語句的哈希值
SQL_ID	VARCHAR2(13)	SQL語句的SQL標(biāo)識符
TABLESPACE	VARCHAR2(31)	分配空間的表空間
CONTENTS	VARCHAR2(9)	指示表是否 TEMPORARY 或 PERMANENT
SEGTYPE	VARCHAR2(9)	排序類型的類型： SORT HASH DATA INDEX LOB_DATA LOB_INDEX
SEGFILE#	NUMBER	初始范圍的文件號
SEGBLK#	NUMBER	初始范圍的塊號
EXTENTS	NUMBER	分配給排序的范圍
BLOCKS	NUMBER	分配給排序的塊中的范圍
SEGRFNO#	NUMBER	初始范圍的相對文件號

關(guān)于V$TEMP_EXTENT_POOL的官方文檔中的解釋

V$TEMP_EXTENT_POOL顯示緩存并用于實例的臨時空間的狀態(tài)。

請注意，臨時空間緩存的加載是惰性的，并且實例可以處于休眠狀態(tài)。

柱	數(shù)據(jù)類型	描述
TABLESPACE_NAME	VARCHAR2(30)	表空間的名稱
FILE_ID	NUMBER	絕對文件號
EXTENTS_CACHED	NUMBER	已緩存的范圍數(shù)
EXTENTS_USED	NUMBER	實際使用的范圍數(shù)
BLOCKS_CACHED	NUMBER	緩存的塊數(shù)
BLOCKS_USED	NUMBER	使用的塊數(shù)
BYTES_CACHED	NUMBER	緩存的字節(jié)數(shù)
BYTES_USED	NUMBER	使用的字節(jié)數(shù)
RELATIVE_FNO	NUMBER	相對文件號

關(guān)于V$TEMPORARY_LOBS官方文檔解釋

V$TEMPORARY_LOBS 顯示臨時LOB。

柱	數(shù)據(jù)類型	描述
SID	NUMBER	會話ID
CACHE_LOBS	NUMBER	緩存臨時LOB的數(shù)量
NOCACHE_LOBS	NUMBER	nocache臨時LOB的數(shù)量
ABSTRACT_LOBS	NUMBER	抽象LOB的數(shù)量