This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 28, 2011 4:18 PM by 614736 RSS

DELETE A DATAFILE

505694 Newbie
Currently Being Moderated
HOW I DELETE A DATAFILE
ADMINISTRATOR GUIDE SAYS THERE'S NO SQL COMMAND TO DELETE A DATAFILE
1. SO U HAVE TO CREATE A TABLESAPCE
2. ADD THAT DATAFILE TO IT
3. DELETE the talespace with option 'including datafiles'

but when i try to add that datafile to a newly cretaed tablespace
i recieve an error says, "the datafiel is part of the database'

thnx for the help
  • 1. Re: DELETE A DATAFILE
    439075 Newbie
    Currently Being Moderated
    Hey,

    Try this - How to drop a datafile from a tablespace

    http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_16.shtml

    Cheers,
    Marcello
  • 2. Re: DELETE A DATAFILE
    Hans Forbrich Oracle ACE Director
    Currently Being Moderated
    pop2@fuzzy:~> sqlplus system/oracle

    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 20 18:32:03 2006

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    SQL> select tablespace_name from dba_tablespaces;

    TABLESPACE_NAME
    --------------------------------------------------------------------------------
    SYSTEM
    UNDO
    SYSAUX
    TEMP
    USERS
    SPATIAL

    6 rows selected.

    SQL> create tablespace test datafile '/home/oracle/test1.dbf' size 5m;

    Tablespace created.

    SQL> alter tablespace test add datafile '/home/oracle/test2.dbf' size 5m;

    Tablespace altered.

    SQL> select file_name from dba_data_files where tablespace_name='TEST';

    FILE_NAME
    --------------------------------------------------------------------------------
    /home/oracle/test1.dbf
    /home/oracle/test2.dbf

    SQL> !ls -l /home/oracle/test*dbf
    -rw-r-----  1 oracle dba 5251072 2006-04-20 18:33 /home/oracle/test1.dbf
    -rw-r-----  1 oracle dba 5251072 2006-04-20 18:33 /home/oracle/test2.dbf

    SQL> alter tablespace test drop datafile '/home/oracle/test2.dbf';

    Tablespace altered.

    SQL> !ls -l /home/oracle/test*dbf
    -rw-r-----  1 oracle dba 5251072 2006-04-20 18:33 /home/oracle/test1.dbf

    SQL> drop tablespace test including contents and datafiles;

    Tablespace dropped.

    SQL> !ls -l /home/oracle/test*dbf
    ls: /home/oracle/test*dbf: No such file or directory

    SQL> exit
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    pop2@fuzzy:~>
    Perhaps next time you would be so kind as to include the version information ...
  • 3. Re: DELETE A DATAFILE
    KuljeetPalSingh Guru
    Currently Being Moderated
    Hi forbrich,

    I think this scenario will not works in oracle 9i

    Thanks
    Kuljeet
  • 4. Re: DELETE A DATAFILE
    495612 Newbie
    Currently Being Moderated
    Hi kuljeet,

    It works in 9i.
  • 5. Re: DELETE A DATAFILE
    KuljeetPalSingh Guru
    Currently Being Moderated
    Hi,

    alter tablespace <tb name> drop datafile ' '; cmd is available from 10g rel 2,

    have a look
    http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part2.html

    Thanks
    kuljeet
  • 6. Re: DELETE A DATAFILE
    495612 Newbie
    Currently Being Moderated
    Yes,
    But i am talking about "Drop tablespace x including contents and datafiles".
  • 7. Re: DELETE A DATAFILE
    570798 Newbie
    Currently Being Moderated
    I think the new feature in 10g r2 is to drop a blank datafile
  • 8. Re: DELETE A DATAFILE
    523455 Newbie
    Currently Being Moderated
    It work's in 9i only when one have Oracle Managed Files (OMF), a new feature introduced in Oracle9i, enables the Oracle Server to automatically create and delete database files using standard operating system interfaces.

    Regards
    rajesh
  • 9. Re: DELETE A DATAFILE
    516450 Journeyer
    Currently Being Moderated
    Yes, you are right. It's one of the new features of Oracle10g, where in allows to drop a datafile associated with a tablespace, provided it is blank. The following is the syntax to do it:

    ALTER TABLESPACE <XYZ> DROP DATAFILE '<FILE_NAME>';

    HTH

    Thanks
    Chandra Pabba
  • 10. Re: DELETE A DATAFILE
    597451 Newbie
    Currently Being Moderated
    Sorry, I thought the DROP DATAFILE clause is not valid with the ALTER TABLESPACE statement in Oracle 10g.

    To drop a datafile from the database, don't you should take the datafile offline by using the ALTER DATABASE...DATAFILE...OFFLINE statement and then drop the datafile by using the ALTER DATABASE...DROP DATAFILE statement?

    Thanks.

    Roberto C. Sánchez
  • 11. Re: DELETE A DATAFILE
    Kashif M Oracle ACE
    Currently Being Moderated
    Hi,

    Please guide me how we can delete data file from Oracle 9i.
    We wrongly we created datafile in different tablespace, now i wana to delete it.

    BR
    Kashif
  • 12. Re: DELETE A DATAFILE
    604693 Newbie
    Currently Being Moderated
    I think this should work
    1)mark that datafile offline drop;
    2)take the tabklespace offline
    3) cp all the necessary datafiles to new location
    4)alter tablespace rename datafile ';;;' to 'newloc'[do not include that datafile]
    5)put back tablespace online.
  • 13. Re: DELETE A DATAFILE
    416047 Newbie
    Currently Being Moderated
    user601690, this is rubbish. Why are you moving all datafiles to another location if you want to take one of them offline? Just offline the datafile if you want to.

    For the OP; you cannot remove a datafile from a tablespaces in 9i. You can remove a datafile from a tablespace in 10G if it is empty and if it is not the only datafile in that tablespace.

    In 9i you can offline the datafile if there is nothing in it, but you cannot remove it.

    See Metalink note 111316.1 titled "How to 'DROP' a Datafile from a Tablespace".

    Message was edited by:
    Yas
  • 14. Re: DELETE A DATAFILE
    sanora600 Newbie
    Currently Being Moderated
    Bad way of getting ride of datafile which has been accidentely added.

    1. Take a backup of tablespace using logical command exp/imp.
    2. Query the data dictionary for available datafiles in tablespace, make note of it(storage and other parameter and location of datafile).
    3. Drop the tablespace with all the datafile
    4. Recreate the tablespace using step 2 information.
    5. Load the data using imp/exp
1 2 Previous Next