DBA 1+ year Interview

 See Also : DBA Interview for 2+

Q. What is the difference between process and thread.

Creation of new process requires new resources and Address space whereas the thread can be created

in the same address space of the process which not only saves space and resources but are also easy to

create and delete, and many threads can exists in a process.

Q. What is main purpose of CHECKPOINT?

A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.

A checkpoint has 2 purposes:

-1. to establish data consistency

-2. Enable faster database recovery.

Q. What is PGA ?

PGA - Program Global Area

or the Process Global Area is a memory region that contains

data and control information for a single

server process or a single background process.

The PGA is allocated when a process is created and de-allocated when the process is terminated.

PGA is an area that is used by only one process.

Q. Different type of Database Failures

Statement failure failed SQL is automatically rolled back and an error is returned to user.

User Process failure abnormal disconnect PMON detects and rollsback and releases locks.

User Error (drop table, data) DBA is required to recover data (import or incomplete recovery)

Media Failure Loss or corruption of files DBA needs to apply appropriate recovery.

Instance Failure Abnormal shutdown Instance simply needs restarted, SMON auto

recovers by:

 -Rolling forward changes in the redo log not recorded in the data files before Open of database.

 -Rollbacks can occur after the database is open, when block data is requested.

Q. When should more than one DB writer process be used? How many

should be used?

If the UNIX system being used is capable of asynchronous IO then only one is required,

if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter

Q.What is the use of the RESUMABLE parameter in EXPORT?

The RESUMABLE parameter allows the export to

1. Suspend if a space allocation issue occurs

2. Wait until the space allocation issue is solved

3. Then resume and therefore not to abort, provided a timeout is set appropriately.

Q. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.

Q. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables.

They are typically used in data warehouse or decision support systems.

Tell me the two methods you could use to determine what DDL changes have been made.

Logminer or Streams

Q. 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?

A.) You can try one of the following to free up TEMP space

1. Issue ALTER TABLESPACE PCTINCREASE 1 followed by

ALTER TABLESPACE PCTINCREASE 0 command;

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

Interviewer may ask you - - ‘’Do you have something to Ask...?’’

Following are the Questions a DBA can Ask to the Interviewer

1.      What processes do you follow while implementing changes in production?

2.      Beside the DBAs and system administrators, who has access to the "Oracle"

operating system account?

3.      How often is the oracle operating system account password changed?

4.      Are the DBAs co-located with the teams they support? How is capacity planning performed?