Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> what's the diffrent between offline and offline drop

what's the diffrent between offline and offline drop

From: alex <tech_a_at_126.com>
Date: Mon, 27 Dec 2004 17:28:52 +0800
Message-Id: <20041227092855.0A65572C410@turing.freelists.org>


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_at_126.com
          2004-12-27


--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 27 2004 - 03:24:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US