But, "offline" and "removed from the system" are two different things...
You are correct that your datafile is offline... That just means that it is
unavailable for use... But, it is still part of your database... The DROP
keyword does not mean that the datafile is removed from the system... It is
a special keyword that allows you to offline a datafile while running in
NOARCHIVELOG... If you check v$datafile you should see that the file is in
a status of offline or recover.. I'm not 100% sure what happens when you
offline drop but I think you may have to recover the datafile ( you have the
archive logs right? ) before you can bring it online... If it was offlined
normal, you should be able to bring it online without recovery... But, you
should be able to figure that out by the status in v$datafile...
v$recover_file is also handy... So, once you know you can bring it online,
you can rename it to what you wish and bring it online...
Good Luck
Tim
-----Original Message-----
Sent: Tuesday, January 29, 2002 9:31 PM
To: Multiple recipients of list ORACLE-L
Hello Jared et al,
The Oracle Reference Manual for 8i, "schematic diagram" has alter database
'' offline drop, so that's what I tried (as system).
The system came back, 'database altered', but a check of dba_data_files
shows that the datafile (#115) is still there. The dba_extents lists no rows
selected if I query on bytes where file# = 115, so taking the datafile
offline as soon as I realized my error was successful in keepin any user
data written to the datafile.
Paul
-----Original Message-----
Sent: Tuesday, January 29, 2002 7:11 PM
To: Multiple recipients of list ORACLE-L
You can't safely drop a datafile from the database.
When you say 'According to Oracle, I should be able to drop a datafile
at has no data written to it', do you have a reference where we can
see that. It does not sound familiar to me.
Per your situation, since you have the datafile offline, just
rename it via 'alter database rename file..',
then put it back online.
Jared
On Tuesday 29 January 2002 15:08, Sherman, Paul R. wrote:
Hello list,
Oracle 8.1.6.3.0, on HP-UX 11.0, using Oracle Parallel Server (OPS)
According to Oracle, I should be able to drop a datafile that has no data
but I can not. Neither DBA studio (the garbage can stays
'grayed'), nor the command line (alter tablespace, with the DROP clause)
works. I had immediately taken the datafile offline (I had typed
sbplus_x18.dbf instead of sbplus_x16.dbf for the datafile name to use in
case you are wondering how this turn of events happened in the first
place), and checked all of the books, and Meta-Link. I tried a test on a
non-production machine, no go; even taking the tablespace off-line first
makes no difference. And of course I get errors as one node can not see the
other nodes sbplus_x18.dbf datafile. I tried an 'alter system check
datafiles' to see if this would make the other node happy (as the datafile
is now off-line, this may work), but I need a solution other than exporting
the data, rebuilding the tablespace, then importing the export.
Anyone ever come across this issue ?
Thank you,
Paul Sherman
DBA
voice - 781-501-4143 (office)
fax - 781-278-8341 (office)
email - psherman_at_elcom.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com--
Author: Jared Still
INET: jkstill_at_cybcon.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com--
Author: Sherman, Paul R.
INET: PSherman_at_elcom.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com--
Author: Johnston, Tim
INET: TJohnston_at_quallaby.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
