对于一些比较大的数据文件,想回收一般就采用:先清除这个数据文件的数据,然后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;

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