Oracle查看表空間使用率

查看用戶默認(rèn)表空間

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;

查看表空間是否開啟自動(dòng)擴(kuò)展

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

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

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;

查看臨時(shí)表空間信息

SELECT * FROM DBA_TEMP_FILES

Oracle臨時(shí)表空間使用率

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

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

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

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

1.查詢臨時(shí)表空間的使用率

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語句使用的臨時(shí)表空間的大小
• BYTES_FREE：剩余多少臨時(shí)表空間

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

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

select * ?from V$TEMPORARY_LOBS;

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

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

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

V$TEMPSEG_USAGE 描述臨時(shí)段使用情況。 

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

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

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

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

V$TEMPORARY_LOBS 顯示臨時(shí)LOB。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論