Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Remove Data Files before Opening a Database

By: FYIcenter.com

(Continued from previous topic...)

How Remove Data Files before Opening a Database?

Let's say you have a corrupted data file or lost a data file. Oracle can mount the database. But it will not open the database. What you can do is to set the bad data file as offline befor opening the database. The tutorial exercise shows you how to set two data files offline and open the database without them:

>sqlplus /nolog

SQL> connect SYSTEM/fyicenter AS SYSDBA

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1285956 bytes
Variable Size              58720444 bytes
Database Buffers           37748736 bytes
Redo Buffers                2908160 bytes
Database mounted.

SQL> ALTER DATABASE DATAFILE '\temp\my_space.dbf'
  2  OFFLINE DROP;
Database altered.

SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf'
  2  OFFLINE DROP;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> col file_name format a36;
SQL> col tablespace_name format a16;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 503316480
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 367001600
MY_SPACE        C:\TEMP\MY_SPACE.DBF
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF

At this point, if you don't care about the data in MY_SPACE, you can drop it now with the database opened.

1. What Is an Oracle Tablespace?
2. What Is an Oracle Data File?
3. How a Tablespace Is Related to Data Files?
4. How a Database Is Related to Tablespaces?
5. How To View Tablespaces in the Current Database?
6. What Are the Predefined Tablespaces in a Database?
7. How To View Data Files in the Current Database?
8. How To Create a New Oracle Data File?
9. How To Create a New Tablespace?
10. How To Rename a Tablespace?
11. How To Drop a Tablespace?
12. What Happens to Data Files If a Tablespace Is Dropped?
13. How To Create a Table in a Specific Tablespace?
14. How To See Free Space of Each Tablespace?
15. How To Bring a Tablespace Offline?
16. How To Bring a Tablespace Online?
17. How To Add Another Datafile to a Tablespace?
18. What Happens If You Lost a Data File?
19. How Remove Data Files before Opening a Database?