style="text-indent:2em">
select t.tablespace_name, round(sum(bytes / (1024 * 024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
3、求表空间的未用空间
col mbytes format 9999.9999
select tablespace_name, sum(bytes) / 1024 / 1024 mbytes
from dba_free_space
group by tablespace_name;
4、察看数据库的大小，和空间使用情况
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
select a.tablespace_name, a.bytes total, b.bytes used, c.bytes free,
(b.bytes * 100) / a.bytes "% used",
(c.bytes * 100) / a.bytes "% free"
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name;
5、查看表空间的文件使用情况
select b.file_id 文件id, b.tablespace_name 表空间, b.file_name 物理文件名,
b.bytes 总字节数, (b.bytes - sum(nvl(a.bytes, 0))) 已使用,
sum(nvl(a.bytes, 0)) 剩余,
sum(nvl(a.bytes, 0)) / (b.bytes) * 100 剩余百分比
from dba_free_space a,
dba_data_files b where a.file_id = b.file_id group by b.tablespace_name,
b.file_name, b.file_id, b.bytes order by b.tablespace_name
--/ dba_free_space --表空间剩余空间状况
-- dba_data_files --数据文件空间占用情况
6、 查询表空间的碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name, block_id, bytes, blocks, 'free space' segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
group by tablespace_name;
