对表空间数据文件进行resize时提示ORA-03297报错。该报错的主要原因是回收数据文件大小时，resize的值太小不能满足该表空间中的已有的对象。
测试过程如下
1、创建测试表空间
14:31:43 TEST@db1(11g)> create tablespace test datafile '/oracle/app/db/oradata/db1/test.dbf' size 100m extent management local uniform size 1m;
Tablespace created.
14:31:47 TEST@db1(11g)>
2、创建测试用户
14:33:13 SYS@db1(11g)> create user test identified by test;
User created.
14:33:18 SYS@db1(11g)> grant dba to test;
Grant succeeded.
14:33:22 SYS@db1(11g)> alter user test default tablespace test;
User altered.
14:33:25 SYS@db1(11g)> connect test/test;
Connected.
14:33:28 TEST@db1(11g)> 3、创建测试表
14:33:28 TEST@db1(11g)> BEGIN
14:34:55 2 for i in 1..9 LOOP
14:34:55 3 execute immediate 'create table ' || 'TEST'||i ||' as select * from dba_objects';
14:34:55 4 end loop;
14:34:55 5 end;
14:34:55 6 /
PL/SQL procedure successfully completed.
14:34:58 TEST@db1(11g)> 4、查看数据文件未使用大小
14:34:58 TEST@db1(11g)> select sum(bytes) from dba_free_space where file_id=5;
SUM(BYTES)
----------
9437184
14:35:44 TEST@db1(11g)> 可以看到，free的空间9M左右，数据文件总大小为100M。
5、查看数据文件占用大小对象排序
14:39:03 TEST@db1(11g)> SET ECHO OFF
14:39:13 TEST@db1(11g)> SET PAGESIZ 25
14:39:13 TEST@db1(11g)>
14:39:13 TEST@db1(11g)> column file_name format a50
14:39:13 TEST@db1(11g)> select file_name, file_id from dba_data_files order by 2;
Segment Extent Summary
FILE_NAME FILE_ID
-------------------------------------------------- ----------
/oracle/app/db/oradata/db1/system01.dbf 1
/oracle/app/db/oradata/db1/sysaux01.dbf 2
/oracle/app/db/oradata/db1/undotbs01.dbf 3
/oracle/app/db/oradata/db1/users01.dbf 4
/oracle/app/db/oradata/db1/test.dbf 5
14:39:13 TEST@db1(11g)>
14:39:13 TEST@db1(11g)> col ownr format a8 heading 'Owner' justify c
14:39:13 TEST@db1(11g)> col type format a8 heading 'Type' justify c trunc
14:39:13 TEST@db1(11g)> col name format a30 heading 'Segment Name' justify c
14:39:13 TEST@db1(11g)> col exid format 990 heading 'Extent#' justify c
14:39:13 TEST@db1(11g)> col fiid format 9990 heading 'File#' justify c
14:39:13 TEST@db1(11g)> col blid format 99990 heading 'Block#' justify c
14:39:13 TEST@db1(11g)> col blks format 999,990 heading 'Blocks' justify c
14:39:13 TEST@db1(11g)>
14:39:13 TEST@db1(11g)> select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
14:39:13 2 from dba_extents
14:39:13 3 where file_id = &file_id
14:39:13 4 order by block_id
14:39:13 5 /
Enter value for file_id: 5
old 3: where file_id = &file_id
new 3: where file_id = 5
Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
TEST TEST1 TABLE 0 5 128 128
TEST TEST1 TABLE 1 5 256 128
TEST TEST1 TABLE 2 5 384 128
TEST TEST1 TABLE 3 5 512 128
TEST TEST1 TABLE 4 5 640 128
TEST TEST1 TABLE 5 5 768 128
TEST TEST1 TABLE 6 5 896 128
TEST TEST1 TABLE 7 5 1024 128
TEST TEST1 TABLE 8 5 1152 128
TEST TEST1 TABLE 9 5 1280 128
TEST TEST2 TABLE 0 5 1408 128
TEST TEST2 TABLE 1 5 1536 128
TEST TEST2 TABLE 2 5 1664 128
TEST TEST2 TABLE 3 5 1792 128
TEST TEST2 TABLE 4 5 1920 128
TEST TEST2 TABLE 5 5 2048 128
TEST TEST2 TABLE 6 5 2176 128
TEST TEST2 TABLE 7 5 2304 128
TEST TEST2 TABLE 8 5 2432 128
TEST TEST2 TABLE 9 5 2560 128
TEST TEST3 TABLE 0 5 2688 128
TEST TEST3 TABLE 1 5 2816 128
TEST TEST3 TABLE 2 5 2944 128
TEST TEST3 TABLE 3 5 3072 128
TEST TEST3 TABLE 4 5 3200 128
TEST TEST3 TABLE 5 5 3328 128
TEST TEST3 TABLE 6 5 3456 128
TEST TEST3 TABLE 7 5 3584 128
TEST TEST3 TABLE 8 5 3712 128
TEST TEST3 TABLE 9 5 3840 128
TEST TEST4 TABLE 0 5 3968 128
TEST TEST4 TABLE 1 5 4096 128
TEST TEST4 TABLE 2 5 4224 128
TEST TEST4 TABLE 3 5 4352 128
TEST TEST4 TABLE 4 5 4480 128
TEST TEST4 TABLE 5 5 4608 128
TEST TEST4 TABLE 6 5 4736 128
TEST TEST4 TABLE 7 5 4864 128
TEST TEST4 TABLE 8 5 4992 128
TEST TEST4 TABLE 9 5 5120 128
TEST TEST5 TABLE 0 5 5248 128
TEST TEST5 TABLE 1 5 5376 128
TEST TEST5 TABLE 2 5 5504 128
TEST TEST5 TABLE 3 5 5632 128
TEST TEST5 TABLE 4 5 5760 128
TEST TEST5 TABLE 5 5 5888 128
TEST TEST5 TABLE 6 5 6016 128
TEST TEST5 TABLE 7 5 6144 128
TEST TEST5 TABLE 8 5 6272 128
TEST TEST5 TABLE 9 5 6400 128
TEST TEST6 TABLE 0 5 6528 128
TEST TEST6 TABLE 1 5 6656 128
TEST TEST6 TABLE 2 5 6784 128
TEST TEST6 TABLE 3 5 6912 128
TEST TEST6 TABLE 4 5 7040 128
TEST TEST6 TABLE 5 5 7168 128
TEST TEST6 TABLE 6 5 7296 128
TEST TEST6 TABLE 7 5 7424 128
TEST TEST6 TABLE 8 5 7552 128
TEST TEST6 TABLE 9 5 7680 128
TEST TEST7 TABLE 0 5 7808 128
TEST TEST7 TABLE 1 5 7936 128
TEST TEST7 TABLE 2 5 8064 128
TEST TEST7 TABLE 3 5 8192 128
TEST TEST7 TABLE 4 5 8320 128
TEST TEST7 TABLE 5 5 8448 128
TEST TEST7 TABLE 6 5 8576 128
TEST TEST7 TABLE 7 5 8704 128
TEST TEST7 TABLE 8 5 8832 128
TEST TEST7 TABLE 9 5 8960 128
TEST TEST8 TABLE 0 5 9088 128
TEST TEST8 TABLE 1 5 9216 128
TEST TEST8 TABLE 2 5 9344 128
TEST TEST8 TABLE 3 5 9472 128
TEST TEST8 TABLE 4 5 9600 128
TEST TEST8 TABLE 5 5 9728 128
TEST TEST8 TABLE 6 5 9856 128
TEST TEST8 TABLE 7 5 9984 128
TEST TEST8 TABLE 8 5 10112 128
TEST TEST8 TABLE 9 5 10240 128
TEST TEST9 TABLE 0 5 10368 128
TEST TEST9 TABLE 1 5 10496 128
TEST TEST9 TABLE 2 5 10624 128
TEST TEST9 TABLE 3 5 10752 128
TEST TEST9 TABLE 4 5 10880 128
TEST TEST9 TABLE 5 5 11008 128
TEST TEST9 TABLE 6 5 11136 128
TEST TEST9 TABLE 7 5 11264 128
TEST TEST9 TABLE 8 5 11392 128
TEST TEST9 TABLE 9 5 11520 128
90 rows selected.
14:39:18 TEST@db1(11g)> 6、删除TEST（2-8）之间的表
14:41:28 TEST@db1(11g)> BEGIN
14:41:30 2 for i in 2..8 LOOP
14:41:30 3 execute immediate 'DROP table ' || 'TEST'||i;
14:41:30 4 end loop;
14:41:30 5 end;
14:41:30 6 /
PL/SQL procedure successfully completed.
14:41:30 TEST@db1(11g)> 7、再次确认数据文件free大小
14:41:30 TEST@db1(11g)> select sum(bytes) from dba_free_space where file_id=5;
SUM(BYTES)
----------
82837504
14:42:13 TEST@db1(11g)> 可以看到，删除表TEST2～TEST8之后，数据文件的free大小为80M左右，数据文件本身大小为100M
14:43:05 TEST@db1(11g)> !ls -ltr /oracle/app/db/oradata/db1/test.dbf
-rw-r-----. 1 oracle oinstall 104865792 Mar 22 14:40 /oracle/app/db/oradata/db1/test.dbf
14:43:15 TEST@db1(11g)> 8、resize数据文件
数据文件大小100M，现在free 80M左右， 实际使用大小为20M左右， 如果现在resize到50M，理论上肯定是够用的，但是操作时会报错。
14:44:37 TEST@db1(11g)> alter DATABASE DATAFILE '/oracle/app/db/oradata/db1/test.dbf' resize 50M;
alter DATABASE DATAFILE '/oracle/app/db/oradata/db1/test.dbf' resize 50M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
14:44:48 TEST@db1(11g)> 9、查询数据文件中最大的block_id
14:45:21 TEST@db1(11g)> select max(BLOCK_ID) from dba_extents where file_id = 5;
MAX(BLOCK_ID)
-------------
11520
14:45:38 TEST@db1(11g)> 换算成大小 11520*8/1024 MB
14:45:54 TEST@db1(11g)> select 11520*8/1024 from dual;
11520*8/1024
------------
90
14:46:02 TEST@db1(11g)> 所以现在根据最大的block_id , resize的大小不能低于90M，否则都会报ORA-03297。
14:46:02 TEST@db1(11g)> alter DATABASE DATAFILE '/oracle/app/db/oradata/db1/test.dbf' resize 89M;
alter DATABASE DATAFILE '/oracle/app/db/oradata/db1/test.dbf' resize 89M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
14:48:25 TEST@db1(11g)> 10、计算哪些对象导致了resize失败
14:48:25 TEST@db1(11g)> -- SHRINK_DATAFILE.SQL
14:49:33 TEST@db1(11g)>
14:49:33 TEST@db1(11g)> -- This script lists the object names and types that must be moved in order to resize a datafile to a specified smaller size
14:49:33 TEST@db1(11g)>
14:49:33 TEST@db1(11g)> -- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
14:49:33 TEST@db1(11g)> -- Size in bytes that the datafile will be resized to
14:49:33 TEST@db1(11g)>
14:49:33 TEST@db1(11g)> SET SERVEROUTPUT ON
14:49:33 TEST@db1(11g)>
14:49:33 TEST@db1(11g)> DECLARE
14:49:33 2 V_FILE_ID NUMBER;
14:49:33 3 V_BLOCK_SIZE NUMBER;
14:49:33 4 V_RESIZE_SIZE NUMBER;
14:49:33 5 BEGIN
14:49:33 6 V_FILE_ID := &FILE_ID;
14:49:33 7 V_RESIZE_SIZE := &RESIZE_FILE_TO;
14:49:33 8
14:49:33 9 SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
14:49:33 10
14:49:33 11 DBMS_OUTPUT.PUT_LINE('.');
14:49:33 12 DBMS_OUTPUT.PUT_LINE('.');
14:49:33 13 DBMS_OUTPUT.PUT_LINE('.');
14:49:33 14 DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');
14:49:33 15 DBMS_OUTPUT.PUT_LINE('===================================================================');
14:49:33 16 DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
14:49:33 17 DBMS_OUTPUT.PUT_LINE('===================================================================');
14:49:33 18
14:49:33 19 for my_record in (
14:49:33 20 SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
14:49:33 21 FROM DBA_EXTENTS
14:49:33 22 WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
14:49:33 23 AND FILE_ID = V_FILE_ID
14:49:34 24 AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
14:49:34 25 ORDER BY 1) LOOP
14:49:34 26 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
14:49:34 27 END LOOP;
14:49:34 28
14:49:34 29 DBMS_OUTPUT.PUT_LINE('===================================================================');
14:49:34 30 DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
14:49:34 31 DBMS_OUTPUT.PUT_LINE('===================================================================');
14:49:34 32
14:49:34 33 for my_record in (
14:49:34 34 SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
14:49:34 35 FROM DBA_EXTENTS
14:49:34 36 WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
14:49:34 37 AND FILE_ID = V_FILE_ID
14:49:34 38 AND SEGMENT_TYPE LIKE '%PARTITION%'
14:49:34 39 ORDER BY 1) LOOP
14:49:34 40 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
14:49:34 41 END LOOP;
14:49:34 42
14:49:34 43 END;
14:49:34 44 /
Enter value for file_id: 5
old 6: V_FILE_ID := &FILE_ID;
new 6: V_FILE_ID := 5;
Enter value for resize_file_to: 50000000
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 50000000;
.
.
.
OBJECTS IN FILE 5 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 50000000 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
TEST.TEST9 - OBJECT TYPE = TABLE
===================================================================
PARTITIONED OBJECTS
===================================================================
PL/SQL procedure successfully completed.
14:49:53 TEST@db1(11g)> 其中50000000大小代表字节，意思就是想把file_id = 5的数据文件resize到50000000字节，这个时候TEST.TEST9 表会导致resize失败，这个时候需要把TEST9 move到其他表空间或者在本表空间move，move的提前是TEST表空间drop的对象需要从回收站删除，否则move之后block_id是不会变化或者变化很小，如下：
14:53:29 TEST@db1(11g)> select block_id, segment_name from dba_extents where file_id = 5;
BLOCK_ID SEGMENT_NAME
---------- -----------------------------
128 TEST1
256 TEST1
384 TEST1
512 TEST1
640 TEST1
768 TEST1
896 TEST1
1024 TEST1
1152 TEST1
1280 TEST1
10368 TEST9
10496 TEST9
10624 TEST9
10752 TEST9
10880 TEST9
11008 TEST9
11136 TEST9
11264 TEST9
11392 TEST9
11520 TEST9
20 rows selected.
14:53:58 TEST@db1(11g)> alter table test9 move;
Table altered.
14:54:11 TEST@db1(11g)> select block_id, segment_name from dba_extents where file_id = 5;
BLOCK_ID SEGMENT_NAME
---------- -----------------------------
128 TEST1
256 TEST1
384 TEST1
512 TEST1
640 TEST1
768 TEST1
896 TEST1
1024 TEST1
1152 TEST1
1280 TEST1
11648 TEST9
11776 TEST9
11904 TEST9
12032 TEST9
12160 TEST9
12288 TEST9
12416 TEST9
12544 TEST9
12672 TEST9
1408 TEST9
20 rows selected.
14:54:17 TEST@db1(11g)> 清除回收站之后，再次move
14:55:41 TEST@db1(11g)> purge recyclebin;
Recyclebin purged.
14:55:48 TEST@db1(11g)> alter table test9 move;
Table altered.
14:55:52 TEST@db1(11g)> select block_id, segment_name from dba_extents where file_id = 5;
BLOCK_ID SEGMENT_NAME
---------- -----------------------------
1536 TEST9
1664 TEST9
1792 TEST9
1920 TEST9
2048 TEST9
2176 TEST9
2304 TEST9
2432 TEST9
2560 TEST9
2688 TEST9
128 TEST1
256 TEST1
384 TEST1
512 TEST1
640 TEST1
768 TEST1
896 TEST1
1024 TEST1
1152 TEST1
1280 TEST1
20 rows selected.
14:55:56 TEST@db1(11g)> 11、清空回收站并move最大block_id块之后，再次resize数据文件可以成功
14:55:56 TEST@db1(11g)> alter DATABASE DATAFILE '/oracle/app/db/oradata/db1/test.dbf' resize 50M;
Database altered.
14:57:11 TEST@db1(11g)> 总结：
resize时会判断该文件最大的block_id, 如果resize的值小于最大block_id*8 (k)，那么resize会失败的，根据最大block_id 查到属于哪个对象，然后move对象，move前需要清空回收站，否则不会释放太多空间。
参考资料：
| How to Resize a Datafile (Doc ID 1029252.6) |
1911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
