Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6

    Unhappy Unanswered: Made a STOOPID mistake

    I created a datafile in the wrong directory. I tooK it offline immediately and there is no data in it.

    I cannot for the life of me remember how to remove it from Oracle 8.1.5 so I can delete the file and start over.

    Can some one tell me how?

    Thanks!!!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Drop the tablespace from the database....

    DROP TABLESPACE tablespacename INCLUDING CONTENTS;

    shutdown database
    startup database

    delete datafile from server ...

    HTH
    Gregg

  3. #3
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6

    Won't that kill the whole table space?

    Hi grabham,
    The database is in production, and has 8 other data files in it. If I drop the table space, and include contents won't that kill everything?

    I did an "alter database datafile 'file' offline drop" but the data file is still showing up in dba_data_files.

    I need to kill off only that one data file.


    Thank you!!!!

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by gbrabham
    Drop the tablespace from the database....

    DROP TABLESPACE tablespacename INCLUDING CONTENTS;

    shutdown database
    startup database

    delete datafile from server ...

    HTH
    Gregg
    Nope,

    Don't drop whole tablespace but try this:
    SHUTDOWN IMMEDIATE
    rename or move the datafile to new location (on OS level)
    STARTUP MOUNT;
    ALTER DATABASE RENAME FILE '/wrong/place/xxx.dbf' TO '/new/location/xxx.dbf';
    ALTER DATABASE OPEN;

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The datafile will continue to show up until the database is rebooted...
    Don't delete the datafile until you have shutdown and restarted the
    database.

    HTH
    Gregg

  6. #6
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6
    Originally posted by gbrabham
    The datafile will continue to show up until the database is rebooted...
    Don't delete the datafile until you have shutdown and restarted the
    database.

    HTH
    Gregg
    Gregg and Ika,

    Thank you both soooo much!
    I will have an opportunity to shutdown the database later today.

  7. #7
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6

    Unhappy It won't go away...

    Originally posted by gbrabham
    The datafile will continue to show up until the database is rebooted...
    Don't delete the datafile until you have shutdown and restarted the
    database.

    HTH
    Gregg
    Hi Gregg,
    I have shutdown the database, but the data file is still showing up. It is offline and I tried using the "alter database datafile 'file' offline drop", shutting down, and restarting but it won't go away.

    Any ideas on how to get rid of this data file?

    Thanks!!

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The file should no longer exists in
    sql> select file_name from dba_data_files;

    It will still exist on the server... You have to manually delete the
    file from the server once Oracle is thru with it...

    HTH
    Gregg

  9. #9
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6

    Unhappy Yep, I understand that.

    The data file is still showing in dba_data_files.

    I don't understand why.

    Any thoughts?

    Thank you!!!!!

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    From Metalink:

    How to 'DROP' a Datafile from a Tablespace: =========================================== Before we start with detailed explanations of the process involved, please note that Oracle does not provide an interface for dropping datafiles in the same way that you could drop a schema object such as a table, a view, a user, etc. Once you make a datafile part of a tablespace, the datafile CANNOT be removed, although we can use some workarounds. Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup. If the datafile you wish to remove is the only datafile in that tablespace, simply drop the entire tablespace using: DROP TABLESPACE <tablespace name> INCLUDING CONTENTS; You can confirm how many datafiles make up a tablespace by running the following query: select file_name, tablespace_name from dba_data_files where tablespace_name ='<name of tablespace>'; The DROP TABLESPACE command removes the tablespace, the datafile, and the tablespace's contents from the data dictionary. Oracle will no longer have access to ANY object that was contained in this tablespace. The physical datafile must then be removed using an operating system command (Oracle NEVER physically removes any datafiles). Depending on which platform you try this on, you may not be able to physically delete the datafile until Oracle is completely shut down. (For example, on Windows NT, you may have to shutdown Oracle AND stop the associated service before the operating system will allow you to delete the file - in some cases, file locks are still held by Oracle.) If you have more than one datafile in the tablespace, and you do NOT need the information contained in that tablespace, or if you can easily recreate the information in this tablespace, then use the same command as above: DROP TABLESPACE <tablespace name> INCLUDING CONTENTS; Again, this will remove the tablespace, the datafiles, and the tablespace's contents from the data dictionary. Oracle will no longer have access to ANY object that was contained in this tablespace. You can then use CREATE TABLESPACE and re-import the appropriate objects back into the tablespace. If you have more than one datafile in the tablespace and you wish to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the affected tablespace. Gather information on the current datafiles within the tablespace by running this query: select file_name, tablespace_name from dba_data_files where tablespace_name ='<name of tablespace>'; Make sure you specify the tablespace name in capital letters. In order to allow you to identify which objects are inside the affected tablespace for the purposes of running your export, use the following query: select owner,segment_name,segment_type from dba_segments where tablespace_name='<name of tablespace>' Now, export all the objects that you wish to keep. Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING CONTENTS. Note that this PERMANENTLY removes all objects in this tablespace. Delete the datafiles belonging to this tablespace using the operating system. (See the comment above about possible problems in doing this.) Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace. (This may have to be done at the table level, depending on how the tablespace was organized.) NOTE: The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace. If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile. If you do not wish to follow any of these procedures, there are other things that can be done besides dropping the tablespace. - If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command. See 'Related Documents' below. - If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.

    HTH
    Gregg

  11. #11
    Join Date
    Mar 2004
    Location
    Vista, CA
    Posts
    6
    Hi Gregg,
    I checked the status of the data file, and it's showing recover. I need to get the database back up ASAP. Since it is a furball to get rid of the file, is there any way I can get it back online?

    It's check point is way stale, but it doesn't look like anything was stored in the file in question. Can I run an alter database recover datafile on it?

    Then I could move it to the correct place and just keep it.

    Thanks for all your help I REALLY appreciate it!!!!

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Yes, you can alter database recover datafile ...
    Then move it or resize it small...

    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •