-
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
-
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.
-
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!
====================================================
-
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
-
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?
-
which version of oracle u have?
NK
====================================================
Stand up for your principles even if you stand alone!
====================================================
-
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.
-
I guess RBS should be a major problem.
Temp can be taken care of.
Vinit
-
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)
--- Everything was meant to be---
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|