Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: Drop datafile - V.urgent


    I've issued the cmd alter database xcv datafile 'fffff' offline drop;
    theb physically dropped the datafile. but datafile is stil exist in dba_data_file and it shows it need recovery.

    pl. help how to remove logically this datafile.

  2. #2
    Join Date
    May 2003
    if the tablespace has only one datafile then drop the tablespace; if your tablespace has many datafiles, the only way is to export objects in the tablespace, dropthe tablespace re-create it and import backuped objects.


  3. #3
    Join Date
    Nov 2004
    South Africa

    Restore or drop tablespace....

    You did an offline drop. You can still restore the datafile.

    SQL> create tablespace mytab datafile
    2 'D:\ORACLE\ORADATA\GARYC\mytab.dbf' size 5M;

    Tablespace created.

    SQL> alter database datafile 'D:\ORACLE\ORADATA\GARYC\mytab.dbf' offline drop;

    Database altered.

    SQL> alter database datafile 'D:\ORACLE\ORADATA\GARYC\mytab.dbf' online;
    alter database datafile 'D:\ORACLE\ORADATA\GARYC\mytab.dbf' online
    ERROR at line 1:
    ORA-01113: file 14 needs media recovery
    ORA-01110: data file 14: 'D:\ORACLE\ORADATA\GARYC\MYTAB.DBF'

    SQL> recover datafile 'D:\ORACLE\ORADATA\GARYC\mytab.dbf';
    Media recovery complete.
    SQL> alter database datafile 'D:\ORACLE\ORADATA\GARYC\mytab.dbf' online;

    Database altered.


    Yes, it needs media recovery, but there is no reason why you could not restore it. It stays in the data dictionary because the tablespace is still there and still consists of that one datafile, therefore it cannot remove the entry for the datafile until you have dropped the tablespace.

    Either restore the datafile to access the tablespace or drop the tablespace. You're file will then go away.

Posting Permissions

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