獲取資料庫空閒空間的SQL
created by Tom Kyte.
Tablespace Name : name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace. Second character of A implies ASSM managed storage, second character of M implies manually managed (pctused, freelists, etc are used to control space utilization)
Kbytes : allocated space of the tablespace, sum of kbytes consumed by all datafiles associated with tablespace.
Used : space in the tablespace that is used by some segment.
Free : space in the tablespace not allocated to any segment.
%Used : ratio of free to allocated space
largest : mostly useful with dictionary managed tablespaces, the size of the largest contigously set of blocks available. If this number in a dictionary managed tablespace is smaller than the next extent for some object, that object could fail with "out of space" even if the FREE column says there is lots of free space.
MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set the maxsize to be less than the current size of a file)
%Max Used : how much of the maximum autoextend size has been used so far
set linesize 200
-------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/
8i或以下版本:
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-21766/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何檢視Oracle資料庫表空間大小(空閒、已使用),是否要增加表空間的資料檔案...Oracle資料庫
- oracle的空間資料庫:Oracle資料庫
- 查詢表空間已使用空間和空閒空間的簡單檢視
- 分析表空間空閒率並收縮表空間
- mssql 獲取表空間大小SQL
- 空間索引 - 各資料庫空間索引使用報告索引資料庫
- 資料庫空間重整方案資料庫
- SHARED POOL 空閒空間分配流程
- 獲取表空間是否可自動擴充套件的SQL套件SQL
- SQL Server 2008 空間資料庫 --- 空間索引概念及建立(取自幫助)SQLServer資料庫索引
- linux 獲取磁碟空間大小Linux
- Oracle資料庫的空間管理技巧Oracle資料庫
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- Sybase資料庫空間相關資料庫
- Oracle資料庫管理 版主空間Oracle資料庫
- 檢視資料庫表空間資料庫
- oracle清除資料庫表空間Oracle資料庫
- 資料庫硬碟空間如何算資料庫硬碟
- 刪除資料庫表空間資料庫
- 【SQL】查詢及修改資料庫預設表空間SQL資料庫
- 【TUNE_ORACLE】檢視Oracle的壞塊在空閒空間中還是在已用空間中的SQL參考OracleSQL
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- PHP 獲取伺服器磁碟空間PHP伺服器
- oracle資料庫中索引空間的重用Oracle資料庫索引
- 檢視Oracle資料庫表空間大小,是否需要增加表空間的資料檔案Oracle資料庫
- 詳細顯示資料表空間的使用率與剩餘空間的SQLSQL
- 資料庫和表空間資料移動資料庫
- Runloop有效利用空閒時間OOP
- 改變資料庫undo表空間資料庫
- 資料庫物件遷移表空間資料庫物件
- 在資料庫之間移動表空間資料庫
- 表空間sqlSQL
- SciPy 空間資料
- 閒聊oracle SQL*Net相關的空閒等待事件OracleSQL事件
- 達夢資料庫表空間等空間大小查詢方法總結資料庫
- 空間資料庫三維空間兩點距離計算錯誤資料庫
- 給資料庫新增儲存空間的案例資料庫
- PHP 獲取不帶名稱空間的類名PHP