因为磁盘空间不够，而表空间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即可