查看各个表空间可以回收空间的脚本

Select '/* ' || To_Char(Ceil((f.Blocks - e.Hwm) * (f.Bytes / f.Blocks) / 1024 / 1024),
                        99999999) || ' M */ ' ||
       'alter database datafile ''' || File_Name || ''' resize ' ||
       Ceil(e.Hwm * (f.Bytes / f.Blocks) / 1024 / 1024) || 'M;' Sql
  From Dba_Data_Files f,
       Sys.Ts$ t,
       (Select Ktfbuefno Relative_Fno,
               Ktfbuesegtsn Ts#,
               Max(Ktfbuebno + Ktfbueblks) Hwm
          From Sys.X$ktfbue
         Group By Ktfbuefno, Ktfbuesegtsn) e
 Where f.Relative_Fno = e.Relative_Fno
   And t.Name = f.Tablespace_Name
   And t.Ts# = e.Ts#
   And f.Blocks - e.Hwm > 1000;