How to add a deleted datafile again
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to add a deleted datafile again

  1. #1
    Join Date
    Feb 2005
    Posts
    10

    How to add a deleted datafile again

    The scenario is
    I have deleted a datafile belonging to a Tablespace say A using the command

    ALTER DATABASE DATAFILE 'a1.dbf' offline drop;

    The datafile exists in the same location .Its not physically deleted from the disk.

    Now i want to add the datafile again back to the Tablespace.

    Please help

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    how about this......

    ALTER TABLESPACE A
    ADD DATAFILE
    '/data/disk1/a1.dbf' SIZE 200M
    AUTOEXTEND ON NEXT 10M
    MAXSIZE 500M;

    Srini

  3. #3
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    NOTE: The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

    I m not sure if u can add the datafile so easily.

    if not, then u need to export all the objects in the datafile,

    drop the tablespace including contents,

    remove the datafile physcially depending on the OS

    re-create the tablespace, add the datafile and import the objects..

    check out oracle metalink Note:111316.1

    Srini

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    cant just drop a datafile from a tablespace, that tablespace is more than likely gone bye bye now.

    YOU will have to what the previous poster said. Although you may not be able to export the data if some of it was contained in that datafile

  5. #5
    Join Date
    Jan 2002
    Posts
    50
    Hi ,
    Try to do incomplete recovery by using backup control file or hot backup before you drop datafile.

    Then you can open the database with resetlog.

    Well I think this must work.
    You will loose some data , which u have added after droping the datafile. Otherwise this must work.


    Then take immediate complete backup of ur database.

    But this will work with database in archivelog mode only.

    Or You can try tablespace point in time recovery also.

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