Oracle回收数据文件空间时报ORA-03297的解决方法
ORA-03297: file contains used data beyond requested RESIZE value

当我们回收数据库空间时,常用的方法是:

ALTER DATABASE DATAFILE '/oracle/u01/app/oracle/oradata/EMREP/user.dbf' RESIZE 3M

但一执行报以下错误

ORA-03297: file contains used data beyond requested RESIZE value
ORA-03297 file contains used data beyond requested RESIZE value
Cause: Some portion of the file in the region to be trimmed is currently in use by a database object.
Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.

使用如下脚本可以获得分配到高位top_blocks的对象信息

SQL> select distinct owner,segment_name,segment_type from dba_extents where file_id = &file_id and (block_id + &top_blocks) >(select max(block_id) from dba_extents where file_id=&file_id)

top_blocks 可以通过以下方法得出;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------
1 /oracle/u01/app/oracle/oradata/EMREP/system.dbf
2 /oracle/u01/app/oracle/oradata/EMREP/undotbs.dbf
3 /oracle/u01/app/oracle/oradata/EMREP/sysaux.dbf
5 /oracle/u01/app/oracle/oradata/EMREP/user.dbf

SQL>select max(block_id) from dba_extents where file_id=12;
MAX(BLOCK_ID)
-------------
2913

SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL>select 2913*8/1024 from dual;

2913*8/1024
-------------
22.7578125

该块位于22M与23M之间

通过上面sql查出来的对象信息

SQL> select distinct owner,segment_name,segment_type from dba_extents where file_id = &file_id and (block_id + &top_blocks) >(select max(block_id) from dba_extents where file_id=&file_id)

OWNER SEGMENT_NA SEGMENT_TYPE
---------- ---------- ------------------
SCOTT T2 TABLE
SCOTT M1 TABLE

file_id=5
top_blocks=2913

移动表空间,回收高水位
alter table t2 move tablespace users;
alter table m1 move tablespace users;

重新更改测试
ALTER DATABASE DATAFILE '/oracle/u01/app/oracle/oradata/EMREP/user.dbf' RESIZE 3M
Database altered.

对于分区表信息:

ALTER TABLE "TEST"."TB_ACCESS"
MOVE PARTITION "TB_ACCESS_P200608"
TABLESPACE "new_tablespace_name"

再进行回收表空间

整合出来的sql语句如下:

select distinct owner, segment_name, segment_type,tablespace_name
from dba_extents
where file_id =
(select file#
from v$datafile
where name = '/oradata/ora9i/GAME_LARGE_2006_4_1.dbf')
and (block_id + (select max(block_id)*8/1024 from dba_extents where file_id=(select file#
from v$datafile
where name = '/oradata/ora9i/GAME_LARGE_2006_4_1.dbf'))) >
(select max(block_id)
from dba_extents
where file_id =
(select file#
from v$datafile
where name = '/oradata/ora9i/GAME_LARGE_2006_4_1.dbf'));