缩小数据文件尺寸报ORA-03297的处理办法

最近历史数据库磁盘空间不足，而有一个表空间有50个G容量，但是实际只占100m的空间，
使用ALTER TABLE table SHRINK SPACE CASCAD后大部分数据文件可以调整，当试图调整其中一个数据文件尺寸的时候报
RA-03297: file contains used data beyond requested RESIZE value
说明这个文件不能通过降低hwm来释放空间了。

数据库版本：oracle 10.2.1
--找到数据文件对应的文件号
SQL>select file#,name from v$datafile where name like '%BASEINFO.dbf';
5                  /data/eucpdb/eucpdb/BASEINFO.dbf

找到文件中最大的块号
SQL>select max(block_id) from dba_extents where file_id=5 ；

1213833

--查看数据库块大小
SQL>show parameter db_block_size

db_block_size integer 8192

计算一下文件中最大使用块占用的位置
SQL>select 1213833*8/1024 from dual;

9483.0703125 M

--为了验证上面做法的准确性，下面做一个试验                      

--调整前数据文件大小为10000M
--现在调整数据库文件为9500M
SQL>ALTER DATABASE DATAFILE '/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9500M
  2  /

数据库已更改

--调整文件为 9400m

SQL>ALTER DATABASE DATAFILE '/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9400M
  2  /

SQL> ALTER DATABASE DATAFILE '/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9400M;
ALTER DATABASE DATAFILE '/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9400M
*
第 1 行出现错误:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

--看来了上面的计算是准确的

SQL> col segment_name format a30
SQL> SET LIN 200  
SQL> select segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=5 and   block_id='1213833';

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
------------------------------ ------------------ ------------------------------ ---------- ---------- ----------
REGISTRYINFO                   TABLE              BASEINFO                               25     524288         64

SQL>

SQL>  ALTER TABLE eucpmanager.REGISTRYINFO SHRINK SPACE CASCADE;

表已更改。

创建一个新的表空间，把block_id比较高的几个表移出表空间

SQL> CREATE SMALLFILE TABLESPACE "BASEINFO_BAK" DATAFILE '/data/eucpdb/eucpdb/baseinfo_bak.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
  2  /

表空间已创建。

SQL> alter user eucpmanager quota unlimited on BASEINFO_BAK;

用户已更改。

把block_id比较高的几个表移动到新的表空间

SELECT DISTINCT SEGMENT_NAME FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO' AND FILE_ID=5 and block_id>1159985 and segment_type='TABLE' ;

SQL> SELECT distinct  'alter table '|| SEGMENT_NAME||' move tablespace baseinfo_bak; ' FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO' AND FILE_ID=5 and block_id>1159985 and segment_type='TABLE' ;

'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACEBASEINFO_BAK;'
----------------------------------------------------------------------------------------------------------------------------
alter table ENTERPRISESERVICEINFO move tablespace baseinfo_bak;
alter table REGISTRYFEEDETAILEX move tablespace baseinfo_bak;
alter table ENTERPRISEROUTE move tablespace baseinfo_bak;
alter table REGISTRYAUTHINFO move tablespace baseinfo_bak;
alter table ENTERPRISEBASEINFO move tablespace baseinfo_bak;

SQL> alter table ENTERPRISESERVICEINFO move tablespace baseinfo_bak;
alter table REGISTRYFEEDETAILEX move tablespace baseinfo_bak;
alter table ENTERPRISEROUTE move tablespace baseinfo_bak;
alter table REGISTRYAUTHINFO move tablespace baseinfo_bak;
alter table ENTERPRISEBASEINFO move tablespace baseinfo_bak;
表已更改。

SQL>
表已更改。

SQL>
表已更改。

SQL>
表已更改。

告警日志中会出现下面的内容，索引需要重建
Thu Apr 24 14:20:21 2008
Some indexes or index [sub]partitions of table EUCPMANAGER.ENTERPRISEBASEINFO have been marked unusable

把下面的执行结果的语句执行所有重建
 SELECT distinct  'alter INDEX '|| SEGMENT_NAME||' REBUILD TABLESPACE BASEINFO_BAK; ' FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO' AND FILE_ID=5 and block_id>1159985 and segment_type='INDEX' ;
 
 
 alter INDEX PK_ENTERPRISEBASEINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX PK_REGISTRYINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX PK_REGISTRYFEEDETAILEX REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;
alter INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO_BAK;

再次修改数据文件大小

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

MAX(BLOCK_ID)
-------------
         3209

SQL> ALTER DATABASE DATAFILE '/data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 1000m;

数据库已更改。

数据库文件的空间已经调整成功了

SQL>

--把挪走的表在挪回来

SQL> alter table ENTERPRISESERVICEINFO move tablespace baseinfo;
alter table REGISTRYFEEDETAILEX move tablespace baseinfo;
alter table ENTERPRISEROUTE move tablespace baseinfo;
alter table REGISTRYAUTHINFO move tablespace baseinfo;
alter table ENTERPRISEBASEINFO move tablespace baseinfo;
alter table registryinfo move tablespace baseinfo;
表已更改。

SQL>
表已更改。

--重建索引
SQL>
SQL>
SQL> ALTER INDEX PK_REGISTRYINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX AK_REGISTRYCODE_REGISTRYINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX PK_ENTERPRISEBASEINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX PK_ENTERPRISESERVICEINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX UNI_REGISTRYAUTHINFO REBUILD TABLESPACE BASEINFO;
ALTER INDEX PK_REGISTRYFEEDETAILEX REBUILD TABLESPACE BASEINFO;

索引已更改。

SQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='BASEINFO_BAK' ;

  COUNT(*)
----------
         0

已经没有对象在新建的这个表空间了。现在删除掉

SQL> drop tablespace baseinfo_bak;

表空间已删除。