DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: deleting datafile

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi, Welcome to new year of challenges,

    Can we delete Datafile without dropping the tablespace?

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    No. You have to empty the tablespace, drop the tablespace, and then recreate the tablespace.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    191
    Yes. You can drop datafile without dropping tablespace.

    alter database datafile '' offline drop;

    database has to be in mount mode.


  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    XYZ is right... A datafile can be dropped without dropping tablespace.

    Sanjay

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Yes is correct you can drop a datafile without drop the tablespace, but you must verify that the datafile is empty.

    Regards

    Angel

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot drop a datafile from a tablespace unless you are gonna drop the entire tablespace, if you do offline drop that really is

    1. I am preparing to drop the file
    2. After drop this file I will proceed to drop the tablespace

    Add a datafile is easy, drop one is not possible without dropping the tablespace.

    Btw offline drop is normally used in database in noarchive log where you have lost a datafile and you want to start the database without getting error (otherwise you will get an error message complaining about missing datafile)

    refer to back & recovery doco if you have further doubts

  7. #7
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    So, What's the final conclusion for the database running in Archive log mode?

    Pl. clear...

    Thanks to all
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    This is the problem with free advice, it's worth what you pay. I suggest you try it in your development environment to see if you get the expected result (which I am sure you won't).
    Jeff Hunter

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well even in archive log mode you cannot delete a datafile from a tablespace, this is not dependant on archive or noarchive just that in archive log mode you can do alter database datafile xxxx offline but it´s not possible in noarchive, you are forced to do offline drop, in either case you have to get rid of whole tablespace anywayz

  10. #10
    Join Date
    Jan 2001
    Posts
    191
    You can delete a datafile from tablespace without delete tablespace. After run
    alter database datafile 'xxx' offline drop;
    You only delete the datafile from your control file, you can use os to delete datafile and later you still can open the database. Keep in mind that datafile name is still in dictionary view. You can't use add datafile to add this datafile anymore. But you can make it online again (if you did not delete datafile in OS). By the way, database objects (such as tables, indexes) will be in any of theses datafiles in this tablespace, if you have any data in this tablespace which have more than one datafiles, even you only dedete one datafile, you may lost your data. If you can lost any data in this tablesace, then you should drop your tablesapce and recreate it later. Then database control file and database dictionary view will give you same information about data files.

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