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：等您坐沙发呢！