Skip to Content
avatar image
Former Member

Drop datafile from tablespace

I'm new to oracle.

After I extended a tablespace I understood that it was too large.

I've been reading about how to drop a tablespace, but I don't want to drop the entire tablespace. I just want to drop the last datafile of this tablespace.

I know how to drop tablespaces in sapdba and brtools, but I don't know how I can drop one specific datafile (number 29) of the tablespace PSAPBTABD?

Please Help med.

Best regards

Harald V

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Oct 22, 2007 at 07:03 PM

    Harald,

    you have a few possibilities:

    1. decrease the size of your datafile to a small value:

    brspace -c -u / -f dfalter

    2. reorg fully your tablesapce

    check Note 646681 - Reorganizing tables with BRSPACE

    as you can see, you cannot drop a datafile easily in Oracle 9.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 22, 2007 at 05:55 PM

    Hello Baran.

    Unfortunatly I have oracle 9.2.

    It is Windows.

    What abaout stopping sap, stopping oracle and manually delete the data-file?

    Best Regards

    Harald V

    Message was edited by:

    Harald Vedvik

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Unfortunatelly, you "cannot" drop a datafile like that.

      If you do it, then ORacle will recognize that the datafile is missing and your problems will start.

      In Oraclw < 10g three is no "drop datafile" option.

      You cannot drop a datafile, do not try to do it, you will end "messing up" the DB.

      Leave the file. You will use it in the future.

      If you want to "recover" space, then change the datafile size.

      The metalink Note:111316.1 clearly indicates:

      Once you make a datafile part of a tablespace, the datafile CANNOT be removed, 
      although we can use some workarounds.

      What the note explains is to "move" the data to a "new" tablespace.

  • avatar image
    Former Member
    Oct 22, 2007 at 07:39 PM

    Hello,

    You are right! If you want the space on disk, you can resize it the smaller size.

    Regards,

    Madhukar

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 22, 2007 at 05:26 PM

    Hello Harald,

    see please this thread:

    how-to-drop-data-file

    also the section 43. <i>How can I delete a data file that was created by mistake</i>

    in the SAP Note<i><b> 592393 - FAQ: Oracle</b></i>

    if you have Oracle 9i databases, it is not easy to drop a datafile, but in Oracle 10g

    you can drop a datafile like that:

    <b><i>ALTER TABLESPACE</i></b> <<u><i>Tablespace Name</i></u>> <b><i>DROP DATAFILE</i></b> '<<u>PATH und NAME of Datafile</u>>';

    for example, you have a tablespace called PSAPC11 and you want to drop a datafile called dat.data15 of this tablespace, you can drop it like that:

    e.g. <b><i>alter tablespace</i></b> <u>PSAPC11</u> <b><i>drop datafile</i></b> <u>'/oracle/C11/psapc11/dat.data15'</u>

    Best regards

    Baran

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      <u><i><b>Attention:</b></i></u>

      <u><b>only</b></u> if you have Oracle <b><i><u>10g</u></i></b> Databases, you can drop a datafile like that:

      ALTER TABLESPACE <Tablespace Name> DROP DATAFILE '<PATH TO und THE NAME of Datafile>';

  • avatar image
    Former Member
    Oct 22, 2007 at 06:25 PM

    Thanks again for your answers Baran.

    I have no metalink account.

    Best regards.

    Harald Vedvik

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 22, 2007 at 07:08 PM

    Hello and thanks for all the respons.

    I've got metalink access and have been reading oracle note 111316.1 and note 1029252.6 How to resize a datafile.

    I now understand that my best option is to leave the data-file.

    Maybe resize it.

    Best regards Harald Vedvik

    Add comment
    10|10000 characters needed characters exceeded