有个小型Oracle 10g应用,用了多年数据大概50G,速度也越来越慢,想进行一下数据整理。
但是搞不清表空间碎片和表碎片的区别,按照网上的查询方法貌似都有碎片。使用 alter table XXX shrink SPACE 收缩表后发现统计的数据也没什么变化,想请教是应该先整理表空间,还是整理表,应该用什么样的方法?
aniven 发表于 2016-12-19 09:50
全库导入导出是不是用 exp/imp 导出再导入数据,这样可以同时减少表碎片和表空间碎片吗?另外收缩是指收 ...
Tom原话如下:
You Asked
Hi Tom,
I've entered the following command to coalesce my 'users' tablespace but it returned
without any coalescing:
alter tablespace users coalesce;
What is the reason?
and we said...
Coalesce simply takes contigous free extents and makes them into a single bigger free
extent.
It is not a reorganization tool.It is a command to take any free extents that are right
next to some other free extent and make one bigger free extent out of them.It is useful
after a drop command when you want to create another object right away.SMON will
normally perform this coalescing in the background but if you need it to happen "right
now", the coalesce command will do it.
我个人也从未使用过alter tablespace coalesce这个语句,从以上回复看此语句只是合并空extent为新的区。
至于你说的收缩到底是指表还是表空间,我只能说是数据文件,表和表空间最终是落实到数据文件上的。
导入导出会在实质上减少区的数目,并改变表在数据文件中的位置。
你说的alter table shrink实质上是收缩段的,也是有效的减少碎片的手段,但是需要2个条件且可能会造成阻塞。