DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Resizing datafile!

  1. #1
    Join Date
    Dec 2002
    Location
    chennai
    Posts
    12

    Resizing datafile!

    Hai all,

    I tried to resize a datafile which was in autoextend mode.The file size is 2.5gb where as the contents of the file is only up to 1gb.So I tried to resize it to 1.5gb but I'm getting the error,
    ORA-03297: file contains used data beyond requested RESIZE value

    I tried to alter tablespace coalesce and then checked outwith dba_free_space.To my surprise there is no much change in the bytes and blocks of dba_free_space or dba_free_space_coalesced after coalescing.

    Kindly let me know the possibilities of resizing my datafile.

    regards,
    Niranjan.

  2. #2
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    As Mentioned in Oracle Documentation

    ORA-03297 file contains used data beyond requested RESIZE value

    Cause: Some portion of the file in the region to be trimmed is currently in use by a database object.

    Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.
    vishal sood
    OCP 8

  3. #3
    Join Date
    Dec 2002
    Location
    chennai
    Posts
    12
    Hai Vishal,

    I had a user before with default tablespace which is related to this datafile.now I changed that user to another tablespace and moved all its contents.So obviously there shouldn't be any problem now.After coalescing the tablespace surely the free space should be merged up and be summed up to fewer blocks,right?But why is that not happenning.
    why the coalesce is not showing any effect on my tablespace?
    Since the datafile size is 2.5gb,I tried to resize it very near ti its size like 2.2gb also but invain.help me.

    Regards,
    Niranjan.

  4. #4
    Join Date
    Jan 2003
    Location
    Delhi
    Posts
    63
    Script to find database object locations for a given datafile.

    http://metalink.oracle.com/metalink/...atabase_id=NOT

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Resizing datafile!

    Originally posted by niranjan
    Hai all,

    I tried to resize a datafile which was in autoextend mode.The file size is 2.5gb where as the contents of the file is only up to 1gb.So I tried to resize it to 1.5gb but I'm getting the error,
    ORA-03297: file contains used data beyond requested RESIZE value

    I tried to alter tablespace coalesce and then checked outwith dba_free_space.To my surprise there is no much change in the bytes and blocks of dba_free_space or dba_free_space_coalesced after coalescing.

    Kindly let me know the possibilities of resizing my datafile.

    regards,
    Niranjan.
    You cannot resize below the HWM of the file. This query will tell you what the HWM is for each file:

    set lines 256
    col "File Name" for A47
    column file_name format a40;
    column highwater format 9999999999;
    SELECT Substr(df.file_name,1,47) "File Name",
    Round(df.bytes/1024/1024,2) "Size (M)",
    Round(e.used_bytes/1024/1024,2) "Used (M)",
    Round(f.free_bytes/1024/1024,2) "Free (M)",
    round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
    FROM dba_data_files df,
    (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
    (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
    (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 e.file_id (+) = df.file_id
    AND df.file_id = f.file_id (+)
    AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
    ORDER BY
    df.tablespace_name, df.file_name
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Dec 2002
    Location
    chennai
    Posts
    12
    Thanx for your support Julian.So can we use deallocate unused clause to free up the space?but still I had already moved that whole user schema in to another tablespace.So what can be done to resize it?
    any other option?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width