Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    28

    Unanswered: HOW TO DROP TABLESPACE if datafile is missing

    HOW CAN I DROP TABLESPACE IF A DATAFILE IS MISSING

    I have this kind of error

    SQL> drop tablespace MIKEVWTS1 including contents and datafiles ;
    drop tablespace MIKEVWTS1 including contents and datafiles
    *
    ERROR at line 1:
    ORA-01116: error in opening database file 14
    ORA-01110: data file 14: '/database/COEADWH/data11/MIKEVWTS101.dbf'
    ORA-27041: unable to open file
    SVR4 Error: 2: No such file or directory
    Additional information: 3

    Thank for your responses

    Sidnet

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    startup mount
    alter database datafile '/path/to/filename' offline drop;
    alter database open;
    drop tablespace tbsp_name including contents cascade constraints;

    HTH & YMMV

    HAND!

  3. #3
    Join Date
    Oct 2003
    Posts
    28

    IS THAT GOOD

    Is that doesn't drop the WHOLE DATABASE ???

    I just need to drop 1 tablespace in my database !!

    Originally posted by anacedent
    startup mount
    alter database datafile '/path/to/filename' offline drop;
    alter database open;
    drop tablespace tbsp_name including contents cascade constraints;

    HTH & YMMV

    HAND!

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: IS THAT GOOD

    Originally posted by Sidnet
    Is that doesn't drop the WHOLE DATABASE ???

    I just need to drop 1 tablespace in my database !!
    No, It will only drop the single tablespace, not the whole database.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •