Unable to drop table.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Unable to drop table.

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Location
    India
    Posts
    36

    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.

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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
    -- Dilip

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Hmmm, from the error, I'm not so sure you don't have a corrupt block in your system tablespace.
    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."

  4. #4
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.
    -- Dilip

  5. #5
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.
    -- Dilip

  7. #7
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  9. #9
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  10. #10
    Join Date
    Oct 2000
    Posts
    467
    Without any hot/cold backups and arch logs, your best bet is to recreate the database.
    Vinit

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