How to drop tablespace with missing datafile?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to drop tablespace with missing datafile?

  1. #1
    Join Date
    Jan 2001
    Posts
    1
    Ok, ok ... I can hear you laughing already.

    I inadvertently deleted a datafile from a tablespace that I no longer need. Now I'd like to drop the tablespace it was associated with.

    Can I do it? And how?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    system@dev815nt.us> create tablespace xyz datafile 'd:\oracle\oradata\dev815nt\xyz01.dbf' size 32M;

    Tablespace created.

    system@dev815nt.us> host del d:\oracle\oradata\dev815nt\xyz01.dbf

    system@dev815nt.us> alter tablespace xyz offline;

    Tablespace altered.

    system@dev815nt.us> drop tablespace xyz including contents;

    Tablespace dropped.

    system@dev815nt.us> select file_name from dba_data_files;

    FILE_NAME
    ------------------------------------------------------
    D:\ORACLE\ORADATA\DEV815NT\SYSTEM01.DBF
    D:\ORACLE\ORADATA\DEV815NT\RBS01.DBF
    D:\ORACLE\ORADATA\DEV815NT\USERS01.DBF
    D:\ORACLE\ORADATA\DEV815NT\TEMP01.DBF
    D:\ORACLE\ORADATA\DEV815NT\TEST_DATA01.DBF

    system@dev815nt.us>
    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."

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Just bring the tablespace offline and you should be able to drop it !

    - Rajeev

  4. #4
    Join Date
    Sep 2000
    Posts
    155

    I think you could do:

    alter database datafile <datafile name> offline drop;



    If your database is unable to open due to this reason then,

    shutdown immediate
    startup mount
    alter database datafile '/app/oracle/oradata/CM/CMetable01.dbf' offline drop;
    alter database open;



    Hope this helps!

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Sorry I don't agree with you as for as general procedure to drop datafile is concerned with:

    Don't do/make a habbit (no matter tablespae has data/not) what rpardip suggested for god sake if you are in archive log mode. You might be at work whole night if you have data in the datafile. You can't bring back your tablespace online. it asks for recovery. Best is export contents of tablespace and then offline and drop, and rebuild the one you dropped.

    DROP TABLESPACE xyz OFFLINE INCLUDING CONTENTS ;

    General reminder, what we tend to do in hurry in day-to-day life. A big lesson I learned from it being at work 36 hours, unfortunately I didn't have good backup either.


    ***ORACLE NOTE:***
    The ALTER DATABASE DATAFILE <datafile name> 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.

    [Edited by sreddy on 01-23-2001 at 07:30 PM]

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