ORA-03297: file contains used data beyond requested RESIZE value

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

ALTER DATABASEITPUB个人空间$E7f]jD wo f
    DATAFILE '/oradata/ora9i/tools03.dbf' RESIZE  900M

但一执行报以下错误

ORA-03297: file contains used data beyond requested RESIZE value

ORA-03297 file contains used data beyond requested RESIZE value

z4[Fb
O4|0Cause: 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;

SQL>select max(block_id) from dba_extents where file_id=12;

MAX(BLOCK_ID)ITPUB个人空间,ZV.|t1qt
-------------
l7a/aZ0^:q0124553

 SQL> show parameter db_block_size;ITPUB个人空间iF)ZCHQ.ogV/J:k
 ITPUB个人空间 ^ h'isg3V,Ar
NAME                                 TYPE        VALUEITPUB个人空间x2R2Y:HK+/ g?:E
------------------------------------ ----------- ------------------------------
E4Sr r$C*G)_0db_block_size                        integer     8192ITPUB个人空间x o-~,v/.Lb
SQL>   select 124553*8/1024 from dual;ITPUB个人空间PAS;H!x:D hnS!Y4A
 
s-~b7bNt1Lg3a0124553*8/1024ITPUB个人空间#N%qMoWv
-------------ITPUB个人空间~rbL J'Z6t
  973.0703125

该块位于973M与974M之间

 

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

alter table t_obj move new_tablespace_name;

对于分区表信息:ITPUB个人空间FT5d;i+Fweo c sV
ALTER TABLE "TEST"."TB_ACCESS" 
z3XJmU ]Y0    MOVE PARTITION  "TB_ACCESS_P200608" 
.N7f1r*]6?/v:E%SP5@0    TABLESPACE "new_tablespace_name"

再进行回收表空间

 

转载：http://space.itpub.net/7199859/viewspace-193116