ora-03297(表空間無法回收)問題的解決方法

在解決商函項目的磁盤空間滿及表空間回收問題時，碰到ora-03297錯誤提示，現把解決方法總結如下：

在實際使用中我們經常會遇到oracle某個表空間占用了大量的空間而其中的資料量卻隻占用了少量空間，此時我們可以用ALTER DATABASE DATAFILE '***.dbf' RESIZE nnM的指令來收縮表空間，但在收縮的過程中會遇到ora-03297錯誤，表示在所定義的空間之後有資料存在，不能收縮。

方法一、 先估算該表空間内各個資料檔案的空間使用情況：

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

b.SQL>select max(block_id) from dba_extents where file_id=11;

MAX(BLOCK_ID)

-------------

2560000

SQL>show parameter db_block_size

NAME TYPE VALUE

----------------------------- ------- ----------- db_block_size integer 8192

c.SQL>select 2560000*8/1024 from dual;

2560000*8/1024

-----------

20000

這說明該檔案中最大使用塊位于20G的位置，正好位于資料檔案的尾部。

d.找出塊中的對象，删除後重建。

SQL>select t.owner,t.segment_name,t.segment_type from dba_extents t where t.tablespace_name='SHTD';

e.收縮表空間

SQL> alter database datafile '/data/shtd/USERS01.dbf' resize 1024M;

Database altered.

重複b--d步，直到最大塊的位置與使用空間差不多為止。

總結：如果表空間中存在大量的資料庫對象，此方法太慢，同時由于是删除後重建，對象的授權丢失，導緻其它使用者無法操作此對象。

方法二、找到塊中的所有對象，然後删除重建

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

方法三、如果某些表占用了資料檔案的最後一些塊，則需要先将該表導出或移動到其他的表空間中，然後删除表，再進行收縮。不過如果是移動到其他的表空間，需要重建其索引。

SQL> alter table t_obj move tablespace t_tbs1;

缺點：移走了表後，表的索引沒有一起移走，如果表上有主鍵或唯一索引将無法删除表空間，會出現ORA-02429錯誤。

解決方法：删除表的限制。alter table tablename drop constraint con_name;

方法四、用exp工具導出整個表空間，删除表空間再重建家空間,最後再導入。

[oracle@shtdtest shtd]$ exp username/password@shtd file=users.dmp tablespaces=users

連接配接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

已導出 ZHS16GBK 字元集和 AL16UTF16 NCHAR 字元集

将導出所選的表空間...

用于表空間 SHTD...

. 正在導出群集定義

. 正在導出表定義

. . 正在導出表                  TB_CHK_DETAIL          0 行被導出

. . 正在導出表                    TB_CHK_ITEM          0 行被導出

. . 正在導出表                  TB_CHK_RESULT          0 行被導出

. . 正在導出表                      TB_CONTACT          0 行被導出

. . 正在導出表                    TB_CONTRACT          0 行被導出

. . 正在導出表                    TB_CUST_BIZ          0 行被導出

. . 正在導出表                    TB_CUST_INFO          0 行被導出

. . 正在導出表                TB_CUST_PROFILE          0 行被導出

. . 正在導出表                    TB_CUST_REG          0 行被導出

.

. 正在導出引用完整性限制條件

. 正在導出觸發器

在沒有警告的情況下成功終止導出。

SQL>drop tablespace shtd including contents and datafiles;

SQL>create tablespace shtd datafile '/data/shtd/SHTD01.dbf' size1024M EXTENT  MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

[oracle@shtdtest shtd]$  imp