user tablespace 空闲空间太大，无法回收的问题

tonytang

在查看表空间剩余空间的时候发现user tablespace非常大，而且利用率很低
Name      Type      Managment       Files    Size(M)    Used(M)    Free(M)    used(%)
------------------------------ --------- ---------- ---------- ---------- ----------
USERS     PERMANENT LOCAL             5   155923.8      68325    87598.8      43.82
应该是应用人员将用户数据文件，放在了usertablespace,于是想先压缩空间再做segment迁移的操作，
我先查询usertablespace的可压缩空间
SELECT a.file_id,a.tablespace_name,CEIL( (NVL(hwm,1)*c.value)/1024/1024 ) smallest,CEIL( blocks*c.value/1024/1024) currsize,CEIL( blocks*c.value/1024/1024) -
CEIL( (NVL(hwm,1)*c.value)/1024/1024 ) savings FROM DBA_DATA_FILES a,( SELECT file_id, MAX(block_id+blocks-1) hwm FROM DBA_EXTENTS
GROUP BY file_id ) b,v$parameter c WHERE c.name='db_block_size' and a.file_id = b.file_id(+) and a.tablespace_name='USERS' order by savings
/
  FILE_ID TABLESPACE_NAME                  SMALLEST   CURRSIZE    SAVINGS
---------- ------------------------------ ---------- ---------- ----------
        25 USERS                               30720      30720          0
        26 USERS                               30720      30720          0
         7 USERS                               32764      32764          0
        24 USERS                               29864      30720        856
        27 USERS                               18280      31000      12720
为什么现在可以压缩的空间居然只有13G不到，明明表空间空余的空间是87G多啊？===个人猜测可能是从文件尾开始写，所以无法压缩，请问现在我如何回收我的空间？

tonytang

谢谢刘大，这是一个很耗费时间的工作和细心的工作，我已经做好脚本准备今天晚上跑了.我想情况我如何验证迁移完全成功，所以是否完全rebuild呢？

Maclean Liu(刘相兵

1. 可以用 4楼提供的语句查询
2. 移动需要 使用move tablespace， 对于有索引的表 还需要rebuild索引

tonytang

原帖由 不了峰 于 2012-6-5 10:15 发表
查看数据文件最末端的对象
SELECT /*+ rule */ owner,segment_name,segment_type FROM DBA_EXTENTS
WHERE  file_id=&file_id AND block_id =
( SELECT MAX(block_id) FROM DBA_EXTENTS WHERE file_id=&file_id ) ...

block_id如何定位啊?还有查询到之后如何移动到前端啊！生产机器，空间确实比较紧了！非常谢谢回复

tonytang

原帖由 紫恒 于 2012-6-5 10:02 发表
应该是查看表的位置，然后把后面的表移到前面来，然后在收缩，应该是这样

这个users tablespace里面有很多的表，这样工作量会不会很大？

不了峰

查看数据文件最末端的对象
SELECT /*+ rule */ owner,segment_name,segment_type FROM DBA_EXTENTS
WHERE  file_id=&file_id AND block_id =
( SELECT MAX(block_id) FROM DBA_EXTENTS WHERE file_id=&file_id )

紫恒

如果表内数据不多的话可以把表都导出来，然后表空间删了重建，在导回去。就不存在中间有空闲的问题了

紫恒

应该是查看表的位置，然后把后面的表移到前面来，然后在收缩，应该是这样