Skip to Content
avatar image
Former Member

How to decrease the size of datafile

Hi all

I want to resize my datafile. I want to reduce the size of my datafile. But when i try to do so through Brtools it gives error for any size i specify. Error is " ORA - 03297: File contains used data beyond requested RESIZE value."

Through DB02 when i checked it shows only 22% used but still it gives this error.

Can any one help me on this.

Thanks in advance.

Vitthal Prabhu

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Nov 28, 2007 at 10:17 AM

    Hi,

    in some cases you have to shutdown your database and after the startup the resize works.

    regards,

    /dirk

    Add comment
    10|10000 characters needed characters exceeded

    • No, the smallest size of a data file is 16K, so I don't think it is a factor in your situation.

      The real limit as some other pointed out is the "high watermark" let's explain it with an example:

      You have a 1Gb data file, at one point, you used 900M of it. but since then you deleted 700M of the data so now it's 200M used.

      In DB02 you will see the tablespace is 20% used. BUT you will only be able to reduce your file size to 900M not 200M. The reason being is the "high watermark" - the highest level the datafile has been filled in history is 900M. (Technically this means Oracle has had used 900M and blocks up to 900M are no longer clean, even if you deleted data, the blocks are marked "has been used" by Oracle and cannot be dropped).

      Think it another way, once you used 900M, and you deleted 700M, how can you be so sure that all the data deleted are the 201M to 900M? it is very possible that several block close to the end of the file is still actively used by the data while data in the middle of the datafile has been deleted. These space will be available for you to used (store new data) but you can't simple drop these blocks - which means you can't reduce the size of your datafile to exclude this space.

      So the real limit is the high-watermark which means the highest level you've ever used this data file.

  • avatar image
    Former Member
    Nov 28, 2007 at 10:03 AM

    Hi,

    you can use following script

    it's use the contents of dba_free_space and has been proven to be fastest than select from dba_extents.

    -


    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 -


    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 binary_integer;

    filesize binary_integer;

    extsize binary_integer;

    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.blocksblocksize);

    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 uptil: '||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 uptil: '||filesize

    ||' Bytes, Actual size: '||c_rec1.bytes);

    dbms_output.put_line('.');

    end if;

    end if;

    end loop;

    end;

    /

    -


    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 the above scripts.

    regards,

    kaushal

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 29, 2007 at 04:38 AM

    Really thanks to all of you for your replies.

    What i think i should reorganize the table to solve my proble. Table name is PSAPEC6

    Vitthal Prabhu

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Pardon me, the script above does that. I had tried a version of this recognizable code from another web source and it's syntax gave troubles. I got the above to work just fine.