碎片程度

最新推荐文章于 2025-09-26 13:49:40 发布

转载最新推荐文章于 2025-09-26 13:49:40 发布 · 110 阅读

本文探讨了在数据库管理中提高性能的关键SQL查询及表空间管理策略，包括碎片程度查询、表空间名称关联、块ID与存储空间的联合查询等，通过实例展示了如何有效利用SQL语句进行数据优化。

碎片程度

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;

查看碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20709695/viewspace-707618/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20709695/viewspace-707618/