1 2 Previous Next 15 Replies Latest reply: Oct 28, 2011 6:18 PM by 614736 RSS

    DELETE A DATAFILE

    505694
      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
          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
            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
              kuljeet singh -
              Hi forbrich,

              I think this scenario will not works in oracle 9i

              Thanks
              Kuljeet
              • 4. Re: DELETE A DATAFILE
                495612
                Hi kuljeet,

                It works in 9i.
                • 5. Re: DELETE A DATAFILE
                  kuljeet singh -
                  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
                    Yes,
                    But i am talking about "Drop tablespace x including contents and datafiles".
                    • 7. Re: DELETE A DATAFILE
                      570798
                      I think the new feature in 10g r2 is to drop a blank datafile
                      • 8. Re: DELETE A DATAFILE
                        523455
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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