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