【赛迪网－it技术报道】空间状态相关内容

1、查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 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;