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

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

  1. #21
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    I'm agree with the arguments given by Sonia and sm8728...truly valid arguments...drop offline will logically remove the file from the list...at the time of startup it will never ask for the dropped datafile for recovery...what does this mean...simply a case of dropping the datafile...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  2. #22
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    ----------------------------------
    Originally by- pipo
    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 !!
    ------------------------------------

    Even if you drop the tablespace the datafiles will still be in your HD. The only way you can remove a file from HD is by OS command only...

    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  3. #23
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    yes, and AFTER having dropped the tablespace... if you wish to make it clean of course

  4. #24
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    pando
    quote:
    ---------------------------------------------------------------------
    Originally posted by soniaarora
    Pando, what do u mean by 'force to drop entire tablespace'? I agree that offline a datafile means that u may drop the tablespace at a later time and not that U will be forced to drop it.
    ---------------------------------------------------------------------

    may?

    no, you are forced to, you cant never ever remove a datafile from a tablespace
    -----------------------------------------------
    Donít be so stubborn

    what will happen if my TS has more then one datafile...if the command will force me to drop entire tablespace...??
    As I told earlier, when you offline drop the datafile oracle will never ask you for recovery of this datafile at the time of next startup...even if you drop this file physically from HD... what does this mean...
    Logically I have dropped my datafile.
    think about it...

    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  5. #25
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    i support pando . we cannot ever drop a datafile . if u drop it u need to drop the tablespace as well . else u need a recovery to be performed .

    OFFLINE takes the datafile offline. If the database is open, you must perform
    media recovery on the datafile before bringing it back online, because
    a checkpoint is not performed on the datafile before it is taken offline.
    DROP takes a datafile offline when the database is in
    NOARCHIVELOG mode.

    Hope this makes u clear .
    siva prakash
    DBA

  6. #26
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    Talking

    ====
    sandycrab

    As I told earlier, when you offline drop the datafile oracle will never ask you for recovery of this datafile at the time of next startup...even if you drop this file physically from HD... what does this mean...
    ========

    what do u mean by this . u want to leave the database in a recovery mode ???? it is not an ideal DBA practice as per my knowledge.
    siva prakash
    DBA

  7. #27
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Originally posted by prakashs43
    i support pando . we cannot ever drop a datafile . if u drop it u need to drop the tablespace as well . else u need a recovery to be performed .

    OFFLINE takes the datafile offline. If the database is open, you must perform
    media recovery on the datafile before bringing it back online, because
    a checkpoint is not performed on the datafile before it is taken offline.
    DROP takes a datafile offline when the database is in
    NOARCHIVELOG mode.

    Hope this makes u clear .

    after Offline drop[ing] a datafile Oracle will never ask you for recovery at the time of next startup even the datafile is been removed physically from the HD. I would never like to bring the datafile online as I've dropped it only because I don't want it any more.
    How can you say U need to drop the TS as well. Plz let me know the case where I have more then one datafile in my TS. Try to do this in your experiment DB and you'll understand...
    "Greatest Rewards come only with Greatest Commitments!"

  8. #28
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not sure what's so hard to understand, even the note from Metalink is pasted here and it's clear stating

    Code:
    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.
    When you offline drop a datafile that datafile is not removed from dictionary, when Oracle has to allocate extents it will still try to allocate extents there

    Code:
    SQL> conn / as sysdba
    Connected.
    
    SQL> create tablespace dropme
      2  datafile 'D:\ORACLE\ORADATA\DEV901\DROPME01.DBF' size 5M,
      3           'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' size 5M
      4  extent management dictionary
      5  minimum extent 128k
      6  default storage (initial 128k
      7                      next 128k
      8                    pctincrease 0);
    
    Tablespace created.
    
    SQL> create table objects tablespace dropme as select * from dba_objects;
    
    Table created.
    
    SQL> select * from fet$ where file# in (11, 12);
    
           TS#      FILE#     BLOCK#     LENGTH
    ---------- ---------- ---------- ----------
            15         12        450        831
            15         11        482        799
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area  105672596 bytes
    Fixed Size                   282516 bytes
    Variable Size              71303168 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 532480 bytes
    Database mounted.
    
    SQL> alter database datafile 'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' offline drop
    
    Database altered.        
     
    SQL> alter database open;
    
    Database altered.
    
    SQL>  select * from fet$ where file# in (11, 12);
    
           TS#      FILE#     BLOCK#     LENGTH
    ---------- ---------- ---------- ----------
            15         12        450        831
            15         11        482        799
    
    SQL> select file#, tablespace_name, name, b.status
      2    from dba_data_files a, v$datafile b
      3   where a.file_id = b.file#;
    
         FILE# TABLESPACE_NAME                NAME                                               STATUS
    ---------- ------------------------------ -------------------------------------------------- -------
             1 SYSTEM                         D:\ORACLE\ORADATA\DEV901\SYSTEM01.DBF              SYSTEM
             2 UNDOTBS                        D:\ORACLE\ORADATA\DEV901\UNDOTBS01.DBF             ONLINE
             3 CWMLITE                        D:\ORACLE\ORADATA\DEV901\CWMLITE01.DBF             ONLINE
             4 DRSYS                          D:\ORACLE\ORADATA\DEV901\DRSYS01.DBF               ONLINE
             5 EXAMPLE                        D:\ORACLE\ORADATA\DEV901\EXAMPLE01.DBF             ONLINE
             6 INDX                           D:\ORACLE\ORADATA\DEV901\INDX01.DBF                ONLINE
             7 TOOLS                          D:\ORACLE\ORADATA\DEV901\TOOLS01.DBF               ONLINE
             8 USERS                          D:\ORACLE\ORADATA\DEV901\USERS01.DBF               ONLINE
             9 OEM_REPOSITORY                 D:\ORACLE\ORADATA\DEV901\OEM_REPOSITORY.DBF        ONLINE
            10 DATA_SMALL                     D:\ORACLE\ORADATA\DEV901\DATA_SMALL01.DBF          ONLINE
            11 DROPME                         D:\ORACLE\ORADATA\DEV901\DROPME01.DBF              ONLINE
            12 DROPME                         D:\ORACLE\ORADATA\DEV901\DROPME02.DBF              OFFLINE
    
    SQL> drop table objects;
    
    Table dropped.
    
    SQL> select * from fet$ where file# in (11, 12);
    
           TS#      FILE#     BLOCK#     LENGTH
    ---------- ---------- ---------- ----------
            15         12        450        831
            15         11        386         32
            15         11        482        799
            15         12        386         32
            15         12        258         32
            15         11        418         32
            15         12        418         32
            15         11        450         32
            15         11        258         32
            15         11        290         32
            15         12        290         32
            15         11        322         32
            15         12        322         32
            15         11        354         32
            15         12        354         32
            15         11        130         32
            15         12        130         32
            15         11        162         32
            15         12        162         32
            15         11        194         32
            15         12        194         32
            15         11        226         32
            15         12        226         32
            15         11          2         32
            15         12          2         32
            15         11         34         32
            15         12         34         32
            15         11         66         32
            15         12         66         32
            15         11         98         32
            15         12         98         32
    as you can see even you offline drop the datafile the file still exists in data dictionary. What does that mean? It means Oracle will try to use free extents in the drop file even it does not exist. What is the fix of this? Drop the tablespace

    Try it yourself

  9. #29
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    Thumbs up

    Good Explanation Pando !!

  10. #30
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    oh sm ... drop tablespace tbsname including contents and datafiles;

    ok?

    or ...

    alter database datafile tbsname resize 1m;



    F.

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