[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / 幼い女性を性の奴隷にした帝國主義日本軍の?制性を公式に認め、謝罪しなければならない. ]



게시판 [71]
02.File Book
File 자료실 [96]
Site Link [14]
개인폴더 [200]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [47]
DB2 [79]
ORACLE [3061]
PSQL [135]
기타정보 [170]
운영체제 [607]
04.Q/A Book
Q/A [53]
방명록 [54]
레벨업 [37]
구인/구직 [2]

+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE


ORA-03297 Resizing Empty Datafile
최길호 [LIST]   2009-08-11 22:41:40, 조회 : 84,499

제목:  ORA-03297 Resizing Empty Datafile
  문서 ID:  415474.1 유형:  PROBLEM
  마지막 갱신 날짜:  08-APR-2008 상태:  PUBLISHED

Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.

When attempting to resize datafiles that are empty, the following error occurs:

ORA-03297 file contains <num> blocks of data beyond requested RESIZE value

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.

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:


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     Remarks:        minimal size of a datafile is 2 Oracle blocks
rem                     resizing should always be a multiple of Oracle blocks
rem     Requirements:   select on sys.dba_data_files
rem                     select on sys.dba_free_space
rem                     select on sys.v_$parameter
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     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     --------------------------------------------------------------------

set serveroutput on
execute dbms_output.enable(2000000);

  cursor c_dbfile is
        select  tablespace_name
        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;


/* 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
    filesize := c_rec1.bytes;
    for c_rec2 in c_space(c_rec1.file_id)
      extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
      if extsize = filesize
        filesize := (c_rec2.block_id - 1)*blocksize;
        /* 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
      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.')
      if filesize < 2*blocksize
        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('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);
      end if;
    end if;
  end loop;

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:
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
       ,(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.

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;

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.


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

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

Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
윈도우 서버에서 sqlplus .... ORA-...
by 최길호
ORA-28500: connection from O...
by 최길호
pkg 확인 SELECT * FROM AL...
by 최길호
최근 게시물
ORA-01722: invalid number [ fi....
by 최길호
아두이노 카카오톡.
by 최길호
ORA-00600: internal error code....
by 최길호