Collections:
Remove Data Files before Opening a Database in Oracle
How Remove Data Files before Opening a Database in Oracle?
✍: FYIcenter.com
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 before 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.
2019-04-09, 824👍, 0💬
Popular Posts:
How To Create a Test Table for Transaction Testing in Oracle? If you want to practice DML statements...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...