Deleted tablespace from linux shell

[jdefanti]

Hi,

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

[jmodic]

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;
instead.

After that you will be able to startup the database and drop the offended tablespace.

[pando]

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

in NOARCHIVELOG

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

[jdefanti]

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