ORACLE DBA INTERVIEW QUESTION AND ANSWER Vol_01

Oracle DBA Interview questions With Answer 

Question 01: Which of the following does not affect the size of the SGA

a) Database buffer
b) Redo log buffer
c) Stored procedure
d) Shared pool

Question 02. A set of Dictionary tables are created

a) Once for the Entire Database
b) Every time a user is created
c) Every time a Tablespace is created
d) None of the above

Question 03. The order in which Oracle processes a single SQL statement is?

A. Execute, parse, fetch
B. Execute, fetch, parse
C. parse, execute, fetch
D. parse, fetch, execute

Question 04. What are the mandatory datafiles to create a database in Oracle 11g?

A. SYSTEM, SYSAUX
B. SYSTEM, USERDATA, TEMP
C. SYSTEM, SYSAUX, UNDO

Question 05. In one server can we have different oracle versions?

A. No
B. Yes

Question 06. How do sessions communicate with database?

A. Server processes use oracle net to connect to the instance.
B. Background processes use oracle net to connect to the database
C. User processes read from the database and write to the instance;
D. Server processes execute SQL received from user processes.

Question 07: Which SGA memory structure cannot be resized dynamically after instance startup?

A. Database Buffer Cace
B. Java Pool
C. Large pool
D. Log buffer
E. Shared Pool

Question 08. When a session changes data, where does the change get written?

A. To the data block in the cache, and the redo log buffer
B. To the data block on disk, and the current online redo log file
C. The session writes to the database buffer cache, and the log writer writes to the current online redo logfile
D. Nothing is written until the change is committed

Question 09. How many maximum no of control files we can have within a database
A 3
B.5
C.1
D.8

Question 10.System Data File Consists of
A.Metadata
B.Business Data
C.Temporary Data
D.Undo Data

Question 11. What is the function of SMON in instance recovery?

A.It writes data to the archive log files.
B.It writes data to the online redo log files.
C.It frees resources held by user processes.
D.It synchronizes data file header and control files.
E.It roles forward by applying changes in the redo log.
F.It writes dirty buffers from the buffer cache to the data files.

Question 12: Which action occurs during a checkpoint?

A.Oracle updates a control file.
B.Oracle performs a delayed block clean-out.
C.Oracle copies the online redo-log to the archive destinations. `
D.Oracle flushes the dirty blocks in the database buffer cache to disk.

Question 13: SMON process is used to write into LOG files

a.TRUE
b.FALSE

Question 14: Oracle does not consider a transaction committed until

A. The Data is written back to the disk by DBWR
B. The LGWR successfully writes the changes to redo
C. PMON Process commits the process changes
D. SMON Process Writes the data

Question 15: How many maximum DBWn(Db writers) we can invoke?

A. 3
B. 1
C. 10
D. 20

Question 16: Which activity would generate less undo data?

A.INSERT
B.UPDATE
C.SELECT
D.DELETE

Question 17: What happens when a user issues a COMMIT?

A. The CKPT process signals a checkpoint.
B. The DBWn process wrtes the transactions changed buffers to the datafiles.
C. The LGWR flushes the log buffer to the online redo log.
D. The ARCn process writes the change vectors to the archive redo log

Question 18: What happens when a user process fails?

A.PMON performs process recovery.
B.SMON performs ICR
C.SMON frees resources held by user process.
D.Oracle Updates a controlfile.

Question 19: What are the free buffers in the database buffer cache.

                        A.Buffer that have changed should be pushed to the disk.
                        B.Buffers that are currently in use.
                        C.Buffer that are being written to disk.
                        D.Buffer that can be overwritten.


                       Question 20: When the SMON Procees perform ICR.
                       A.For every startup
                       B.Only at the time of startup after graceful shutdown
                       C.Only at the time of startup after abort shutdown

[
 Correct answer: 1(c), 2(a), 3(c), 4(c), 5(b), 6(d), 7(d), 8(a), 9(d), 10(a), 11(e), 12(d), 13(b), 14(b), 15(d), 16(a),17(c),18(a),19(d),20(c)
] 
Collected from http://www.reachdba.com