oracle interview questions

Oracle Interview Questions and Answers

156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?

Ans:  Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};

               EXEC SQL PREPARE readytogo FROM :c_sqlstring;

               EXEC SQL EXECUTE readytogo;

157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?

Ans: Oracle8 allows tables and Indexes to be partitioned or broken up into smaller parts based on range of key values. Partitioning is a “divide and conquer” strategy that improves administration and performance in data warehouse and OLTP systems.

159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?

Ans: The basic oracle memory structure associated with Oracle includes:

Software Code Areas The System Global Area (SGA) ,The Database Buffer Cache

The shared Pool, The Program Global Areas (PGA), Stack Areas ,Data Areas, Sort Areas

160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?

Ans: Database, Tablespace , DB Object, Segment, Extents

161. WHAT IS SGA?

Ans: A System Global Area is a group of shared memory allocated by

Oracle that contains data and control information for one Oracle database instance. IF the multiple users are concurrently connected to the same instance, the data in the instance’s SGA is “shared” among the users.

Consequently, the SGA is often referred to as either the “system Global Area” or the “Shared

Global Area”.

162. WHAT IS PGA?

Ans: The Program Global Area is a memory buffer that contains data and

control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.

163. WHAT IS AN ORACLE INSTANCE?

Ans: Every time a database is started, an SGA is allocated and Oracle

background processes are started. The combination of these processes and memory buffers is called an Oracle instance.

164. WHAT ARE DIFFERENT ORACLE PROCESSES?

Ans:  A process is a “thread of control” or a mechanism in an operating

system that can be execute a series of steps. Some operating systems use terms jobs or task.

 A process normally has its own private memory area in which it runs. An Oracle database system has general types of process: User Processes and Oracle Processes.

165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?

Ans: SMON (System Monitor) performs instance recovery at instance of

startup. In a multiple instance system (one that uses the parallel server), SMON of one instance can also perform instance recovery other instance that have failed whereas The PMON (Process Monitor) performs process recovery when a user process fails.

166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?

Ans:  Database is a physical Component

         Tablespace is a Logical component

167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?

Ans: The Data Base Writer writes modified blocks from the database

buffer cache to the data files.

168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?

Ans: The Log Writer writes redo log files to disk. Redo log data is

generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo entries into an online redo log file.

169. WHAT IS RECOVERER?

Ans: The Recover (RECO) is used to resolve distributed transactions that are pending due to network or system failure in a distributed database. At timed intervals, the local RECO attempts to concept to remote database and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

170. WHAT IS ARCHIVER?

Ans:  The Archiver (ARCH) copies the online redo log files to archival storage when they are full. ,ARCH is active only when a database’s redo log is used ARCHILOG mode.

171. WHAT IS A STORED QUERY?

Ans: VIEW

172. WHAT IS USER PROCESS AND SERVER PROCESS?

Ans: A User process is created and maintained to execute the software

code of an application program (such as PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process also manages the communication

with server processes. User processes communication with the server

Processes through the program interface.

Other processes call ORACLE processes. In a dedicated server

configuration, a server Process handles requests for a single user process. A multithread

server configuration allows many user processes to share a small number of server processes, minimizing the utilization of available system resources.

173. WHAT IS A SELF REFERENTIAL INTEGRITY?

Ans: Table related to itself .Foreign key of the table links to primary  key of the same table.

174. WHAT IS A "RAISE" STATEMENT?

Ans: It is used to Raise Exceptions.

175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?

Ans: Rowid is the address of the row at where it is stored in the

database. Rownum is count of records whereas Rowid is identification of the each row