ORA-03297 When Resize A Datafile [ID 578793.1]

郑全

 

ORA-03297 When Resize A Datafile [ID 578793.1]

转到底部

---

修改时间:2013-3-2 类型:PROBLEM 状态:MODERATED 优先级:3

注释 (0)

In this Document
  Symptoms
  Cause
  Solution
  References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 24-Nov-2011***

Symptoms

Receiving ORA-03297 when trying to resize a data file.
The recycle bin is purged.
The associated dba_segments output is empty. DBA_SEGMENTS returns no row selected for that datafile.

SQL> select segment_name from dba_segments where header_file=<file_num>;

no rows selected

 

Cause

The header_file column for the dba_segments stores the ID of the file containing the segment header, this column will only show segments whose header information is stored in this datafile.

The "no row selected" output means that none of the segments have their header information stored in this datafile which does not necessarily mean that there is no data in this datafile.
Therefore, the best way to check for data in the datafile is by using dba_extents.

The below test explains the behaviour.

SQL> select FILE_NAME, FILE_ID from dba_data_files where TABLESPACE_NAME='TEST';
FILE_NAME FILE_ID
------------------------------ ----------
C:\TEST1.DBF 5
C:\TEST2.DBF 6

SQL> create table test_seg(name char(100)) tablespace test;
Table created.

SQL> insert into test_seg values('Oracle');
1 row created.

-- > Insert many rows into the table test_seg, so that the segment will span datafiles i.e. both datafile 5 and 6 will contain data from test_seg.

SQL> commit;

Commit complete.

SQL> select segment_name from dba_segments where header_file=5;

no rows selected   <<-- this means that the header information of the segment is not stored in this datafile

SQL> select segment_name from dba_segments where header_file=6;

SEGMENT_NAME
--------------------------------------------------------------------------------
TEST_SEG


SQL> alter database datafile 'C:\test1.dbf' resize 1M;
alter database datafile 'C:\test1.dbf' resize 1M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

 

Solution

DBA_EXTENTS should be used to lists all the segments contained in that datafile.

Use the script provided in
Note 1029252.6 - How to Resize a Datafile to find database object locations for a given datafile

References

NOTE:1029252.6 - How to Resize a Datafile
NOTE:130866.1 - How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark