DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001

    I'm running Oracle 8.1.5 on Linux.

    I created a new tablespace today:

    sqlplus> ALTER TABLESPACE INDEX001 add datafile '/u02/oradata/index00100.dbf' SIZE 2000K

    I then realized I needed 2000M, not 2000K. By mistake, I deleted the file /u02/oradata/index00100.dbf from a linux shell. I did not 'drop' the tablespace from sqlplus first.

    Now, the DB won't start up, so I can't get into sqlplus to drop the tablespace. How do I fix this??

    I am not a DBA, but have been left with an Oracle instance to support, with no DBAs on staff or any Oracle support.

    Thanks in advance for any help,

    -Joseph DeFanti

  2. #2
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    Start the database in mount mode (STARTUP MOUNT). Then isue the following:
    ALTER DATABASE DATAFILE '/u02/oradata/index00100.dbf' OFFLINE;

    If youur database is in NOARCHIVELOG mode, you must use
    ALTER DATABASE DATAFILE '/u02/oradata/index00100.dbf' OFFLINE DROP;

    After that you will be able to startup the database and drop the offended tablespace.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jun 2000
    Madrid, Spain
    you have to delete the new datafile as well however this will force you to drop the tablespace afterwards, since it seems that that tablespace is holding index segments so there is not much harm dropping the tablespace since indexes are relatively easy to rebuild


    1. startup mount
    2. alter database datafile '/u02/oradata/index00100.dbf' offline drop
    3. alter database open
    4. drop tablespace INDEX001
    5. recreate the tablespace
    6. recreate your indexes

  4. #4
    Join Date
    Oct 2001

    Re: Deleted tablespace from linux shell

    Thanks for all of the quick responses!

    I was able to

    ALTER DATABASE DATAFILE '/u02/oradata/index00100.dbf' OFFLINE;

    resume operations, and then

    ALTER DATABASE DATAFILE '/u02/oradata/index00100.dbf' OFFLINE DROP;

    I am back in business! Thanks to all who responded quickly!

    -Joseph DeFanti

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