Interview Questions → DBA → DBA Questionnaire I

DBA Questionnaire I

Explain the difference between Oracle Database and Instance ?

Oracle Database consists of Datafiles, Control files, Redo log files whereas Oracle Instance consists of SGA and Oracle processes ( DBWR, LGWR, SMON, PMON, ARCH, CKPT etc )

What is Mutating Table error ?

Mutating Table error happens when you try to modify the same row you are using it. Use temp table to avoid this error.

What is correlated sub-query ?

Correlated sub query is a sub query which has reference to the main query

Which one of the below change will decrease the PAGING/SWAPPING

      1. INCREASE SORT_AREA_RETAINED_SIZE

      2. DECREASE SHARED_POOL_SIZE

      3. DECREASE OPEN_CURSORS paramenters

      4. INCREASE DB_CACHE_SIZE

DECREASE SHARED_POOL_SIZE

Explain steps involved in migration from Oracle 9.0.1 to 9.2.0.4 ?

      1. Shutdown 9.0.1 database

      2. Apply software patch to new/old ORACLE_HOME

      3. Change ORACLE_HOME to point to new 9.2.0.4 binaries

      4. STARTUP MIGRATE

      5. @ORACLE_HOME/rdbms/admin/catpatch.sql

      6. @ORACLE_HOME/rdbms/admin/catrcp.sql

      7. Change init.ora parameter COMPATIBLE = 9.2.0.4

      8. Shutdown and StartUp normal

Can you create database under any unix userid besides oracle ?

Yes, you can create database user other than oracle as long as that user is part of the DBA Group in UNIX

Locally managed TEMP Tablespace is 100% full and there is no space available to add datafile to increase TEMP tablespace. What can you do that might free up TEMP space?

      1. Issue ALTTER TABLESPACE PCTINCREASE 1 followed by ALTER TABLESPACE PCTINCREASE 0 command:

      2. Close some of the idle sessions connected to the database

List 5 important features of Oracle 9i

      1. AUTOMATIC UNDO MANAGEMENT

      2. AUTOMATICE PGA MEMORY MANAGEMENT

      3. MULTI-TABLE INSERT statements

      4. EXTERNAL TABLES

      5. DYNAMIC MEMORY MANAGEMENT

Name five top 9I init.ora parameters affecting performance

      1. CURSOR_SHARING

      2. DB_CACHE_SIZE

      3. PGA_AGGREGATE_TARGET

      4. WORKAREA_SIZE_POLICY

      5. DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE

How do you recover database when you lost all of your control files?

In case of loss of all control files, you can still recover database as long as you have all archievelog files. You can issue the following command to recover the database :

RECOVER DATABASE USING BACKUP CONTROLFILES UNTIL CANCEL;

Apply all archive log files

ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
          Backup database ( COLD )
STARTUP;

Posted By -

Prayas Chaudhary

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**