Background Processes in Oracle

Background Process:

• Database writer (DBWR): Writes modified blocks from database buffer cache to the datafile.
• Log Writer (LGWR): Writes redo log entries to disk.
• Check Point: At specific times all modified databases buffers in the SGA are written to the data files by DBWR. This event is called Checkpoint.
• System Monitor (SMON): Performs instance recovery at instance startup
• Process Monitor (PMON): Performs process recovery when user process fails
• Archiver (ARCH): Copies on line redo log files to archival storage when they are full
• Dispatcher: For multi threaded server for each request one dispatcher process
• Lock: For parallel server mode for inter instance locking

Hot & Cold Backup

A cold backup is a physical backup. During a cold backup the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles do not change during the copy so the database is in sync upon restore.
 

Used when:

Service level allows for some down time for backup

A hot backup is a physical backup.  In a hot backup the database remains open and available to users. All files of the database are copied (image copy).  There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too.  Upon a restore, the changes in the log files are reapplied to bring the database in sync.

Used when:

A full backup of a database is needed
Service level allows no down time for the backup

A logical backup is an extract of the database.  All SQL statements to create the objects and all SQL statements to populate the objects are included in the extract.  Oracle provides a utility export, to create the extract.   A partner utility, import, is used to bring the data back into the database.

A logical backup can be done at the table, schema (or proxy owner), or database level.  That is, we can extract only a list of specified tables, a list of specified schemas or the full database.

Used to:

Move or archive a database
Move or archive a table(s)
Move or archive a schema(s)
Verify the structures in the database

Early Binding/Late Binding

When you generate RPCs (remote procedure calls) using SQL*Module, you have a choice of early binding or late binding. Your choice of early or late binding is controlled by the BINDING option.

When you choose early binding, SQL*Module generates a call to the procedure stored in the database, and also uses a time stamp that is associated with the call. The time stamp records the date and time (to the nearest second) that the stored procedure was last compiled. The time stamp is created by the Oracle database. If a host application calls the stored procedure through the interface procedure, and the time stamp recorded with the interface procedure is earlier than the time stamp on the stored procedure recorded in the database, an error is returned to the host application in the SQLCODE and/or SQLSTATE status parameter. The SQLCODE error is 4062 "time stamp of name has been changed".

The late binding option, on the other hand, does not use a time stamp. If your application calls a stored procedure that has been recompiled since SQL*Module generated the interface procedure, no error is returned to the application.

The advantages of late binding are

• Greater flexibility
• Changes in the stored procedure(s) are transparent to the user
• Gives behavior similar to interactive SQL (for example, SQL*PLus)

The disadvantages of late binding are

• There might be additional performance overhead at runtime, due to the necessity of compiling the PL/SQL anonymous block.
• It is difficult to detect runtime PL/SQL compilation errors in the host application. For example, if the anonymous block that calls the late-bound procedure fails at runtime, there is no convenient way for the host application to determine the cause of the error.
• The lack of time-stamp capability means that changes, perhaps radical changes, in the stored procedure could be made after the host application was built, and the application would have no way of detecting this. 

Purity Levels

Prior to Oracle8i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.

The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:

PRAGMA RESTRICT_REFERENCES (program_name |

   DEFAULT, purity_level);

The keyword DEFAULT applies to all methods of an object type or all programs in a package.

There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects. Side effects are listed in the following table with the purity levels they address.

Purity Level	Description	Restriction
WNDS	Write No Database State	Executes no INSERT, UPDATE, or DELETE statements.
RNDS	Read No Database State	Executes no SELECT statements.
WNPS	Write No Package State	Does not modify any package variables.
RNPS	Read No Package State	Does not read any package variables.
TRUST (Oracle8i)		Does not enforce the restrictions declared but allows the compiler to trust they are true.

The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:

To be called from SQL, all stored functions must assert WNDS.

All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.

To be executed remotely, the function must assert WNPS and RNPS.

To be executed in parallel, the function must assert all four purity levels or, in Oracle8i, use PARALLEL_ENABLED in the declaration.

