Drop a tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Drop a tablespace

  1. #1
    Join Date
    Mar 2001
    Posts
    109

    Drop a tablespace

    I intended to drop a tablespace which is not usable any more. But I removed the datafile of that tablespace by mistake before running DROP TABLESPACE statement. So I got the following error when I tried to drop the tablespace now.

    SQL> drop tablespace XXXXX including contents;
    drop tablespace XXXXX including contents
    *
    ERROR at line 1:
    ORA-01115: IO error reading block from file 20 (block # 1)
    ORA-01110: data file 20: '/usr/local/oracle/oradata/db1/XXXXX.dbf'
    ORA-27091: skgfqio: unable to queue I/O
    ORA-27072: skgfdisp: I/O error
    SVR4 Error: 25: Inappropriate ioctl for device
    Additional information: 1


    Could any body tell me how to drop this tablespace ?

    Thanks very much.
    zm

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You can treat this as a missing datafile problem first (guessing that you deleted the datafile instead of dropping it). In startup mount mode, drop the datafile. Open the database and drop the tablespace.

  3. #3
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    Question

    Well try creating a file with the same name and same file extension. '/usr/local/oracle/oradata/db1/XXXXX.dbf' Then try dropping the tbs.

    Hope it will work coz it worked with me but i was on NT4.

    Hear ya soon!
    NK
    ====================================================
    Stand up for your principles even if you stand alone!
    ====================================================

  4. #4
    Join Date
    Mar 2001
    Posts
    109
    After set tablespace datafile offline, the DROP TABLESPACE was successful.

    I did try to create a fake datafile with the same name like XXXXX.dbf, but DROP TABLESPACE failed (we are on Solaris UNIX).
    zm

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by nabeel
    Well try creating a file with the same name and same file extension. '/usr/local/oracle/oradata/db1/XXXXX.dbf' Then try dropping the tbs.

    Hope it will work coz it worked with me but i was on NT4.

    Did it really? No problems with SCN or anything like that? You mean you can just "plug in" an old datafile, say, maybe two months old from a backup, and have it work?

  6. #6
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122
    which version of oracle u have?
    NK
    ====================================================
    Stand up for your principles even if you stand alone!
    ====================================================

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by stecal
    Did it really? No problems with SCN or anything like that? You mean you can just "plug in" an old datafile, say, maybe two months old from a backup, and have it work?
    I think (I'm not sure though) that it depends on the tablespace, TEMP and RBS should not be a problem. Anyone?

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Oct 2000
    Posts
    467
    I guess RBS should be a major problem.
    Temp can be taken care of.
    Vinit

  9. #9
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Steven is right as far as it goes above 9i. I've never tried it but i've heard in lower versions you can try those stunts...the checkpoint counter will however not allow you to fool oracle this way.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  10. #10
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    oho... most of the people are giving solutions without considering the downtime..Isnt it we DBAs duty to give maximum uptime???

    the scenario could be of 2 types

    1. The tablespace could be system or rollback segment tablespace.
    2. Other non-system, non-RBS tablespaces.

    the common logic behind both the scenarios is to make the Oracle not to read the header of the concerned datafilefile/tablespace . This can be done by using "offline immediate".

    We can not take system tablespace or the tablespace that contains active rollback segments offline. (case 1). So, we have to take the database to mount stage and make those datafiles "offline immediate".

    In case 2, we can take the tablespace offline immediate in open state itself. "alter tabelspace tsname offline immediate;"

    After making these steps, we can drop/ recover the tablespace.
    -nagarjuna

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