对于一些比较大的数据文件，想回收一般就采用：先清除这个数据文件的数据，然后resize大小

实验：

先创建一个测试表空间

create tablespace test datafile '/u01/app/oracle/oradata/U01/datafile/test.dbf'  size 10M autoextend on next 50M;

然后为这个表空间创建一个表

create table test1 tablespace test as select * from dba_objects;

现在查看这个表空间数据文件大小

select BYTES/1024/1024,MAXBYTES/1024/1024 from Dba_Data_Files where tablespace_name ='TEST';

现在查看文件大小还是开始的10M

接着添加数据：

insert into   test1   select * from dba_objects ;

然后 commit;

现在再查看这个数据文件的大小，看看是不是成了60M了

再查看一下，数据文件具体用的大小情况：

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "当前空闲空间(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "总剩余空间",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='TEST'
　　ORDER BY 3 desc;

从这个看出实际用的并没有那么多，然后再把这个文件增加，接着添加数据

insert into TEST1   select * from test1 ;
commit;

现在再查看下数据文件具体的大小

然后根据这个实际占用大小回收数据文件

select FILE_ID from dba_data_files where tablespace_name='TEST';
alter database datafile X resize XX M;

现在是可以回收的。

回收之后再查看下，数据文件占用的具体大小。

这时大小是肯定改变了。

然后再创建一个表2

create table test2 tablespace test as select * from dba_objects;

再看下具体占用空间，看看是不是已经改变了。

然后清除表1的数据

truncate table test1;

清除之后，接着再查看数据文件具体的大小

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "当前空闲空间(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "总剩余空间",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name='TEST'
　　ORDER BY 3 desc;

这个和上面的一样。。。。

看下执行结果实际占用的大小是不是已经改变。

然后现在执行回收数据

alter database datafile X resize XXM;

是不是报错了。。。。

为什么呢？

因为现在在文件中只是数据没有了，但是块号还没有改变，也就是block_id还没有改变，可以通过查到

select  segment_name,block_id,blocks FROM dba_extents where Tablespace_name ='TEST' order by block_id desc;

这个返回结果意思是：block_id 是这个segment_name段所对应的一个区的起始块号，对应的这个区里面有连续的blocks个块

然后总共的块数就是这个段所占用的空间，也就是总共有多少个块。

现在块的编号还没有改变，所以不能回收大小。

需要执行：

alter table test2 move;

这个意思就是说重新分布表2的块号，使高水位线降低。然后回收数据文件的大小

再执行：

alter database datafile X resize XXM;

现在就可以回收了。。。。