|
|
IOTN :: Field Book :: ORACLE 
|
|
ORA-03297 Resizing Empty Datafile
|
|
최길호
[LIST]
|
2009-08-11 22:41:40, 조회 : 84,482 |
제목: ORA-03297 Resizing Empty Datafile
문서 ID: 415474.1 유형: PROBLEM
마지막 갱신 날짜: 08-APR-2008 상태: PUBLISHED
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8.0
This problem can occur on any platform.
Symptoms
When attempting to resize datafiles that are empty, the following error occurs:
ORA-03297 file contains <num> blocks of data beyond requested RESIZE value
Cause
A file header dump reveals that the header still contains information in the file when
in fact there is not. This is potentially Bug 5361778. Therefore, the file header needs to be rebuilt before the resizecan take place.
Solution
1. Generate a trace file with the file header dump. This will generate 2 trace files in the directory defined by the user_dump_dest parameter.
SQL> alter session set tracefile_identifier='file_hdr';
SQL> alter Session Set Events 'immediate trace name file_hdrs level 10';
SQL> alter session set tracefile_identifier='block';
SQL> alter system dump datafile '/u04/oradata/AUSADEV/psindex_05.dbf' block min 1 block max 10;
SQL> exit (important step)
2. The following fixes the problem:
SQL> exec dbms_space_admin.TABLESPACE_REBUILD_BITMAPS ('<tablespace_name>');
SQL> alter database datafile '/u04/oradata/AUSADEV/psindex_05.dbf' resize 5800M;
=================================================================================================
제목: How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark
문서 ID: 130866.1 유형: PROBLEM
마지막 갱신 날짜: 10-OCT-2008 상태: PUBLISHED
Problem Description
-------------------
In a number of cases it is desired to shrink an oversized datafile. The
problem is then up to which size can the datafile be shrunk? Using the trial
and error approach will certainly give:
SQL> alter database datafile '/ots3/oradata/v817/oradata/v817/temp01.dbf'
2 resize 433000;
alter database datafile '/ots3/oradata/v817/oradata/v817/temp01.dbf'
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
This is because the High Water Mark (HWM) of the datafile, which means
'the largest size it has ever been', had been exceeded.
Solution Description
--------------------
Two options will be described to find the highwatermark of a datafile.
1. The first one will use the contents of dba_free_space and has been proven
to be fastest:
Disclaimer:
===========
This script is provided for educational purposes only. It is NOT supported by
Oracle World Wide Technical Support. The script has been tested and appears
to work as intended. However, you should always test any script before
relying on it.
============
rem Subject: Calculation of HighwaterMark of datafiles
rem
rem Remarks: minimal size of a datafile is 2 Oracle blocks
rem resizing should always be a multiple of Oracle blocks
rem
rem Requirements: select on sys.dba_data_files
rem select on sys.dba_free_space
rem select on sys.v_$parameter
rem
rem
rem It may be necessary to change declarations of filesize and extsize
rem to NUMBER instead of binary_integer. Binary Integer can take up
rem values from -2147483647 to +2147483647.
rem
rem If you have a datafile of size 2GB and larger then size of the file
rem is 2147483648 bytes and larger and thus it can not be stored in
rem a binary integer. The respective variables could be defined
rem as number instead.
rem
rem --------------------------------------------------------------------
set serveroutput on
execute dbms_output.enable(2000000);
declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !='INVALID'
order by tablespace_name,file_id;
cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;
blocksize number;
filesize number;
extsize number;
begin
/* get the blocksize of the database, needed to calculate the startaddress */
select value
into blocksize
from v$parameter
where name = 'db_block_size';
/* retrieve all datafiles */
for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<<outer>>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized to: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/
Output from option Number 1:
Tablespace: BIGPARTYWEEK1 Datafile: /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf
Can be resized uptil: 49152 Bytes, Actual size: 1040384
.
Tablespace: IFS_LOB_N Datafile: /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf
Can not be resized, no free space at end of file.
.
Tablespace: IFS_MAIN Datafile: /ots3/oradata/v817/oradata/v817/ifs_main.dbf
Can be resized uptil: 23027712 Bytes, Actual size: 52428800
.
Tablespace: INDX Datafile: /ots2/oradata/v817/oradata/v817/indx01.dbf
Can be resized uptil: 16384 Bytes, Actual size: 56623104 Bytes
.
Tablespace: OEM_REPOSITORY Datafile:
/ots1/oradata/v817/oradata/v817/oem_repository.dbf
Can not be resized, no free space at end of file.
.
2. The second option is to select from dba_extents (most likely to be much
slower if many objects are present in the database):
column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999
select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/
Output from option Number 2:
TABLESPACE_NAME FILE_NAME HIGHWATER
--------------- -------------------------------------------------- -----------
BIGPARTYWEEK1 /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf 49152
IFS_LOB_N /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf 157294592
IFS_MAIN /ots3/oradata/v817/oradata/v817/ifs_main.dbf 23027712
OEM_REPOSITORY /ots1/oradata/v817/oradata/v817/oem_repository.dbf 56492032
RBS /ots1/oradata/v817/oradata/v817/rbs01.dbf 230170624
Important Note:
============
When you create a locally managed tablespace with a uniform size of 160K for example, then the formula in the script:
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
Indicates that the datafile can not be resized although it is possible to resize the datafile to a lower value.
Testcase:
>>>
SQL> create tablespace test_ok
datafile 'test_ok01.dbf' size 10M
extent management local
uniform size 32K
segment space management auto;
SQL> create tablespace test_bad
datafile 'test_bad01.dbf' size 10M
extent management local
uniform size 160K
segment space management auto;
<<<
Explanation
-----------
When trying to resize beyond the highwatermark of the datafile an ORA-3297 is
returned. The problem is what is the highwatermark because the error is not
showing it. Resizing up to the minimum is in one step possible using one of
the above scripts.
|
3.235.60.197
|
|
|
|
Copyright 1999-2023 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
12/01 