Drop a tablespace

Printable View

10-31-2002, 02:22 PM
mzhu

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.
10-31-2002, 02:55 PM
stecal

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.
10-31-2002, 03:02 PM
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.

Hear ya soon!:D
10-31-2002, 03:08 PM
mzhu

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).
10-31-2002, 03:11 PM
stecal

Quote:

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?
10-31-2002, 03:18 PM
nabeel

which version of oracle u have?
10-31-2002, 04:03 PM
Mr.Hanky

Quote:

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
11-01-2002, 12:04 AM
vinit

I guess RBS should be a major problem.
Temp can be taken care of.
11-01-2002, 10:24 AM
Tarry

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.
11-02-2002, 09:39 AM
nagarjuna

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.