How Remove Data Files befor opening a Database?
Page 1 of 1
How Remove Data Files befor 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' OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf' 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 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.
>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' OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf' 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 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.
Similar topics
» How To View the Data Files in the Current Database?
» What Happens to the Data Files If a Tablespace Is Dropped?
» How a Tablespace Is Related to Data Files?
» Explain the relationship among Database, Tablespace and Data file.?
» What are the components of physical database structure of Oracle database?
» What Happens to the Data Files If a Tablespace Is Dropped?
» How a Tablespace Is Related to Data Files?
» Explain the relationship among Database, Tablespace and Data file.?
» What are the components of physical database structure of Oracle database?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal