Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews DB Oracle Interviews:Concepts and ArchitectureData AccessDatabase ArchitectureDatabase ManagementDatabase Security OracleDistributed ProcessingFlexfieldForms ReportsGeneral OracleMemory ManagementMTO-SAP Financial AccountingOCIOracle AOLOracle AROracle Backup RecoveryOracle D2KOracle DatabaseOracle Database DeveloperOracle DB OptimizationOracle DBAOracle DeveloperOracle ETLOracle Forms 3.0Oracle Forms 4.0Oracle GLOracle PL-SQLOracle RMANOracle ScenariosOracle SecurityOracle SQLOracle System ArchitectureOracle Technology Network (OTN)Programmatic ConstructsRAC (Real Application Clusters)SQL Plus
Copyright © 2018. All Rights Reserved
Oracle Database Interview Question:
How Remove Data Files before opening a Database?
Submitted by: AdministratorLet'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/globalguideline AS SYSDBA
SQL> STARTUP MOUNT;
ORACLE instance started.
<pre>Total System Global Area 100663296 bytes
Fixed Size 1285956 bytes
Variable Size 58720444 bytes
Database Buffers 37748736 bytes
Redo Buffers 2908160 bytes
Database mounted.</pre>
SQL> ALTER DATABASE DATAFILE ' empmy_space.dbf'
2 OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE DATAFILE ' empmy_space_2.dbf'
2 OFFLINE DROP;
Database altered.
Submitted by: Administrator
>sqlplus /nolog
SQL> connect SYSTEM/globalguideline AS SYSDBA
SQL> STARTUP MOUNT;
ORACLE instance started.
<pre>Total System Global Area 100663296 bytes
Fixed Size 1285956 bytes
Variable Size 58720444 bytes
Database Buffers 37748736 bytes
Redo Buffers 2908160 bytes
Database mounted.</pre>
SQL> ALTER DATABASE DATAFILE ' empmy_space.dbf'
2 OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE DATAFILE ' empmy_space_2.dbf'
2 OFFLINE DROP;
Database altered.
Submitted by: Administrator
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;
<pre>TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:ORACLEXEORADATAXEUSERS.DBF 104857600
SYSAUX C:ORACLEXEORADATAXESYSAUX.DBF 503316480
UNDO C:ORACLEXEORADATAXEUNDO.DBF 94371840
SYSTEM C:ORACLEXEORADATAXESYSTEM.DBF 367001600
MY_SPACE C:TEMPMY_SPACE.DBF
MY_SPACE C:TEMPMY_SPACE_2.DBF</pre>
At this point, if you don't care about the data in MY_SPACE, you can drop it now with the database opened.
Submitted by: Administrator
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;
<pre>TABLESPACE_NAME FILE_NAME BYTES
--------------- --------------------------------- ---------
USERS C:ORACLEXEORADATAXEUSERS.DBF 104857600
SYSAUX C:ORACLEXEORADATAXESYSAUX.DBF 503316480
UNDO C:ORACLEXEORADATAXEUNDO.DBF 94371840
SYSTEM C:ORACLEXEORADATAXESYSTEM.DBF 367001600
MY_SPACE C:TEMPMY_SPACE.DBF
MY_SPACE C:TEMPMY_SPACE_2.DBF</pre>
At this point, if you don't care about the data in MY_SPACE, you can drop it now with the database opened.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.