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

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

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 的尾部

本篇文章直接解释了第二个