Resizing Datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Resizing Datafiles

  1. #1
    Join Date
    Mar 2001
    Posts
    131

    Question

    Hi Friends,

    Can Someone tell me what will be the implications of Resizing the datafiles if i decrease the existing size of the datafile ? I repeat I want to DECREASE the file size.

    Have told by some one Oracle does not recommand to decrease the datafile size. Can someone show me link or docs where written like this ?

    Thanks in advance.

    UpeshP

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    You cannot decrease the size of an existing datafile..only option u have is to re-create a new datafile with the new size...

    For doc...check oracle documentation and look for managing datafiles in the Oracle Administration guide...

    regards
    anandkl
    anandkl

  3. #3
    Join Date
    Nov 1999
    Posts
    226

    See This

    Your database crashed with the following errors after you issued the command:

    > ALTER DATABASE DATAFILE RESIZE...

    Datafile is ONLINE, and in a tablespace that is being used. In this
    case the rollback segment tablespace.

    ORA-00603: "ORACLE server session terminated by fatal error"
    Cause: An ORACLE server session is in an unrecoverable state.
    Action: Login to ORACLE again so a new server session will be
    created

    ORA-00376: "file %s cannot be read at this time"
    Cause: attempting to read from a file that is not readable.
    Most likely the file is offline.
    Action: Check the state of the file. Bring it online

    ORA-01110: datafile :
    Cause: This message reports the filename involved with other messages.
    Action: See the associated messages for a description of the problem.


    Problem Explanation:
    ====================

    When using command ALTER DATABASE DATAFILE RESIZE..., your instance should
    have the database mounted, open, or closed, and the files involved should
    NOT be in use.

    With rollback segments being used in this ONLINE datafile, attempts to resize
    it could cause a crash and even corruption.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by anandkl
    You cannot decrease the size of an existing datafile..only option u have is to re-create a new datafile with the new size...
    This is not true.

    You can decrease the size of a data file as long as you don't try to shrink it past the point where it contains data.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2000
    Posts
    119
    Let me elaborate on this. as marist89 has said you can decrease the datafile size. Also I do not recall whether oracle 'Recommends' not to reduce datafile size. You can shrink datafile till the point data has spread in the file.
    OCP 8.0, 8i, 9i

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