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

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

  1. #31
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Cool

    Originally posted by soniaarora
    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.)

    nope, you dont need to shutdown the database in NT to remove a datafile that has been logically removed from Oracle. You just query any of datafile related datadictionary views ater removing the datafile from Oracle using sql syntex. Your lock gets released and you can remove the file physically...

    Steps on NT:

    alter database drop <; --logical deletion of datafile
    select * from dba_data_files; --this releases locks on datafile
    remove the file physically..
    This works...
    -nagarjuna

  2. #32
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Cool

    Originally posted by soniaarora
    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.
    nope.. We can use a workaround to remove the datafile from a tablespace.

    Steps:

    1. alter database drop datafile <>;
    2. alter database backup controlfile to trace;
    3. edit the controlfile creation script generated by the above command. Remove the entry of the datafile that we have dropped in step 1.
    4. Now, start up the database in nomount state and recreate the control file.
    5. Do the recovey if needed using current logfiles.
    6. Open the database.

    This works fine. But, we need some experiance to do this. Be, careful doing this..

    -nagarjuna


  3. #33
    sigh, so many PROFESSIONALs discussing such a MEANINGLESS topic!
    It is so clear that you have to rebuild the tablespace after your offline the datafile.
    Offline drop the datafile only allows you to open the database temporary so that you can export/move the tables in the tablespace and drop and recreate it later.
    I think the metalink note has said it clearly.

    nagarjuna:
    Your way of removing that datafile is TOTALLY WRONG!


    www.cnoug.org

  4. #34
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary

  5. #35
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by pando
    I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary
    mmm...v$datafile is a controlfile view...

    chao_ping > I agree that, this is a meaningless topic.. I posted that msg just to add spice to our DBA life. We get bored with daily routine works.. These kinda experiments gives some fun.. nothing much.. I do agree that my reply was meaningless..

  6. #36
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by nagarjuna
    [mmm...v$datafile is a controlfile view...
    [/B]
    Oracle use v$datafile (control flie) mostly in ALTER DATABASE OPEN(MOUNT) process.
    In most others situations oracle will use sys.ts$ and sys.file$ tables.
    but records about datafile still present in dictioany tables and don't present in controlfile!!!
    Very interesting situation...

    THIS IS WRONG WAY.

    [Edited by Shestakov on 09-07-2002 at 12:27 PM]

  7. #37
    Join Date
    Sep 2002
    Posts
    9
    Pando is right, since you can`t control how data is writen to the files, you don`t know the contets of the file you are "droping".
    A table may span over more than one datafile, for example, so there is no logic in droping a datafile, not in my eyes, anyway.

  8. #38
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Angry

    I am extremely sorry for that post. That was not the right way to do. That was really a stupidity. Some problem with my basics I guess..

  9. #39
    Join Date
    Oct 2000
    Posts
    139
    soniaarora

    you cannot remove a datafile from a tablespace, even the note you pasted said so

    read it again and properly!

    thank you

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