由于要做一个测试，我在users表空间中创建一个表并且插入了500万条数据，结果导致数据文件的大小由1G涨到了8G。测试完成后删除表，但数据文件的大小仍然维持在8G。

研究了一下，可以通过resize datafile来减小数据文件的大小。

首先，要查清楚数据文件的真实使用空间。可以通过查询dba_extents达到这个目的。users表空间使用的数据文件为D:\ORACLE\ORADATA\EPCIT\DATA_FILE\EPCITUSERS01.DBF

Sql代码  

1. SQL> select file_name, sum(e.bytes)/1024/1024 as MB  
2.   2  from dba_extents e join dba_data_files f on e.file_id=f.file_id  
3.   3  group by file_name;  
4.   
5. FILE_NAME                                                       MB  
6. ------------------------------------------------------- ----------  
7. E:\ORADATA\DATA_FILE\EPCITSYSAUX01.DBF                      330.25  
8. E:\ORADATA\DATA_FILE\EPCITBLOBS01.DBF                     197.1875  
9. D:\ORACLE\ORADATA\EPCIT\DATA_FILE\PERFSTAT.DBF              143.75  
10. D:\ORACLE\ORADATA\EPCIT\DATA_FILE\EPCITUSERS01.DBF        143.3125  
11. E:\ORADATA\DATA_FILE\EPCITINDEX01.DBF                      421.625  
12. E:\ORADATA\DATA_FILE\EPCITSYSTEM01.DBF                    355.5625  
13. E:\ORADATA\DATA_FILE\EPCITUNDOTBS01.DBF                    26.3125  
14.   
15. 7 rows selected.  

SQL> select file_name, sum(e.bytes)/1024/1024 as MB
  2  from dba_extents e join dba_data_files f on e.file_id=f.file_id
  3  group by file_name;

FILE_NAME                                                       MB
------------------------------------------------------- ----------
E:\ORADATA\DATA_FILE\EPCITSYSAUX01.DBF                      330.25
E:\ORADATA\DATA_FILE\EPCITBLOBS01.DBF                     197.1875
D:\ORACLE\ORADATA\EPCIT\DATA_FILE\PERFSTAT.DBF              143.75
D:\ORACLE\ORADATA\EPCIT\DATA_FILE\EPCITUSERS01.DBF        143.3125
E:\ORADATA\DATA_FILE\EPCITINDEX01.DBF                      421.625
E:\ORADATA\DATA_FILE\EPCITSYSTEM01.DBF                    355.5625
E:\ORADATA\DATA_FILE\EPCITUNDOTBS01.DBF                    26.3125

7 rows selected.

可以看出EPCITUSERS01.DBF实际使用空间只有143MB。resize datafile的时候不可以小过这个大小。

Sql代码  

1. SQL> alter database datafile 6 resize 100M;  
2. alter database datafile 6 resize 100M  
3. *  
4. ERROR at line 1:  
5. ORA-03297: file contains used data beyond requested RESIZE value  
6.   
7. SQL> alter database datafile 6 resize 500M;  
8.   
9. Database altered.