set linesize 200
set pagesize 200
col tablespace_name format a20
col extent_management format a20
col "spc_used" format a15
col "free/used/alloted/limited" format a30
col "used/ALLOC%" format 9999999
col "used/MAX%" format 9999999
With tbsstat As (
select a.tablespace_name,
(a.bytes_alloc - nvl(b.bytes_free, 0)) sizeold,
(CASE
WHEN round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) < 1 THEN
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 0) ||'m'
ELSE
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) ||'g'
END) "spc_used",
t.contents,t.status,t.extent_management ,
--free
(CASE
WHEN round(nvl(b.bytes_free, 0) / 1024 / 1024/1024, 0) < 1 THEN
round(nvl(b.bytes_free, 0) / 1024 / 1024, 0) || 'm'
ELSE
round(nvl(b.bytes_free, 0) / 1024 / 1024/1024, 0) || 'g'
END )||' / '||
--used
(CASE
WHEN round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) < 1 THEN
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 0) ||'m'
ELSE
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024, 0) ||'g'
END) || ' / ' ||
--alloc
(CASE
WHEN round(a.bytes_alloc /1024/1024/1024, 0) < 1 THEN
round(a.bytes_alloc /1024/1024 ,0)||'m'
ELSE
round(a.bytes_alloc /1024/1024/1024, 0)||'g'
END) || ' / ' ||
--limited
(CASE
WHEN round(maxbytes/1024/1024/1024,0) < 1 THEN
round(maxbytes/1024/1024,0)||'m'
ELSE
round(maxbytes/1024/1024/1024,0) ||'g'
END ) "free/used/alloted/limited",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,0) "used/ALLOC%",
round((a.bytes_alloc-nvl(b.bytes_free, 0))/maxbytes*100,0) "used/MAX%"
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b,
dba_tablespaces t
where t.tablespace_name = a.tablespace_name
AND a.tablespace_name = b.tablespace_name (+)
)
Select tablespace_name, /*"spc_used", Contents, status, extent_management,*/ "free/used/alloted/limited", "used/ALLOC%", "used/MAX%" From tbsstat
ORDER BY 3 desc;
Oracle ts.sql
未经允许不得转载:徐万新之路 » Oracle ts.sql
相关推荐
ASMLib 多路径配置
Oracle Linux 8上 Oracle ASM 共享盘命名方案 —— multipath 与 udev 两种实现
Oracle 26ai: How to install Oracle 26ai GI (HAS) using Native ASM
Oracle 26ai RAC 图形界面安装指南(VirtualBox + OL 8.10)
Vmware Workstation 2026下Oracle Linux 8.10 虚拟机网卡命名对齐方案(ebsdb1 / ebsdb2)
sysaux中HEATMAP 对象较大
生产库AWR手工清理及 SYSAUX 表空间收缩
Oracle Data Guard 11g到19c切换避坑指南

徐万新之路

