Truncate table 或者 drop table 收縮資料檔案,經常遇到ORA-03297: file contains used data beyond requested RESIZE value 查詢dba_free_space 也有空閒空間。經過查詢MOS(Doc ID 1029252.6)得知

If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.

Make sure you leave enough room in the datafile for importing the object back into the tablespace.

意思是說如果空閒的extent如果在檔案的中間,此時無法進行resize ,必須把尾部的object drop 然後重建再resize datafile。

以下是本人做的測試;

[oracle@bogon ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 31 11:10:41 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/orcl/test2.dbf' size 10M autoextend on next 1M;

Tablespace created.

SQL> create table tab1 tablespace test2 as select * from dba_objects;

Table created.

SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like '%test2%';

FILE# NAME BYTES

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

23 /u01/app/oracle/oradata/orcl/test2.dbf 11

SQL> create table tab2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like '%test2%';

FILE# NAME BYTES

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

23 /u01/app/oracle/oradata/orcl/test2.dbf 21

SQL> select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,blocks from dba_extents where file_id=23 order by BLOCK_ID;

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID BLOCKS

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

TAB1 23 0 9 8

TAB1 23 1 17 8

TAB1 23 2 25 8

TAB1 23 3 33 8

TAB1 23 4 41 8

TAB1 23 5 49 8

TAB1 23 6 57 8

TAB1 23 7 65 8

TAB1 23 8 73 8

TAB1 23 9 81 8

TAB1 23 10 89 8

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID BLOCKS

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

TAB1 23 11 97 8

TAB1 23 12 105 8

TAB1 23 13 113 8

TAB1 23 14 121 8

TAB1 23 15 129 8

TAB1 23 16 137 128

TAB1 23 17 265 128

TAB1 23 18 393 128

TAB1 23 19 521 128

TAB1 23 20 649 128

TAB1 23 21 777 128

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID BLOCKS

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

TAB1 23 22 905 128

TAB1 23 23 1033 128

TAB1 23 24 1161 128

TAB2 23 0 1289 8

TAB2 23 1 1297 8

TAB2 23 2 1305 8

TAB2 23 3 1313 8

TAB2 23 4 1321 8

TAB2 23 5 1329 8

TAB2 23 6 1337 8

TAB2 23 7 1345 8

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID BLOCKS

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

TAB2 23 8 1353 8

TAB2 23 9 1361 8

TAB2 23 10 1369 8

TAB2 23 11 1377 8

TAB2 23 12 1385 8

TAB2 23 13 1393 8

TAB2 23 14 1401 8

TAB2 23 15 1409 8

TAB2 23 16 1417 128

TAB2 23 17 1545 128

TAB2 23 18 1673 128

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID BLOCKS

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

TAB2 23 19 1801 128

TAB2 23 20 1929 128

TAB2 23 21 2057 128

TAB2 23 22 2185 128

TAB2 23 23 2313 128

TAB2 23 24 2441 128

50 rows selected.

Block_id 是連續的

SQL> truncate table tab1

2 ;

Table truncated.

SQL> select * from dba_free_space where file_id=23;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

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

TEST2 23 17 ########## 1272 23

TEST2 23 2569 ########## 120 23

有原來tab1 的free blocks 1272

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;

alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M

*

ERROR at line 1:

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

無法進行resize

下面把tab1 drop 再測試

SQL> drop table tab1 purge;

Table dropped.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;

alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M

*

ERROR at line 1:

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

依然報錯

然後truncate tab2 再進行測試

SQL> truncate table tab2;

Table truncated.

SQL> select * from dba_free_space where file_id=23;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

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

TEST2 23 9 ########## 1280 23

TEST2 23 1297 ########## 1392 23

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M;

alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M

*

ERROR at line 1:

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

此時只能收縮 tab2 的空間 但是不能收縮 tab1的空間

然後再drop tab2

SQL> drop table tab2 purge

2 ;

Table dropped.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 1M;

Database altered.

可以收縮tab1的空間

note:

收縮資料檔案和兩個因素有關

1 降低高水位

2 free extent在datafile 的尾部

本篇文章直接解釋了第二個

Alter database datafile resize ORA-03297 原因解析

意思是說如果空閒的extent如果在檔案的中間,此時無法進行resize ,必須把尾部的object drop 然後重建再resize datafile。

以下是本人做的測試;

相關文章

Alter database datafile resize ORA-03297 原因解析

意思是說如果空閒的extent如果在檔案的中間,此時無法進行resize ,必須把尾部的object drop 然後重建 再resize datafile。

以下是本人做的測試;

相關文章

意思是說如果空閒的extent如果在檔案的中間,此時無法進行resize ,必須把尾部的object drop 然後重建再resize datafile。