These functions must not call any other program that does not also assert the minimum purity level.

If a package has an initialization section, it too must assert purity in Oracle7.

If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.

Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited. 

Procedure/Function (Difference)

Procedure

A procedure is a subprogram that performs a specific action

Procedure Does and Does not return the Value.

Procedure we can use (In, Out, InOut Parameter)

You cannot use the procedure in select Statement.

Execute as a PL/SQL statement

No RETURN clause in the header

Can return none, one, or many values

Function

A function is a subprogram that computes a value

Invoke as part of an expression

Must contain a RETURN clause in the header

Must return a single value

Must contain at least one RETURN statement

Always return the Value.

Function you cannot use the (In, Out, InOut Parameter)

You can use the Function the in select Statement.

SQL Loader (Different Types of Files/Contents)

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

When we speak of the SQL*Loader environment, we are referring to the database, the SQL*Loader executable, and all the different files that you need to be concerned with when using SQL*Loader. These are shown in Figure

The SQL*Loader environment

The SQL*Loader Control File

The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:

• The name and location of the input data file
• The format of the records in the input data file
• The name of the table or tables to be loaded
• The correspondence between the fields in the input record and the columns in the database tables being loaded
• Selection criteria defining which records from the input file contain data to be inserted into the destination database tables.
• The names and locations of the bad file and the discard file

The Log File

The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:

• The names of the control file, log file, bad file, discard file, and data file
• The values of several command-line parameters
• A detailed breakdown of the fields and data types in the data file that was loaded
• Error messages for records that cause errors
• Messages indicating when records have been discarded
• A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

The Bad File and the Discard File

Whenever you insert data into a database, you run the risk of that insert failing because of some type of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.

Discard files, on the other hand, are used to hold records that do not meet selection criteria specified in the SQL*Loader control file. By default, SQL*Loader will attempt to load all the records contained in the input file.

Bitmap/B-Tree Index (Difference, Advantages)

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.

Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.

Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.

Inline Views & Top N Analysis

The Inline view: It is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

           

Top N Analysis: The task of retrieving the top or bottom N rows from a database table. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK () and DENSE_RANK ().

Using the ROWNUM Pseudocolumn
One-Way to solve this problem is by using the Oracle pseudocolumn ROWNUM. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

E.g. To select top 5 rows

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

   FROM

   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

      FROM Emp

      ORDER BY NVL (Sal, 0) DESC)

   WHERE ROWNUM < 6;

Utilizing Oracle 8i's Ranking Functions

Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions.

SELECT Empno, Ename, Job, Mgr, Sal,

   RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Rank,

   DENSE_RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Drank

   FROM Emp                             

   ORDER BY SAL Desc NULLS LAST;

The difference between RANK () and DENSE_RANK () is that RANK () leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK () but only fourth place using RANK (). The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

List Item Types

Poplist: The poplist style list item appears firstly as a single field (similar to a text item field).  When the end user selects the list icon, a list of available choice appears.

Tlist: The Tlist style list item appears as a rectangular box, which displays a permanent number of values.  When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.

Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.

Different Database Triggers

Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement.

 For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.

If a triggering statement affects no rows, a row trigger is not executed at all.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.

Statement Triggers

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).

 For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers

When defining a trigger, you can specify the trigger timing.

That is, you can specify whether the trigger action is to be executed before or after the triggering statement.

BEFORE and AFTER apply to both statement and row triggers.

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:

BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.

BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:

AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.

If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations

Using the options listed in the previous two sections, you can create four types of triggers:

BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.

BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.

AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.

AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

New Database Triggers

Startup, Shutdown, Logon, Logoff, Alter, Create, Drop

Various Cursor Attributes

SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.

SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.

SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.

SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

P/L SQL Tables / Arrays

PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.

Rules for PL/SQL Tables:

1.      A loop must be used to insert values into a PL/SQL Table

2.      You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.

Diff b/w Package Spec & Body

Packages provide a method of encapsulating and storing related procedures, functions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).

Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.