Datafile has been removed already , But I still can find its information in V$datafil - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 39

Thread: Datafile has been removed already , But I still can find its information in V$datafil

  1. #11
    Join Date
    Jul 2002
    Posts
    132
    Sorry Pando, I dont agree with u. Go thru this note in Metalink:



    Bookmark Fixed font Go to End

    Doc ID: Note:111316.1
    Subject: How to 'DROP' a Datafile from a Tablespace
    Type: BULLETIN
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 31-MAY-2000
    Last Revision Date: 27-APR-2001


    PURPOSE
    This note explains how a datafile can be removed from a database.

    Since there can be confusion as to how a datafile can be dropped because of
    the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains the
    steps needed to delete a datafile and in contrast, and when the OFFLINE DROP
    command is used.


    SCOPE & APPLICATION
    There are two situations where people may want to 'remove' a datafile from a
    tablespace:

    1. You have just mistakenly added a file to a tablespace, or perhaps you
    made the file much larger than intended and now want to remove it.

    2. You are involved in a recovery scenario and the database won't start
    because a datafile is missing.

    This article is meant to discuss circumstance 1 above. There are other
    articles that discuss recovery scenarios where a database cannot be brought
    online due to missing datafiles. Please see the 'Related Documents' section
    at the bottom of this article.


    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 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 ='';

    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 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 ='';

    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=''

    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 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 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
    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.


    RELATED DOCUMENTS
    -----------------

    [NOTE:30910.1] Recreating database objects
    [NOTE:1013173.6] Recovering from a lost datafile in a USER tablespace
    [NOTE:1013115.6] Recovering from a lost datafile in an INDEX tablespace
    [NOTE:1013221.6] Recovering from a lost datafile in a ROLLBACK tablespace
    [NOTE:1029252.6] How to resize a datafile



    .



    --------------------------------------------------------------------------------

    Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.


  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    NOTE:
    The ALTER DATABASE DATAFILE 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


    go through it again carefully

  3. #13
    Join Date
    Jul 2002
    Posts
    132
    Pando,
    This is exactly what I meant. It is not that after u offline a datafile, u HAVE to drop the tablespace. It is only that this is the workaround available to remove the datafile which is not required in the tablespace.

  4. #14
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I think we have some missunderstanding here, are you saying you can remove a datafile from a tablespace? Or you are saying you cannot?

  5. #15
    Join Date
    Jul 2002
    Posts
    132
    time for me to leave office lest I shud miss my bus. Shall resume on Monday. sorry

  6. #16
    Join Date
    Aug 2002
    Posts
    21
    sonia is correct the only way to drop a datafile from db w/o dropping the ts is to offline drop it provided it is not the last file in the ts . it will not force you to drop the ts . This will also remove the entry from the data dictionary.
    Oracle my life

  7. #17
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    I do not agree, Pando is right here : you CANNOT drop a datafile w/o dropping the tablespace.
    you can make the datafile offline, but it will still be on your HD, it won't be dropped !!

  8. #18
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Pando is right, the file is simply marked as offline in the controlfile. As the document say this allows you to open the database without the controlfile trying to checkpoint the file.

    The original post however is possible if running under Unix as it is possible to physically remove the datafile while the database is running but the entry will still appear as valid until the data within that file is accessed or the database is bounced.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  9. #19
    Join Date
    Aug 2002
    Posts
    21
    We r not talking about dropping the file from HD but dropping it from the DB,and that is what we accomplish by this command. Pls note the command drop tablespace including contents also does not drop the datafiles from HD. Prior to 9i there was no way to remove the files from HD by firing DB commands . The files had to be physically removed from the HD
    Oracle my life

  10. #20
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    The original post states "Datafile has been removed already" surely this means it has been removed from the HD

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

Posting Permissions

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



Click Here to Expand Forum to Full Width