Oracle resize收缩数据文件

如遇到操作系统层面空间不够，临时需要收缩数据文件来腾出空间的话，可按如下步骤执行：
1.查询数据文件可收缩的情况

SELECT a.tablespace_name,
file_name,
c.VALUE / 1024 "Blk. size(Kb)",
CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size') c
WHERE a.file_id = b.file_id(+)
and a.status !='INVALID'
order by 6;

TABLESPACE_NAME           FILE_NAME                                               Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------------- ------------------------------------------------------- ------------- ------------------ ------------ -----------
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR02.DBF                  8            8192    8192           0
SYSTEM                    E:\ORACLE\ORADATA\FRORCL\SYSTEM01.DBF                               8            1570    1570           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR05.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR08.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR09.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR12.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR03.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR16.DBF                  8           10240   10240           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR13.DBF                  8           10240   10240           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR20.DBF                  8           30720   30720           0
UNDOTBS1                  E:\ORACLE\ORADATA\FRORCL\UNDOTBS02.DBF                              8            1024    1024           0

TABLESPACE_NAME           FILE_NAME                                               Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------------- ------------------------------------------------------- ------------- ------------------ ------------ -----------
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR                        8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR04.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR06.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR19.DBF                  8           30720   30720           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR17.DBF                  8           30720   30720           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR013.DBF                 8           16384   16384           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR07.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR15.DBF                  8           10240   10240           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR18.DBF                  8           30720   30720           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR11.DBF                  8            8192    8192           0
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR10.DBF                  8            8192    8192           0

TABLESPACE_NAME           FILE_NAME                                               Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------------- ------------------------------------------------------- ------------- ------------------ ------------ -----------
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR14.DBF                  8           16384   16384           0
USERS                     E:\ORACLE\ORADATA\FRORCL\USERS01.DBF                                8          5           10           5
FRUSER_TD                 E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FRUSER                    8            8174    8192          18
EXAMPLE                   E:\ORACLE\ORADATA\FRORCL\EXAMPLE01.DBF                              8             313     347          34
FRUSER_TD                 E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FRUSER02                  8            8152    8192          40
SYSAUX                    E:\ORACLE\ORADATA\FRORCL\SYSAUX01.DBF                               8           13746   14440         694
SYSTEM                    E:\ORACLE\ORADATA\FRORCL\SYSTEM02.DBF                               8            5966    8192        2226
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR21.DBF                  8           27433   30720        3287
UNDOTBS1                  E:\ORACLE\ORADATA\FRORCL\UNDOTBS03.DBF                              8            6193   10240        4047
KETTLE_TD                 E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\KETTLE                    8          7         8192        8185
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR23.DBF                  8            1320   10720        9400

TABLESPACE_NAME           FILE_NAME                                               Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
------------------------- ------------------------------------------------------- ------------- ------------------ ------------ -----------
FRUSER_10_TD              E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FRUSER_10_1               8         15        16384       16369
FR_TD                     E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR22.DBF                  8           10825   30720       19895
UNDOTBS1                  E:\ORACLE\ORADATA\FRORCL\UNDOTBS01.DBF                              8            6084   32768       26684

已选择36行。

解释：
tablespace_name：表空间名称
file_name：数据文件名称
blk.size：block_size
smallest：数据文件可缩小到的最小大小
currentsize：数据文件当前大小
savings：数据文件可收缩的空间（其实就是currentsize-smallest的值）

执行操作：

SQL> alter database datafile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR22.DBF' resize 10825M;

数据库已更改。

SQL> alter database datafile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FRUSER_10_1' resize 15M;

数据库已更改。

SQL> alter database datafile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR23.DBF' resize 1320M;

数据库已更改。

SQL> alter database datafile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\KETTLE' resize 7M;

数据库已更改。

SQL> alter database datafile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\FR21.DBF' resize 27433M;

数据库已更改。

这样，就通过收缩数据文件来达到腾出空间的目的了