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

Thread: datafile resize

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    Lightbulb datafile resize

    Hi guys,

    how do i reset size of data file.currently for example temp and undo tablspace have lot of data files around 7 files each , where the utilization is very minimal.

    Some other tablespace don't have any space left.the Drive on which all these tablespace are also full.the only option left for me resize couple of data files on various table spaces and assign this to the required ones.

    when i try to resize it gives me an error, there is data in this you cannot resize .

    if we resize will be there be any chance of DB crash or data lost, the reason asking is, it's production.

    Pls help.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    give an example of what you did, with error messages.

    Nut if there is data above the point you are trying to resize to then it will fail

  3. #3
    Join Date
    Nov 2000
    Location
    San Diego
    Posts
    24

    Take the datafiles offline and drop them.

    Hi Prodadmin,

    If there is data in the data files and you try to resize(reduce) it some times it does give you error. Infact I tried once and it did not give me error and the database crashed and I have to recover it from the backups. Of course the file I was resizing was from a data tablespace not from TEMP or UNDO tablespace. Since that incedence I don't dare to do the resizing act in my life again, but I have seen some DBAs are comfortable doing it.

    In your case you can do as follows (for 8i):

    1. Do this in the maintenace window not when users are connected.

    2. Stop and start the database.

    3. Force an archive log. (Preferable SQL > ARCHIVE LOG LIST ; )

    3. Stop the database and take a cold backup and start it again.

    4. Identify the databafiles to be removed from the TEMP/UNDO tablespaces.

    5. Take each datafile (that you want to drop) offline and drop them. The command should be similar (not same) to the command shown below.
    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

    6. The above procedure just delinks the datafiles from oracle tablespaces but they sill remain in the filesystem (unless you are using OMF feature of 9i).

    7. Delete the files from the OS.

    8. Stop the database, take a cold backup and restart the database.


    Note:
    MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
    MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
    MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
    MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
    MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.


    -Hidayath.
    Last edited by Hidayath; 05-14-2004 at 06:37 PM.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    your crash wasnt due to resizing a datafile - thats a perfectly normal thing to do and used everywhere

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