Unable to drop table.

Dear Friends,

I have the following issue in my database. This is my development server. I need to drop one table, it is giving the following error.

drop table data_to_rom;

ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 11024)
ORA-01110: data file 1: '/oradata/oradata/system01.dbf'
ORA-27091: skgfqio: unable to queue I/O
SVR4 Error: 5: I/O error
Additional information: 11023

This table is in a tablespace called sds_temp_ts which is having 3 datafiles. I am able to see these datafiles from dba_data_files with status as AVAILABLE.
In the V$DATAFILE, the columns status is 'OFFLINE'.
In the v$datafile_header, the column error is 'OFFLINE NORMAL'.
But these files are not in OS. I think they were accidentely removed from OS. When I have tried to drop these files from the database, it is giving the message sucessfuly dropped. But the status in the above v$ views retains the same.

When I tried to drop the tablespace, I am getting the same error

ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 11024)
ORA-01110: data file 1: '/oradata/oradata/system01.dbf'
ORA-27091: skgfqio: unable to queue I/O
SVR4 Error: 5: I/O error
Additional information: 11023

Even when I tried to drop the user, I am getting the same error.
Now my requirement is I need to drop this table, if it not possible, I would like to drop the datafiles, even if this is not possible I would like to drop the tablespace and at last I am ready to drop the schema also.

Please let me know how to proceed on this.

First of all you should never delete any datafile from tablespace with multiple datafiles. Doing so renders tablespace unusable.

Unless you want to recover your tablespace upto the point before deletion of datafile took place, the other option if to drop the tablespace.

Do a shutdown abort and start your database in mount state.

Once the database is mounted, use the following commands.
alter database datafile 'bad_filename.dbf' offline drop;

alter database open;

drop tablespace including contents.

HTH

Hmmm, from the error, I'm not so sure you don't have a corrupt block in your system tablespace.

hmm, so it was SYSTEM tablespace. Sorry, I am not sure if drop tablespace stuff could work in that case. you may have to RECOVER from backup.

----------
Just wondering why do we need to add extra datafiles to SYSTEM Tablespace. Since we are not supposed to keep any user created objects in SYSTEM Tablespace. unless maybe datafiles are very small.

hi Dilip,
Thanks for the reply. I have tried to drop the datafiles at the mount state, it has given statement processesed. After opening the database, still i am finding these datafile in the database. when tried to drop the tablespace, again the same error.

Waiting for all your inputs.

Do you have backup? are you on Archivelog mode? If No, I dont see any way for you to recover SYSTEM tablespace. Like Jeff said, your SYSTEM Tablespace may have been corrupted. Ignore my above advise on dropping the Tablespace. I should have seen you having trouble with system tablespace.

27091 is usually a good sign of corruption, but could
it be possible that you do not have read/write access
on the disk that the tablespace file is located on ???
Have some of the priv's changed on the server side
given the impression that you have an Oracle issue?

since you drop a table you update data dictionary (i.e tablespace system), the problems comes from there, most probably I would say you have some sort of corruption

Dear All,

Here are the inputs, I dont have any backup. Database is running in noarchivelog mode. I am least bothered in dropping the entire schema where the table is located. As of now I am not able to drop the user even, the problem arises. How to repair the SYSTEM tablespace to remove the corruption.

Without any hot/cold backups and arch logs, your best bet is to recreate the database.