【轉自】http://space.itpub.net/48361/viewspace-253106

?

最近歷史數據庫磁盤空間不足，而有一個表空間有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;

表空間已刪除。

到此調整已經結束了。
其實調整方法有很多，如用imp/exp等
如果我的做法有什么不足之處請執教，誰有更好的辦法歡迎提供，本人水平有限。

?

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的缩小数据文件尺寸报ORA-03297的处理办法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。