Oracle – Technical FAQ – Part 1

b) LogWriter(LGWR) : LogWriter writes the redo log entries to disk. Redo log data is generated in redo log buffer of SGA. As transaction commits and logs buffer fills, LGWR writes log entries into a online redo log file.

c) System Monitor (SMON) : The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure.

d) Process Monitor (PMON) : The Process Monitor performs process recovery when user process fails. Pmon clears and frees resources that process was using.

e) CheckPoint (CKPT): At specified times, all modified database buffers in SGA are written to data files by DBWR at checkpoints and updating all data files and control files of database to indicate the most recent checkpoint.

f) Archiever (ARCH) : The archiever copies online redo log files to archival storal when they are busy.

g) recoveror (RECO) : The recoveror is used to reslove the distributed transaction in network.

h) Dispatcher (Dnnn) : The Dispatcher is useful in Multi Threaded Architecture.

i) Lckn : We can have upto 10 lock processed for inter instance locking in parallel sql.

Q2) How many types of sql statements are there in Oracle?

A2) There are basically 6 types of sql statements. They are

a) Data defination Language (DDL) : The DDL statements define and maintain objects and drop objects.

b) Data Manipulation Language (DML) : The DML statements manipulate database data

c) Transaction Control Statements : Manage change by DML

d) Session Control : Used to control the properties of current session enabling and disabling roles and changing e.g. Alter statements , Set Role

e) System Control Statements : Change properties of Oracle Instance e.g. Alter System

f) Embedded Sql : Incorporate DDL, DML and TCS in processing Languagel e.g. Using the SQL statements in language such as ‘C’, Open, Fetch, Execute and close

Q3) What is a transaction in Oracle ?

A3) A transaction is a logical unit of work that compromises one or more SQL statements executed by a simple user. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly commited or rolled back.

Q4) What are the key words used in Oracle?

A4) The key words that are used in Oracle are

a) Commiting : A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements

b) Rollback : A transaction that retracts any of the changes resulting from SQL statements in Transaction.

c) Savepoint : For long transactions that contain many SQL statements , intermediate markers or savepoints are declared. Savepointes can be used to divide a transaction into smaller points.

d) Rolling forward : Process of applying redo log during recovery is called rolling forward.

e) Cursor : A cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the SQL statement. Oracle uses an implicit cursor statement for single row query and uses Explicit cursor for a multirow query.

f) System Global Area (SGA) : The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database buffre cache and redo log buffer.

g) Program Global Area (PGA) : The PGA is a memory buffer that contains data and control information for server process.

h) Database Buffer Cache : Database buffer of SGA stores he most recently used blcoks of database data. The set of database buffers in an instance is called Database Buffer Cache.

i) Redo log buffer : Redo log buffer of SGA stores all the redo log entries.

j) Redo log files :Redo log files are set of files that protect altered database in memory that has been not written to Data files. They are basically used for backup when a database crashes.

k) Process : A process is a ‘thread of control’ or mechanism in Operating System that executes series of steps

Q5) What are procedures, functions and packages?

A5) Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

Procedure do not return values while functions return one and only one value.

Packages provide a method of encapsulating and storing related procedures, functions and other package contents.

 more questions are here : https://knoworacle.wordpress.com/2008/06/08/oracle-technical-faq-part-2/

Part 3 is here :   https://knoworacle.wordpress.com/2008/06/09/oracle-technical-faq-part-3/