因为磁盘空间不够，而表空间data中的很多表已经被迁移走，现在需要从表空间data中回收空间；

一个数据文件能够resize(缩小)的最大限度需要取决于其max extent，通过以下sql可以查看

select a.FILE#,

       a.name,       ceil(HWM * a.BLOCK_SIZE) / 1024 / 1024 ResizeTO,       a.BYTES / 1024 / 1024 CurrentMB  from v$datafile a,       (select file_id, max(block_id + blocks - 1) HWM          from dba_extents         group by file_id) b,       dba_data_files d where a.file# = b.file_id   and d.FILE_ID = a.FILE#   and d.TABLESPACE_NAME = 'DATA'   and (a.BYTES - b.HWM * a.BLOCK_SIZE) > 0;

查询结果如下     FILE# NAME                                                                                                   RESIZETO  CURRENTMB---------- ---------------------------------------------------------------------------------------------------- ---------- ----------         6 /data/oracle/oradata/justin/data02.dbf                                                               8191.0625       8192        14 /data/oracle/oradata/justin/data43.dbf                                                              201.992188       8192        84 /data/oracle/oradata/justin/data24.dbf                                                               8191.0625       8192       105 /data/oracle/oradata/justin/data32.dbf                                                              8000.99219       8001       106 /data/oracle/oradata/justin/data33.dbf                                                              8000.99219       8001       107 /data/oracle/oradata/justin/data34.dbf                                                              8000.99219       8001       115 /data/oracle/oradata/justin/data35.dbf                                                              8191.99219       8192       118 /data/oracle/oradata/justin/data36.dbf                                                              8000.99219       8001       124 /data/oracle/oradata/justin/data37.dbf                                                              8191.99219       8192       129 /data/oracle/oradata/justin/data38.dbf                                                              8191.99219       8192       130 /data/oracle/oradata/justin/data39.dbf                                                              8191.99219       8192       131 /data/oracle/oradata/justin/data40.dbf                                                              5218.99219      10240       133 /data/oracle/oradata/justin/data41.dbf                                                              4045.99219      10240       134 /data/oracle/oradata/justin/data42.dbf                                                              3731.99219      10240       按照以上结果可以执行       alter database datafile 134 resize 3740m;

对于temp file，则需要查看v$sort_usage视图

select max(segblk#)*8192/1024/1024/1024 from v$sort_usage;

SQL> select max(segblk#)*8192/1024/1024/1024 from v$sort_usage;

MAX(SEGBLK#)*8192/1024/1024/1024

--------------------------------                      7.68756866

alter database tempfile '/data/oracle/oradata/justin/temp01.dbf' resize 7700m;

 如果是undo数据文件，则不必这么麻烦可以新建一个undo tablespace，指定较小的数据文件，并且执行alter system set undo_tablespace = new_undo scope =both;观察v$rollstat，待到旧的undo segment都offline时执行drop tablespace old_undo including contents and datafiles即可

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