Resize Datafile时ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

前些天做测试，为了图方便，在sys用户下建立了一张大表，插入的数据大概几亿条，导致system空间增长了30G左右，后面想删除数据，缩小system空间,因此truncate table，后面又drop table,但是此时只是降低了段的高水位，数据文件还有高水位，因此磁盘空间并没有被释放。

1、先查询可以利用的空闲空间

SQL> select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='SYSTEM';

SUM(BYTES/1024/1024/1024)

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

31.4642944

2、计算datafile可以resize收缩的空间，也就是必须剩余部分其他对象正在使用的空间

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,

ceil(HWM * a.block_size)/1024/1024 ResizeTo,

(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,

'alter database datafile '''||a.name||''' resize '||

ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD

from v$datafile a,

(select file_id,max(block_id+blocks-1) HWM

from dba_extents where file_id in

(select b.file# From v$tablespace a ,v$datafile b

where a.ts#=b.ts# and a.name='SYSTEM')

group by file_id) b

where a.file# = b.file_id(+)

and (a.bytes - HWM *block_size)>0

order by 5

FILE#

----------

NAME

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

CURRENTMB RESIZETO RELEASEMB

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

RESIZECMD

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

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

1024 545.5 478.5

alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' resize 546M;

3、直接收缩数据文件

SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF' RESIZE 546m;

正常情况下这样就可以了，但是我在计算datafile可以resize收缩的空间时，发现只能收缩至30G左右，如果收缩太小，出错

SQL> ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF' RESIZE 10240M;

第 1 行出现错误:

ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

文件也有高水位,不是你想RESIZE到多少就多少的,有些有效数据被切掉,当然是不允许的,但是除了原来那张大表，剩余的数据不可能占用怎么大的空间，所以估计是哪个表或其他对象还占用了大量空间

4、找到文件对应的文件号

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

FILE# NAME

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

1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

6 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\XTFDB\UNIEAP.DBF

7 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\XTFDB\NEUDOC.DBF

8 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\XTFDB\ELARP.DBF

9 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\XTFDB\SEAS.DBF

10 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\XTFDB\DLMIS.DBF

5、看下找到文件中最大的块号

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

SQL> select owner,segment_name,segment_type,tablespace_name,extent_id from dba_extents where block_id=(select max(block_id) from dba_extents);

owner segment_name segment_type tablespace_name extent_id

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

SYS C_OBJ#_INTCOL# CLUSTER SYSTEM 18

6、主要是C_OBJ#_INTCOL#这个聚簇段占用的块的位置的太大了，接下来要先查出这个聚簇段属于某张表

SQL> select * from dba_clu_columns where cluster_name='C_OBJ#_INTCOL#';

OWNER CLUSTER_NAME CLU_COLUMN_NAME TABLE_NAME TAB_COLUMN_NAME

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

SYS C_OBJ#_INTCOL# OBJ# HISTGRM$ OBJ#

SYS C_OBJ#_INTCOL# INTCOL# HISTGRM$ INTCOL#

可以看出来，是属于HISTGRM$表，HISTGRM$系统表，这个表是记录各个业务表的数据分布情况的，网络上查到其基本可以删除

7、截断c_obj#_intcol#

SQL> truncate cluster c_obj#_intcol#;truncate cluster c_obj#_intcol#

第 1 行出现错误:ORA-00701: 无法变更热启动数据库所需的对象

8、截断HISTGRM$表

SQL> truncate table HISTGRM$;

ORA-14512:不能对聚集对象进行操作

ORA-00701：无法改变热启动数据库所需的对象

9、使用move

SQL> ALTER TABLE HISTGRM$ MOVE;

第 1 行出现错误:

ORA-14512: 不能对聚簇对象进行操作

一样的错误

上网查：CLUSTER C_OBJ#_INTCOL#增长导致数据库的SYSTEM 表空间被大量占用，因为这个CLUSTER是一个BOOTSTRAP$对象。由于是BOOTSTRAP$对象，所以无法TRUNCATE.由于这个对象是251>56,因此不是核心BOOTSTRAP$对象，所以我们用得上EVENT 38003了。大意是修改一下event级别，然后重新启动后即可。

10、设置EVENT参数，重启数据库

SQL> alter system set EVENT="38003 trace name context forever, level 10" SCOPE=SPFILE;

系统已更改。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 100666244 bytes

Database Buffers 503316480 bytes

Redo Buffers 7135232 bytes

数据库装载完毕。

数据库已经打开。

11、重新截断cluster c_obj#_intcol#

SQL> truncate cluster c_obj#_intcol#;

簇已截断。

12、Resize datafile

SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' resize 548M;

数据库已更改。

13、测试，确保没有影响

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'emp', estimate_percent=>30, method_opt=>'for all indexed columns size skewonly',cascade=>true, degree=>2);

PL/SQL 过程已成功完成。

14、检查使用exp与expdp是否可以顺利导出数据

我的exp出现如下错误：

C:\Documents and Settings\Administrator>exp scott/tiger file=e:/ymhtest.dmp

EXP-00008: 遇到 ORACLE 错误 600

ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [],

. . 正在导出表 BONUS

EXP-00008: 遇到 ORACLE 错误 600

ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [],

. . 正在导出表 COMMIT

EXP-00008: 遇到 ORACLE 错误 600

ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [],