Database Free space report
Database Free space report
- 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 121 ------------------------------------------------------- -- 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 /
If you are stuck on software written last century:
-------------------------------------------------------- -- 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','*',' ') 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 /
分类:
ORACLE
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战