Oracle数据库压缩表空间,进行转移表

公司的Oracle数据库表都建立到了默认的users表空间中,导致表空间很大,所以需要进行空间压缩,但是执行压缩的过程,有点无奈.

首先要压缩一个表空间就要查看当前表空间的大小,以及表空间对应的最大块.

<span style="font-family:FangSong_GB2312;font-size:18px;">SELECT dd.tablespace_name,
dd.autoextensible,
file_name,
　　 round(SUM(dd.bytes) / (1024 * 1024), 2) <strong>tot_grootte_mb 　　</strong>
from sys.dba_data_files dd 　　
WHERE dd.tablespace_name='USERS'
group BY dd.tablespace_name,
dd.autoextensible,
file_name</span>

tot_grootte_mb就是换算完成后这个表空间的实际大小了单位:MB

然后我们看看这个表空间中最大块的位置计算下可以缩小的大小是多少

<span style="font-family:FangSong_GB2312;font-size:18px;">SELECT MAX(block_id) <strong>maxblock</strong>,
       MAX(block_id) * 8192 / 1024 / 1024 <strong>maxblock_mb</strong>
  FROM dba_extents
 WHERE tablespace_name = 'USERS';
</span>

</pre><p></p><p><span style="font-family:FangSong_GB2312;font-size:18px;">如果差别比较大那就直接压缩了:</span></p><p><pre name="code" class="sql"><span style="font-family:FangSong_GB2312;font-size:18px;">alter database datafile '/path/data.dbf' resize 1024M;  </span>

1024是看你最大块计算后得到的maxblock_mb来的

结果...我这个最大块和现在的大小基本没区别..坑了.

这时候去看看这个表空间中最大块都是什么东西,能转走,转走..

SELECT t.block_id,t.tablespace_name,t.segment_name,t.segment_type
FROM dba_extents t
WHERE t.tablespace_name=upper('users')
ORDER BY t.block_id DESC

用这个查看什么东东在数据库数据块的最上面,我比较惨.最上面是lob文件的字段信息和索引信息.不了解的可以根据: LOBSEGMENT和 LOBINDEX 去查下,反正我查了有一会儿..才找明白怎么转走这两个大神

根据表空间排在最上面的数据信息找到对应的数据文件然后转到你希望他存在的位置.

转移使用的语句:

1转移数据表:

alter table <strong>table_name </strong>move tablespace <strong>tablespace_name </strong>

写的肯定是你要转的表名和你希望他去的表空间了.

2转移索引:

alter index <strong>index_name </strong>rebuild tablespace <strong>tablespace_name</strong>

写的当然是索引名和要去的表空间了..

3转移坑爹的lob字段:

-1你要根据查到的LOBINDEX 去找到对应属于什么表什么字段.好坑爹有木有.

查找方式:

SELECT table_name, column_name, t.tablespace_name,segment_name, index_name FROM user_lobs t
WHERE t.index_name='<strong><span style="color:#ff0000;">SYS_IL0000208950C00032$$</span></strong>'

其中意乱凌乱的东西就是数据库自动给你生成的lob字段的索引名了,根据这个你可以得到,其所在的表,表空间,以及对应的字段.然后在转移这个东东.

转移语句如下:

alter table <strong>table_name </strong>move lob(<strong>column_name</strong>) store as (tablespace <strong>tablespace_name</strong>);

反正我是查了好多地方才看明白这么写的.可能是我比较笨吧..

然后如愿我转走了这个坑爹的字段.

再然后我开始压缩我的表空间了..

这个博文只是记录下操作过程.防止下次被坑,也帮助下遇到相同情况的同志们.