简单看一下达梦表空间使用-凯发app官方网站

set lineshow off
select tablespace_name,
ts_type,
trunc(ts_curr_total)as cur_size_mb,
trunc(ts_curr_total-used) as curr_free_mb,
trunc(used) as used_mb,
trunc(pct_curr_used) as curr_used_pct,
decode(status,
0, 'online',
'offline') as status,
trunc(ts_total) as max_size_mb,
trunc(ts_total-used) as free_mb,
trunc(pct_used) as used_pct
from ( select df.name as tablespace_name,
'permanent' as ts_type,
status$ as status,
max_size as ts_total,
curr_max_size as ts_curr_total,
decode((max_size - used_size_t),
null, 0,
max_size - used_size_t) as free,
decode((curr_max_size - used_size_t),
null, 0,
curr_max_size - used_size_t) as curr_free,
decode(used_size_t,
null, 0,
used_size_t) as used,
decode(used_size_t,
null, 0,
round(cast(used_size_t / curr_max_size as decimal) * 100)) as pct_curr_used,
decode(used_size_t,
null, 0,
round(cast(used_size_t / max_size as decimal) * 100)) as pct_used
from (select a.name,
a.id,
a.status$,
sum(cast(b.total_size - b.free_size as decimal) * page() / 1024 / 1024) as used_size_p,
sum(case auto_extend when 0 then cast(b.total_size as decimal) * page() / 1024 / 1024 when 1 then b.max_size end) as max_size,
sum(cast(b.total_size as decimal) * page() / 1024 / 1024) as curr_max_size
from v$datafile b,
v$tablespace a
where b.group_id = a.id and a.name not in ('temp',
'roll')
group by a.name,
a.id,
a.status$) df
left join (select ts_id,
sum(n_full_extent n_free_extent n_frag_extent) * sf_get_extent_size() * page() / 1024 / 1024 as used_size_t
from v$segment_infos
group by ts_id) seg
on seg.ts_id = df.id
union all
select tablespace_name,
'undo' as ts_type,
status,
ts_total,
total as ts_curr_total,
free,
free as curr_free,
(total - free) as used,
round(cast((total - free) as decimal) * 100 / total) pct_curr_used,
round(cast((total - free) as decimal) * 100 / ts_total) pct_used
from (select t.name tablespace_name,
t.status$ status,
sum(cast(free_size as decimal) * page() / 1024 / 1024) as free,
sum(cast(d.total_size as decimal) * page() / 1024 / 1024) as total,
sum(case auto_extend when 0 then cast(d.total_size as decimal) * page() / 1024 / 1024 when 1 then d.max_size end) as ts_total
from v$tablespace t,
v$datafile d
where t.id = d.group_id and t.name in ('roll')
group by t.name,
t.status$)
union all
select tablespace_name,
'temporary' as ts_type,
status,
ts_total,
total as ts_curr_total,
free,
free as curr_free,
(total - free) used,
round(cast((total - free) as decimal) * 100 / total) pct_curr_used,
round(cast((total - free) as decimal) * 100 / ts_total) pct_used
from (select t.name tablespace_name,
t.status$ status,
cast(free_size as decimal) * page() / 1024 / 1024 as free,
cast(d.total_size as decimal) * page() / 1024 / 1024 as total,
case (select sys_value
from v$parameter
where name = 'temp_space_limit') when 0 then 99999999 else (select sys_value
from v$parameter
where name = 'temp_space_limit') end as ts_total
from v$tablespace t,
v$datafile d
where t.id = d.group_id and t.name in ('temp')) )
order by ts_type,
pct_curr_used desc;

阅读(68) | 评论(0) | 转发(0) |

上一篇：达梦删除同义词 -5596 没有权限

下一篇：timestamp 裸数据格式转换

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们