How to Resize a Datafile，下面的测试来至于QQ群里一位好友，在resize修改数据文件报ORA-03297错误。数据库存放在文件系统的时候，可能会遇到需要使用resize数据文件来达到收缩文件系统使用空间，一般在小环境遇得到，大环境基本遇不到。下面是基于11.2.0.3平台测试，不同版本可能部分地方不一致(如数据文件头中的位图块个数)
1 环境介绍
|
www.cdhtz.com > 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
www.cdhtz.com > !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，创建数据文件与表
|
www.cdhtz.com > 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,并插入大量的数据 www.cdhtz.com > insert /*+ append */ into htz.htz select * from htz.htz; 74616 rows created. www.cdhtz.com > commit; Commit complete. ……………………………. www.cdhtz.com > insert /*+ append */ into htz.htz select * from htz.htz; 19101696 rows created. www.cdhtz.com > commit; Commit complete. 这里看到每一个数据文件占用的大小为2G www.cdhtz.com > 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 www.cdhtz.com > @segment.sql 查看HTZ段占用的大量，这里可以看到占用了4288M OWNER:SEGMENT_NAME SIZE(M) BLOCKS EXTENTS —————————— ———- ———- ——- HTZ.HTZ 4288 548864 250 ****************************** ———- Total: 4288 创建另一张表，使用的块位于resize保留的块之后 www.cdhtz.com > create table htz.htz1 tablespace users as select * from dba_objects; Table created. 将htz.htz表truncate，释放占用空间，这里不要使用delete删除数据，delete不释放空间。 www.cdhtz.com > truncate table htz.htz; Table truncated. |
3 RESIZE DATAFILE
|
查询每个数据文件对象占用空间大小，如果有表需要做shrink space，请提前完成 www.cdhtz.com > select file_id,sum(bytes) from dba_extents where tablespace_name=’USERS’ group by file_id;
FILE_ID SUM(BYTES) ———- ———- 6 5308416 4 4194304 查询数据文件中最用的最大块的位置 www.cdhtz.com > 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 www.cdhtz.com > select 290816*8192,259456*8192 from dual;
290816*8192 259456*8192 ———– ———– 2382364672 2125463552 这里可以看到已经分配给对象的块的最大位置都在2G以后。整个对象只占用了不到10M的空间，所以可以将数据库RESIZE来10M。 使用file_resize_check.sql脚本来检查那些对象分配的块的位置大于我们需要保存的大小 www.cdhtz.com > @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.
www.cdhtz.com > @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被阻塞， www.cdhtz.com > alter table htz.htz1 move; Table altered. 这里看到占用的块的最大位置已经降到了768块的位置 www.cdhtz.com > 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个块 www.cdhtz.com > 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 www.cdhtz.com > select 768*8192 from dual; 768*8192 ———- 6291456 www.cdhtz.com > select 5242880+128*8192 from dual; 5242880+128*8192 —————- 6291456 所以这里应该接6291456的大小 www.cdhtz.com > alter database datafile 4 resize 6291456; Database altered. www.cdhtz.com > alter database datafile 6 resize 6291456; Database altered.
下面再看看数据文件的大小，已经达到我们的预期了。 www.cdhtz.com > 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 |
how to resize a datafile for shrinking space：等您坐沙发呢！