select '数据文件空间概述' "对象名",
'-------------' "类型",
'文件编号' "文件编号",
'分配的空间' "起始块号",
'使用的空间' "结束块号",
'可释放空间' "大小(MB)"
from dual
union all
select '相邻起始块号相差大的就是空闲区',
'单位(GB)',
to_char(a.file_id),
to_char(round(bytes / 1024 / 1024 / 1024, 2)) alloc_size,
to_char(b.filesize) actual_size,
to_char(round(bytes / 1024 / 1024 / 1024, 2) - b.filesize) can_dealloc
from dba_data_files a,
(select file_id, round(sum(bytes) / 1024 / 1024 / 1024, 2) filesize
from dba_extents
where tablespace_name = 'IPCCWEBPLAT'
group by file_id) b
where a.tablespace_name = 'IPCCWEBPLAT'
and a.file_id = b.file_id
union all
select *
from (select *
from (select owner || '.' || segment_name ||
case partition_name
when null then
null
else
'.' || partition_name
end seg_name,
segment_type,
to_char(file_id) file_id,
to_char(min(block_id)) min_block_id,
to_char(max(block_id + blocks - 1)) max_block_id,
to_char(round(sum(bytes) / 1024 / 1024, 2)) seg_size
from dba_extents
where tablespace_name = 'IPCCWEBPLAT'
group by owner || '.' || segment_name ||
case partition_name
when null then
null
else
'.' || partition_name
end,
segment_type,
file_id)
ORDER BY file_id, to_number(max_block_id) DESC);
阅读(600) | 评论(0) | 转发(0) |
