http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [71]
02.File Book
File 자료실 [95]
Site Link [17]
개인폴더 [201]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [43]
DB2 [79]
ORACLE [3003]
PSQL [134]
기타정보 [155]
운영체제 [603]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
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


  LIST

제목 작성자 작성일 조회
Troubleshoot  ORA-03137: TTC    최길호 2018/04/24 1006965
WebLogic  weblogic cache    최길호 2018/02/28 236826
WebLogic  weblogic cluster session share [ 세션 공유 login.jsp login chk, weblogic.management.ManagementException: [Deployer:149188] ]    최길호 2018/03/03 124581
Troubleshoot  IMP-00003 ORA-32603: invalid FREEPOOLS LOB storage option value [ move lob table, lob index ]    최길호 2013/05/26 120212
Troubleshoot  proc, windows, vc 2003 , pcmake , Files\Microsoft은(는) 예상되지 않았습니다    최길호 2006/10/02 100237
Troubleshoot  dblink ORA-02068 ORA-03113 ORA-02080 [ ALTER SESSION CLOSE DATABASE LINK RDBNM; ]  [4]  최길호 2010/07/10 99028
Troubleshoot  ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1]    최길호 2021/09/23 98738
WebLogic   WEB-INF/lib 우선 적용, index-directory-enabled 인덱스 디렉토리    최길호 2018/02/27 97286
WebLogic  java -jar fmw_12.2.1.3.0_wls.jar The OpenJDK JVM is not supported on this platform.    최길호 2018/02/06 86589
Troubleshoot  TRIGGER,Function ORA-02069 ORA-06512 ORA-04088 ORA-02070    최길호 2008/12/19 86469
Script  session monitor [ dbms_xplan sql binds ]  [2]  최길호 2010/06/30 85384
Troubleshoot  ORA-03297 Resizing Empty Datafile    최길호 2009/08/11 84482
Admin  dd copy datafile raw device , filesystem  [4]  최길호 2009/09/07 81818
SQL,PL*SQL  dbms_random 사용예  [1]  최길호 2010/04/12 81272
Troubleshoot  ODBC {Microsoft ODBC for Oracle}, ORA-12154, ODBC NA000 IM006 01000  [1]  최길호 2008/01/04 77665
Troubleshoot  ORA-12537 , <unknown connect data> * 12537  [2]  최길호 2009/11/21 68001
Troubleshoot  ALTER SESSION CLOSE DATABASE LINK    최길호 2010/07/11 66182
Troubleshoot  ld: 0711-224 WARNING: Duplicate symbol: p_xargc    최길호 2010/06/08 65306
Admin  ORACLE과 MSSQL간의 DBLINK 방법    최길호 2008/09/19 65298
Tool  RAC jdbc connection test [ thin java test.java sample ]  [3]  최길호 2010/06/14 61889

    목록보기   다음페이지 1 [2][3][4][5][6][7][8][9][10]..[151]   [다음 10개]
       

Copyright 1999-2023 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2021/06/30
윈도우 서버에서 sqlplus .... ORA-...
by 최길호
2021/06/30
ORA-28500: connection from O...
by 최길호
2019/05/02
pkg 확인 SELECT * FROM AL...
by 최길호
최근 게시물
12/01
[ORACLE]
dgmgrl 운영, db11st h/w 교체 시나리....
by 최길호
12/01
[ORACLE]
DGMGRL> show configuration;....
by 최길호
11/23
[운영체제]
arch filesystem 100% full 예방 /....
by 최길호