ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-03297: file contains used data beyond requested RESIZE value

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

原创 Linux操作系统 作者:paulyibinyi 时间:2008-02-25 11:01:16 4 删除 编辑

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

ALTER DATABASE
    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


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;

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

MAX(BLOCK_ID)
-------------
124553

 SQL> show parameter db_block_size;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL>   select 124553*8/1024 from dual;
 
124553*8/1024
-------------
  973.0703125

该块位于973M与974M之间

 

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

alter table t_obj move new_tablespace_name;

对于分区表信息:
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'));
  

 

 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-193116/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 闪回查询
请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6512200