FAQ
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
written to it, 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: 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).

Search Discussions

  • Jared Still at Jan 30, 2002 at 12:19 am
    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).
  • Sherman, Paul R. at Jan 30, 2002 at 2:36 am
    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).
  • Johnston, Tim at Jan 30, 2002 at 3:45 am
    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).
  • Rachel Carmichael at Jan 30, 2002 at 3:06 pm
    yes it prevents users writing to the datafile... but it leaves your
    database in a precarious state if you have to do recovery.. or if you
    do a shutdown and try to open the database normal.

    "Sherman, Paul R." wrote:
    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).
    Do You Yahoo!?
    Great stuff seeking new owners in Yahoo! Auctions!
    http://auctions.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 29, '02 at 11:07p
activeJan 30, '02 at 3:06p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase