RE: e: deleting a datafile on OPS

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 <database> '<datafile>' 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 <YOUR_DB_HERRE> 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).