(Continued from previous part...)

175. What is Rollback Segment?

A Database contains one or more Rollback Segments to temporarily store “undo” information.

176. What are the Characteristics of Data Files?

A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

177. How to define Data Block size?

A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

178. When a user process fails, what background process cleans up after it?

PMON

179. What background process refreshes materialized views?

The Job Queue Processes.

180. How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

181. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command?

The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

182. What is the function of Optimizer?

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

183. What is Execution Plan?

The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

184. What are the different approaches used by Optimizer in choosing an execution plan?

Rule-based and Cost-based.

(Continued on next part...)