[Oracle] 檢視tablespace的使用率(Including temp tablespace)
以前也寫過檢視tablespace的sql,
下面寫的sql包含temp tablespace的使用率的
將permanent和temporary的使用率包含在一起的.
select * from
(select x.tablespace_name,x.contents,x.status,x.extent_management,x.all_bytes/1024/1024 "ALL_SIZE(MB)",y.free_bytes/1024/1024 "FREE_SIZE(MB)",
(x.all_bytes-y.free_bytes)/1024/1024 "USED_SIZE(MB)",round((x.all_bytes-y.free_bytes)/x.all_bytes,4)*100 USAGE
from (select ts.tablespace_name,ts.contents,ts.status,ts.extent_management,sum(bytes) all_bytes from
dba_tablespaces ts, dba_data_files dbf
where ts.tablespace_name=dbf.tablespace_name group by ts.tablespace_name,ts.contents,ts.status,ts.extent_management) x,
(select tablespace_name,sum(bytes) free_bytes from dba_free_space fs group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
union all
select h.tablespace_name,'TEMPORARY' contents,f.status,'LOCAL',sum(h.bytes_free+h.bytes_used)/1024/1024 "ALL_SIZE(MB)",sum((h.bytes_free+h.bytes_used-nvl(p.bytes_used,0)))/1024/1024 "FREE_SIZE(MB)",
sum(p.bytes_used)/1024/1024 "USED_SIZE(MB)",round(sum(p.bytes_used)/sum(h.bytes_free+h.bytes_used),4)*100 USAGE
from v$temp_space_header h,v$temp_extent_pool p,dba_temp_files f
where h.file_id=p.file_id(+) and h.file_id=f.file_id
group by h.tablespace_name,f.status)
order by 8 desc
下面寫的sql包含temp tablespace的使用率的
將permanent和temporary的使用率包含在一起的.
select * from
(select x.tablespace_name,x.contents,x.status,x.extent_management,x.all_bytes/1024/1024 "ALL_SIZE(MB)",y.free_bytes/1024/1024 "FREE_SIZE(MB)",
(x.all_bytes-y.free_bytes)/1024/1024 "USED_SIZE(MB)",round((x.all_bytes-y.free_bytes)/x.all_bytes,4)*100 USAGE
from (select ts.tablespace_name,ts.contents,ts.status,ts.extent_management,sum(bytes) all_bytes from
dba_tablespaces ts, dba_data_files dbf
where ts.tablespace_name=dbf.tablespace_name group by ts.tablespace_name,ts.contents,ts.status,ts.extent_management) x,
(select tablespace_name,sum(bytes) free_bytes from dba_free_space fs group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
union all
select h.tablespace_name,'TEMPORARY' contents,f.status,'LOCAL',sum(h.bytes_free+h.bytes_used)/1024/1024 "ALL_SIZE(MB)",sum((h.bytes_free+h.bytes_used-nvl(p.bytes_used,0)))/1024/1024 "FREE_SIZE(MB)",
sum(p.bytes_used)/1024/1024 "USED_SIZE(MB)",round(sum(p.bytes_used)/sum(h.bytes_free+h.bytes_used),4)*100 USAGE
from v$temp_space_header h,v$temp_extent_pool p,dba_temp_files f
where h.file_id=p.file_id(+) and h.file_id=f.file_id
group by h.tablespace_name,f.status)
order by 8 desc
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2120428/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Oracle Script] check temp tablespace usageOracle
- tablespace 大檔案,undo,temp tablespace
- Oracle - ORA-01652: unable to extend temp segment by 128 in tablespace TEMPOracle
- Oracle11g新增檢視查詢表空間使用率DBA_TABLESPACE_USAGE_METRICSOracle
- Oracle10g新增的檢視dba_tablespace_usage_metricsOracle
- 使用SQL指令碼檢視錶空間使用率和使用dba_tablespace_usage_metrics檢視的區別SQL指令碼
- recover database delete archivelogs skip tablespace temp;報錯DatabasedeleteHive
- drop tablespace xx INCLUDING CONTENTS AND DATAFILES; 檔案不立刻消失
- ORA-1652: unable to extend temp segment by 256 in tablespace PSAPTEMPAPT
- Oracle OCP(48):UNDO TABLESPACEOracle
- Oracle Table and tablespace CompressOracle
- 檢視資料庫中tablespace和datafile的使用情況。資料庫
- 聊一聊Oracle的Tablespace(一)Oracle
- 檢視單個SQL消耗TEMP表空間以及TEMP表空間使用率SQL
- [Oracle Script] check tablespace usage infoOracle
- Oracle 11g tablespace usageOracle
- [Oracle] Raw device上Create TablespaceOracledev
- oracle unlimited tablespace 許可權的用途OracleMIT
- db2 sms tablespace 不支援large tablespaceDB2
- oracle 切換undo tablespace小結Oracle
- Rename Tablespace in Oracle database 10gOracleDatabase
- How to Rename Tablespace In Oracle10gOracle
- How to move Oracle Spatial objects from SYSAUX tablespace to a user defined tablespace [ID 1119758.1OracleObjectUX
- oracle10g中的bigfile tablespaceOracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle10 dba_tablespace中的retentionOracle
- ORA-1652: unable to extend temp segment by 128 in tablespace錯誤的解決方法
- Tablespace Space Script
- remap_tablespaceREM
- The SYSAUX Tablespace (40)UX
- 【TABLESPACE】Oracle表空間最佳實踐Oracle
- Oracle基礎 01 表空間 tablespaceOracle
- Tablespace Management Enhancements in Oracle Database 10gOracleDatabase
- oracle實用sql(6)--tablespace/datafile resizeOracleSQL
- oracle10g ASM transport_tablespaceOracleASM
- oracle實驗記錄 (bigfile tablespace)Oracle
- oracle實驗記錄 (transport tablespace(Rman))Oracle
- 【Oracle】oracle tablespace&datafile -- oracle表空間 分享[轉]Oracle