what's the diffrent between offline and offline drop

Hi oracle-l

i did a test about 'alter database datafile xxx offline (drop)'
it seems for the end user, there is no diffrent. i can recover the datafile and 
take the datafile online even i offline drop the datafile.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ALEX\SYSTEM01.DBF
C:\ORACLE\ORADATA\ALEX\RBS01.DBF
C:\ORACLE\ORADATA\ALEX\USERS01.DBF
C:\ORACLE\ORADATA\ALEX\TEMP01.DBF
C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF
C:\ORACLE\ORADATA\ALEX\INDX01.DBF

6 rows selected.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' offline;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;
alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF'


SQL> recover datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF';
Media recovery complete.
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;

Database altered.

SQL>
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' offline drop;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;
alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF'


SQL> recover datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF';
Media recovery complete.
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;

Database altered.
        

          Thanks
                                Alex

        tech_a@xxxxxxx
          2004-12-27


--
http://www.freelists.org/webpage/oracle-l

Other related posts: