oracle修改参数大小限制,【案例】Oracle修改文件大小报错ORA-03297的原因和解决办法...

【案例】Oracle修改文件大小报错ORA-03297的原因和解决办法

时间:2016-11-03 10:23   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

how to resize a datafile for shrinking space，Oracle研究中心案例分析：运维DBA反映在操作Oracle数据文件大小时遇到报错，在resize修改数据文件报ORA-03297错误无法执行完成。

在resize修改数据文件报ORA-03297错误。数据库存放在文件系统的时候，可能会遇到需要使用resize数据文件来达到收缩文件系统使用空间，一般在小环境遇得到，大环境基本遇不到。

下面是基于11.2.0.3平台测试，不同版本可能部分地方不一致(如数据文件头中的位图块个数)

1 环境介绍

oracleplus.net> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

oracleplus.net> !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

2，创建数据文件与表

oracleplus.net> select file_name,tablespace_name,file_id,bytes/1024/1024/1024 from dba_data_files where tablespace_name=’USERS’;

这里可以看到表空间下面有2个数据文件，占用空间都很少

FILE_NAME TABLESPACE_NAME FILE_ID BYTES/1024/1024/1024

———————————————— —————- ——– ——————–

/oracle/app/oracle/oradata/orcl1123/user02.dbf USERS 4 .009765625

/oracle/app/oracle/oradata/orcl1123/user01.dbf USERS 6 .009765625

创建测试表htz.htz,并插入大量的数据

oracleplus.net> insert /*+ append */ into htz.htz select * from htz.htz;

74616 rows created.

oracleplus.net> commit;

Commit complete.

…………………………….

oracleplus.net> insert /*+ append */ into htz.htz select * from htz.htz;

19101696 rows created.

oracleplus.net> commit;

Commit complete.

这里看到每一个数据文件占用的大小为2G

oracleplus.net> select file_name,tablespace_name,file_id,trunc(bytes/1024/1024/1024) from dba_data_files where tablespace_name=’USERS’;

FILE_NAME TABLESPACE_NAME FILE_ID TRUNC(BYTES/1024/1024/1024)

————————————————- ————— ———- —————————

/oracle/app/oracle/oradata/orcl1123/user02.dbf USERS 4 2

/oracle/app/oracle/oradata/orcl1123/user01.dbf USERS 6 2

oracleplus.net> @segment.sql

查看HTZ段占用的大量，这里可以看到占用了4288M

OWNER:SEGMENT_NAME SIZE(M) BLOCKS EXTENTS

—————————— ———- ———- ——-

HTZ.HTZ 4288 548864 250

****************************** ———-

Total: 4288

创建另一张表，使用的块位于resize保留的块之后

oracleplus.net> create table htz.htz1 tablespace users as select * from dba_objects;

Table created.

将htz.htz表truncate，释放占用空间，这里不要使用delete删除数据，delete不释放空间。

oracleplus.net> truncate table htz.htz;

Table truncated.

3 RESIZE DATAFILE

查询每个数据文件对象占用空间大小，如果有表需要做shrink space，请提前完成

oracleplus.net> select file_id,sum(bytes) from dba_extents where tablespace_name=’USERS’ group by file_id;

FILE_ID SUM(BYTES) Oracle oracleplus.net

———- ———-

6 5308416

4 4194304

查询数据文件中最用的最大块的位置

oracleplus.net> select file_id,max(block_id+blocks) from dba_extents where tablespace_name=’USERS’ group by file_id;

FILE_ID MAX(BLOCK_ID+BLOCKS)

———- ——————–

6 259456

4 290816

oracleplus.net> select 290816*8192,259456*8192 from dual;

290816*8192 259456*8192

———– ———–

2382364672 2125463552

这里可以看到已经分配给对象的块的最大位置都在2G以后。整个对象只占用了不到10M的空间，所以可以将数据库RESIZE来10M。

使用file_resize_check.sql脚本来检查那些对象分配的块的位置大于我们需要保存的大小

oracleplus.net> @file_resize_check.sql

Enter value for file_id: 4

old 6: V_FILE_ID := &FILE_ID;

new 6: V_FILE_ID := 4;

Enter value for resize_file_to: 5194304

old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;

new 7: V_RESIZE_SIZE := 5194304;

.

.

.

OBJECTS IN FILE 4 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 5194304 BYTES

===================================================================

NON-PARTITIONED OBJECTS

===================================================================

HTZ.HTZ1 – OBJECT TYPE = TABLE

===================================================================

PARTITIONED OBJECTS

===================================================================

PL/SQL procedure successfully completed.

oracleplus.net> @file_resize_check.sql

Enter value for file_id: 6

old 6: V_FILE_ID := &FILE_ID;

new 6: V_FILE_ID := 6;

Enter value for resize_file_to: 6308416

old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;

new 7: V_RESIZE_SIZE := 6308416;

.

.

.

OBJECTS IN FILE 6 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 6308416 BYTES

===================================================================

NON-PARTITIONED OBJECTS

===================================================================

HTZ.HTZ1 – OBJECT TYPE = TABLE

===================================================================

PARTITIONED OBJECTS

===================================================================

PL/SQL procedure successfully completed.

move表，这里需要注意的是move表导致其它的DML被阻塞，

oracleplus.net> alter table htz.htz1 move;

Table altered.

这里看到占用的块的最大位置已经降到了768块的位置

oracleplus.net> select file_id,max(block_id+blocks) from dba_extents where tablespace_name=’USERS’ group by file_id;

FILE_ID MAX(BLOCK_ID+BLOCKS)

———- ——————–

6 768

4 768

这里看到报ORA-03297:报错，因为我们只考虑了对象占用的空间大小，没有考虑数据文件头中的位图等块占用的大小，11G是128个块

oracleplus.net> alter database datafile 4 resize 5194304;

alter database datafile 4 resize 5194304

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

oracleplus.net> select 768*8192 from dual;

768*8192

———-

6291456

oracleplus.net> select 5242880+128*8192 from dual;

5242880+128*8192

—————-

6291456

所以这里应该接6291456的大小

oracleplus.net> alter database datafile 4 resize 6291456;

Database altered.

oracleplus.net> alter database datafile 6 resize 6291456;

Database altered.

下面再看看数据文件的大小，已经达到我们的预期了。

oracleplus.net> select file_name,tablespace_name,file_id,bytes/1024/1024/1024 from dba_data_files where tablespace_name=’USERS’;

FILE_NAME TABLESPACE_NAME FILE_ID BYTES/1024/1024/1024

———————————————– —————- ——– ——————–

/oracle/app/oracle/oradata/orcl1123/user02.dbf USERS 4 .005859375

/oracle/app/oracle/oradata/orcl1123/user01.dbf USERS 6 .005859375

本文固定链接: http://www.htz.pw/2014/11/18/how-ot-resize-a-datafile-for-shrinking-space.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle修改文件大小报错ORA-03297的原因和解决办法