Authentication

Oracle Syntax
Users and authentication rules are defined in the database using the CREATE USER and ALTER USER SQL statements. There is some difference in the available syntax for these two commands, but we will
attempt to cover all of the syntax options. Following is a demonstration of some of the basic capabilities
for defining users to the database:

create user joe identified by newpass1;
create user cam identified by origpass password expire;
create user ops$mary identified externally;
create user chris identified globally as ‘CN=chris,O=xyzcorp’;
create user app_proxy_user identified by app_serv_pw;

alter user joe password expire;
alter user cam identified by newpass3 replace origpass;
alter user ops$mary account lock;
alter user chris grant connect through app_proxy_user
authentication required;

In this series of commands, you see the creation of five new database user accounts. The first, “joe,” is cre-
ated with a password that will be used for authentication. The second, “cam,” is given a password also,but it is set to expire so that it will have to be reset by the administrator or “cam” before the account can
be used to connect to the database. The “ops$mary” account will be authenticated using an external
mechanism (the operating system) rather than by supplying a password; the O/S user “mary” will be
able to connect directly to the “ops$mary” database account. User “chris” will be authenticated through
the use of an Lightweight Directory Access Protocol (LDAP) directory, the topic of another section later in
this chapter. The final account being created is going to be a proxy account used by an application server
to be able to maintain a connection and share it among various application users. This is different than
simply having the application server use a single account for multiple end user transactions in that the
database will be able to track the individual users who are proxied. This is useful for maintaining different
authorizations for individual users, for applying virtual private database policies, or for proxy auditing,
discussed later in this chapter. The fundamental purpose of using proxy users is the ability to preserve the
actual user identity even though the actual connection is made through a “generic” user connection.
(The ops$ prefix is a default used for external authorization through the operating system but it can be
modified by setting the OS_AUTHENT_PREFIX initialization parameter.)
The code example continues by showing five examples of altering existing user accounts to change
authentication rules. First the password for the “joe” account is explicitly expired by the DBA (although
you’ll see later how to force periodic password expiration automatically.) Next, the “cam” password is
explicitly changed. This can be done by the DBA or by cam directly if cam has the alter user privilege.
(The extra clause shown here of REPLACE current_password is only needed when a user is changing
his or her own password and a password complexity routine has been specified by the administrator.)
The third change shown locks the “ops$mary” account so no logins will be allowed.
The final example alters the “chris” account to allow connections for this account to be made using the
shared “app_proxy_user” account that was created for this purpose. (This example shows the syntax
from Oracle 10g; slightly different syntax for proxy authentication was available in release 9i.) You will
see how such proxy accounts are used later in the chapter.

We mentioned that password complexity routines can be defined by the DBAto ensure that users specify
passwords that meet certain minimum standards for complexity to reduce the chance of someone guessing
their password or discovering it through repeated trial and error. A password complexity routine is
assigned as part of a profile that is assigned to a user. Typically, an organization will define one or more
standard profiles, and every user account will be assigned one of these when the user is created. Profile
assignments can also be changed:
alter user joe profile corp_security_profile;
Profiles must first be created by the DBA using the CREATE PROFILE command. Profiles are used for two
purposes, as a means of controlling resources used by an account and for enforcing password and other
authentication rules. Here is an example of password and login control:
CREATE PROFILE corp_security_profile
LIMIT
failed_login_attempts       5
password_lock_time          1
password_life_time         90
password_grace_time         3
password_reuse_max          5
password_reuse_time       365
password_verify_function  f_corp_pwd_vfy;
Most organizations will not necessarily implement all of the available password rules as shown in this
exhaustive example. This command says that any user who has been assigned the corp_security_profile will have his or her account locked after five successive failed attempts to log
in and that the account will remain locked for 24 hours (one day) unless explicitly unlocked by the DBA.
Any password can be used for no more than 90 days, but the user will receive advance warnings for
three days before the actual password expiration. A user’s password cannot be reused until five other
password changes have been made, and a password can’t be reused within 365 days.
Finally, the profile specifies that all passwords will be validated using a verification function, named
f_corp_pwd_vfy. This function can perform any tests on the actual proposed password string and will
either accept or reject the password. If rejected, the user will have to select another password. Before this
profile can be created this function will have to be written using PL/SQL using techniques discussed
later in Chapter 13, “Functions.” Since many readers don’t have extensive PL/SQL experience yet, we’ll
show an overly simple example of how this function might be coded just to ensure that a user doesn’t
use his or her username as a password. (This function must be created by the privileged user SYS in
order for it to be used in a profile.)

CREATE OR REPLACE FUNCTION f_corp_pwd_vfy
(in_username       IN VARCHAR,
in_new_password   IN VARCHAR,
in_old_password   IN VARCHAR)
RETURN BOOLEAN
AS
pwd_okay BOOLEAN;
BEGIN
IF in_new_password = in_username THEN
raise_application_error(-20001, ‘Password may not be username’);
END IF;
RETURN TRUE;
END f_corp_pwd_vfy;
Remember this simple example only makes one test of password validity. Oracle provides a script
(utlpwdmg.sql, found within the rdbms/admin subdirectory under the ORACLE_HOME directory) to
create a more useful password verification routine or to use as a template for your own routine. Figure
5.1 shows what happens when a user who has been assigned this profile attempts to change his
password in violation of the rule.

Third Normal Form

Once an entity is in 2NF, the final test of purity of design that is  performed looks for transient dependencies
among the entity’s attributes. In this third normal form (abbreviated as 3NF), you verify that each attribute is
really dependent upon the key as opposed to being dependent upon some other attribute that is dependent
upon the key. Did that make sense? Let’s look at a violation of 3NF as a way to better understand it.
Look back at Figure 4-3. There’s another questionable attribute in the first-pass POLICY entity attribute list.
Does ANNUAL_MAX_OUT_OF_POCKET_EXP really belong here? Is this dependent upon the key, POLICY_ID?
Perhaps. But it may be true that this value changes each year as the policy is renewed. If so, the value of
ANNUAL_MAX_OUT_OF_POCKET_EXP is dependent upon the POLICY_ID and the year. Since you may need
to process CLAIMs from one year during the next year, it might be necessary to keep both the current year
and some previous years’ values in the database. This attribute, and perhaps some others, really should be
put into a new entity, POLICY_YEAR. Further discussion of requirements will be needed to know for sure.
Let’s continue our examination of Figure 4-3’s proposed list of attributes just a little longer. What about
AGENT_NAME? It can be argued that the agent for any given policy should be known once you identify
the specific policy, so there isn’t a clear violation of first normal form. However, as you continue to flesh
out the design, you are likely to find that you need an AGENT entity to which the POLICY entity will be
related. When that happens you will want to carry the key (such as AGENT_ID) within the POLICY entity
and use AGENT_NAME as a non-key attribute within the AGENT entity. This would be a better 3NF design
if you are going to track any other information about agents besides their name.

Second Normal Form

Sometimes a table requires a multiple part key. If so, then every attribute (column) must be dependent
upon the entire multiple part key and not just part of the key. There is one example in the insurance data
model (created by resolving the many-to-many relationship). Each CLAIM must have at least one
CLAIM_TREATMENT.
To build the key to this new entity, you will have to include the CLAIM_ID (key to the CLAIM entity) and
another key that will relate each row to a particular TREATMENT entity key. Deciding which attributes
belong with CLAIM and which belong with CLAIM_TREATMENT is done by assessing whether the attribute
can be uniquely identified by just the CLAIM_ID or whether you will have to know which specific TREATMENT
of that CLAIM. Therefore, in Figure 4-4, you see that TREATMENT_ID has been included in this entity.
CLAIM_DATE has been left in the CLAIM entity because it applies to the entire claim. Further analysis may
lead to additional attributes that are dependent upon this entity’s composite key, but frequently intersection
entities like this carry just the key values that related it to the two primary entities in order to resolve the
many-to-many relationship.

So, the rule of second normal form (2NF) is that, for any entity that has a multiple-column key, all data
attributes in that entity must be dependent upon the full key, not just part of that key. If the value of a
column could be determined strictly through knowing part of the key, then that column doesn’t belong
in this entity (or else you have not correctly defined the key structure.).
When you find that you need multiple columns to identify an instance of an entity, our rule not allowing
keys to be null now applies to each of the columns in the multipart key.

First Normal Form

Because the relational database (as least logically) links data only through data values, there must be
something in the data that lets you find a specific row when you want it. This identifying key may be one
column (perhaps ORDER_ID in an ORDERS table), or it may require multiple columns (such as the combination
of ORDER_ID and ORDER_LINE_NUMBER within an ORDER_LINES table.)
We’ll be using this concept of keys throughout the discussion of normalization in the following sections.
There are several forms of keys (primary keys, candidate keys, foreign keys) that we’ll address more pre-
cisely when we get to the physical design stage. For now, we’ll be using the general term” key” to
loosely refer to the primary key of a physical table.

So, for first normal form, each column must be related to the key of the table, but more than just related—
each should be dependent upon the key for its identity. Another way of stating this is that for a table in
first normal form, if you know the value of a row’s key, then you can determine, without ambiguity, the
value for each column in the table. Once you find a row in that table using a key value you have also
found the single value for each of the other columns of that row.

Entity-Relationship Modeling

Entity-Relationship Diagrams (ERDs) attempt to accomplish two things early in the design of a database. First they document the entities of interest to the enterprise. Entities may represent things (such as a customer, an
employee, or a product) or an event (like a registration, a shipment, or an order). Entities have identifying
characteristics and additional descriptive characteristics. For instance, an employee has an employee number,
a name, a home address, a work location, a supervisor, a hire date, a pay rate, and so on. One of the challenges
during design will be sorting through all of the available data “thingies” to figure out which are entities, which
are identifiers of entity instances, and which are descriptive of particular instances of an entity.
In addition to documenting entities, an ERD documents the relationship between entities. From the list
of example entities in the previous paragraph, you might conclude that there is a relationship between a
customer and a product. It might be direct (a CUSTOMER owns a PRODUCT) or it might be more complex
set of relationships:
❑ each CUSTOMER may place one or more ORDERs
❑ each ORDER must include one or more PRODUCTs
❑ each SHIPMENT must include at least one PRODUCT to one and only one CUSTOMER
❑ each ORDER may be fulfilled with one or more SHIPMENTs
❑ a SHIPMENT may include parts of one or more ORDERs for one and only CUSTOMER

This list is an example of the relationship rules that might exist for a company. Another company with
the same entities may have very different relationships and rules. For instance, an alternative relationship
might be that “a SHIPMENT fulfills one and only one ORDER”. Sorting through these relationships and the
business rules that define them is the essence of designing a database that will meet the requirements of
a specific business. Getting one rule wrong during logical design can lead to a database that will not be
able to support necessary business processes.
Setting aside the order entry discussion, see Figure 4-1 for a quick illustration of the ERD modeling
technique for documenting a logical database design that might be part of a (highly simplified) insur-
ance billing system. Each box designates an entity . . . an object of importance to the enterprise. The ERD
doesn’t attempt to show all of the low-level data elements (attributes) associated with each entity. At
most, identifying elements (keys) are shown. A total of seven entities have been shown in this example.

While we might have shown INSURED and POLICY_OWNER as completely distinct entities, this model
assumes that there are many characteristics of the two that overlap, so they have been shown as two
subtypes of the PERSON entity.

As we said, the second aspect of an ERD is to document the relationships between entities, as shown by
the connecting lines. One line is drawn for each relationship; every entity should be expected to have at
least one relationship to some other entity, but not all entities are necessarily related to every other entity.
It is possible for two entities to have more than one relationship.

Further, it should be recognized that every relationship exists in two directions. If entity Ahas a relationship
to entity B, then entity B has a relationship to entity A. Asingle line defines this bidirectional relationship,
but it is useful to define the relationship in both directions. Many methodologies and tools are not strict
in this regard, but it helps when reading an ERD to be able to traverse the relationships without having
to translate on the fly. In Figure 4-1, the relationship between POLICY and CLAIM entities can be stated in
two ways: (1) A POLICY may have one or more CLAIMs submitted and (2) A CLAIM must be submitted
against exactly one POLICY.
The nature of each relationship may be optional or mandatory in one direction or both. In this statement
of the bi-directional relationship between the POLICY and CLAIM entities, the term “MUST” indicates
that the relationship is mandatory in that direction (a CLAIM cannot exist without an associated POLICY.)
A solid line in the ERD indicates that the relationship, in that direction, is mandatory.
An optional relationship exists in the other direction—the term “MAY” informs us that a POLICY is
allowed to exist without any associated CLAIMs. In the ERD, a broken line indicates that end of the line
to indicate that the relationship from that direction is optional.
The degree of each relationship, additionally, may be a one-to-one correspondence between the entities
or, more commonly, a one-to-many relationship. Where many occurrences of an entity may exist for a
relationship, a “crow’s foot” is used to designate the many end of the one-to-many relationship, such as
the fact that multiple CLAIMs are allowed for a POLICY. The written description of the relationship uses the
“one or more” phrase to designate this.
Where only one occurrence of an entity may participate in the particular relationship, no crow’s foot is
used and we’ll describe the relationship using “one and only one” or “exactly one” terminology.

Many-to-many relationships do occur in the real world, and during this logical design phase, they may
appear in our ERD. Figure 4-1 showed such a relationship between CLAIM and TREATMENT. Many-to-many
relationships, however, present difficulties in creating a physical database. Remember that a relational
database makes its connection between tables using data values themselves rather than pointers. We will
eventually, during physical design, implement a one-to-many relationship by carrying the key value of
the “one” side as a data value stored within each row on the “many” side table. Many-to-many relationships
can’t be stored that way, so data modelers will resolve the many-to-many relationship into two one-to-
many relationships and by creating a new connecting entity to help define the relationship. This issue
was ignored in the preliminary example, but Figure 4-2 shows that specific portion of our total ERD with
this relationship resolved.

Another common construct included within an ERD are “Type of” subentities exemplified by INSURED
and POLICY_OWNER that have been shown as subtypes of the PERSON entity. At a later revision of this
ERD, some differences in types of PROVIDERs might have been discovered that would lead to a similar
subtyping for doctors, hospitals, physical therapists, and pharmacies.
One other ERD diagramming convention shows “one of” choices among multiple relationships. This option
is shown by the arc crossing the relationship lines connecting the CLAIM entity to DENIAL and PAYMENT with
an arc. According to our simple ERD, either (but not both) of these may exist for any given CLAIM.
The primary advantage of ERD diagramming is that ERDs are easily explained to nontechnical users and
sponsors of a proposed system. Most business people can easily validate the important entities, their identifying
attributes, and the rules that define the relationships between the entities in their real world. Asecond advan-
tage is that ERDs are very easily translated from their logical representation to a preliminary physical design of
a relational database. It is, after all, a relational technique. The third advantage of ERD modeling is that ERDs
can scale to show the total information model of an entire enterprise or a restricted view can present just the
subset of entities of interest to a particular system. (Pulling a small part of the entire ERD, as in Figure 4-2, is an
example of this capability.) This allows enterprise data architects a wonderful way to “zoom” in and out to
examine global relationships or detailed implications of a proposed change in one particular program.
The disadvantage of ERD modeling is that it is primarily a data-focused methodology and doesn’t
attempt to model the processing needs of the application. Processing will have to be designed to support
each relationship, but the ERD doesn’t suggest how that processing will be handled. ERDs, as valuable
as they are, require a complementary design and documentation technique to handle processing needs
and module decomposition.

Database Design Phases

Logical Database Design
From a list of requirements (as they are discovered and documented), the team initially identifies a long
list of data elements that they know must be included in the database. They additionally sort the data
elements into groupings, informally at first and then with more rigor, around the entities of importance
to the organization.
Logical design is performed without concern to the specific database management system that will
eventually host the application’s data. The logical database design for a system will look identical
whether the eventual system will be hosted on DB2 on the mainframe or on SQL Server on a Windows
workstation. That isn’t to say that the eventual systems will be identical—it’s just to say that those
differences don’t appear during logical design.

During the logical design phase the development team determines what data needs to be acquired, stored,
and used by a particular application. Simultaneously, the team identifies what functional processes need
to be performed by the application. As each model is refined, it is compared to the other to ensure that
needed data for each process is included in the data model and that each element of data included in the
data model has some identified processes that will create, use, and (usually) modify or purge the data.

For all of the data elements collected in this phase, it is necessary to begin documenting some basic
characteristics of the data. You need to know where the data comes from (if it isn’t being created by the
new application), and you will want to gather some preliminary estimates of the type of data:
❑ Is it strictly numeric? Will it always be integers or allow decimal values?
❑ Is the data item a date? If so, what granularity is required . . . to the day? to the minute? to the
millisecond?
❑ Will the data be alphanumeric or will it need to include other characters?
❑ What if a needed element isn’t character data but an image or some other unstructured object? I
so, how large do you expect each instance to be?
❑ Is there a defined domain (range or list of allowable values) associated with the data? Does the
domain relate to other data expected to be in your system? Does the domain have an external
definition rule (such as spatial coordinates limited to valid latitude and longitude pairs)?
❑ What is the expected lifetime of this particular data element? Is it transitory or does it continue
to exist for a predictable period of time?
Gathering an initial shopping list of data elements is a necessary but relatively casual process during the
early parts of logical system design. Some discipline, however, is soon needed. The primary means of
organizing this rapidly growing list of data elements is normalization and is traditionally documented
using Entity-Relationship Diagram (ERD) techniques. Both ERDs and the process of normalization are
covered in more detail in a later section of this chapter.

Performance Implications

All this use of rollback buffers sounds like it might take up a lot of resources, which would impact perfor-
mance. Granted, the Oracle database is a complex piece of software, but rollback buffers have been built into the core of the Oracle database for over 15 years. Oracle uses a variety of techniques to reduce the overhead of using this unique architecture. Although we hesitate to make performance claims for any software, since much of performance depends on good design and implementation, industry standard benchmarks have
shown the Oracle database to certainly be one of the fastest and most scalable databases in the world.
There is another side performance benefit of the way that the Oracle database implements locking that
is not directly related to MVRC but does have to do with locks. Most other databases manage locks
in memory. Since these databases typically use more locks than Oracle, they keep track of these locks in
memory for faster performance. Memory is typically one of the more constrained resources, so the over-
head of managing locks can take away from the memory used by the database. In addition, database
administrators have to make sure that there is sufficient memory to manage all the locks in the database,
since a problem with the lock manager can impact the entire database.
Oracle does not store locks in memory. Instead, the Oracle database keeps track of row locks in the header
of the physical page that contains the row. This header also stores the location of a previous version of its
rows in the rollback segments. If a row is going to be accessed for an SQL operation, the page it resides on
has to be retrieved, so the lock information will be present in the page header. Because of this, there is no
need for a separate lock manager. In addition, there is no increased overhead for an increased number of
rows with locks. So Oracle not only eliminates the number of locks used to handle large numbers of users
but also eliminates one of the ways that larger numbers of users demand greater resource usage.
Of course, the performance benefits delivered by reduced contention can be extremely significant. It
doesn’t matter how fast your database can go if you are waiting on locked rows. Add to this the fact that
you, as a developer, don’t have to spend any time writing code to achieve this level of concurrency and
data integrity, and you have a significant advantage overall.

Multiversion Read Consistency

The somewhat cumbersome name multiversion read consistency, henceforth referred to as MVRC, does
describe how the Oracle database works to provide its unique consistency. Whenever any transaction
changes data in the database, Oracle creates a new version of the changed row. Oracle uses its rollback
buffer to store earlier versions of rows. Simple enough, but the value it provides is impressive.
Each row version is marked with the system change number, or SCN, of the transaction that changed the
values. Every transaction gets a global SCN number assigned in the order that the transaction was
started. The SCN provides an absolute record of the serial order of the transaction.
When a transaction is retrieving rows to satisfy a query, it checks the SCN of the latest version of the
row. If the SCN is smaller than the SCN associated with the transaction, the row version is retrieved. If
the SCN of the row is larger than the transaction SCN, Oracle automatically knows that it cannot use
that version of the row. Oracle reaches back into the rollback buffers to retrieve an earlier version of the
row with an appropriate SCN. This process is shown in Figure 3-4.
If, for some reason, Oracle cannot find an appropriate version of the row in the rollback buffers, the
database will return an ORA-01555 error, which reads as “Snapshot too old.” You should size your
rollback buffers to be large enough to avoid this type of error. Starting with Oracle9i, you can choose to
allow the database to automatically handle UNDO, which should eliminate these errors.

MRVC gives each user a specific view of their data, at the point in time that their transaction began.
Because of this, there is no need for Oracle to use read locks in their locking system. No read locks
means less contention in two ways.
First, write operations do not block read operations. If another user changes data during the course of a
transaction, there is no need to protect the data, since the version of the data that has been read will be
stored as a version in the database.
Even more importantly, read operations do not block write operations. Since no read locks are used, a
transaction can grab an exclusive write lock whenever it needs to, regardless of whether anyone is read-
ing the data at the time or not.
For both of these reasons, you typically have less contention with an Oracle database, which not only
means better overall performance at run time but less time spent developing applications that run
against Oracle, since you don’t have to add logic to deal with either the possibility of contention or the
repercussions of that contention.
The Oracle database keeps track of the SCN of the oldest active transaction. When it needs to reclaim space
in the rollback buffer, it cleans out versions of data that are no longer required by any active transactions—
in other words, data rows that have an SCN number smaller than the SCN of the oldest active transaction.

Locks

A lock in a database performs a similar function to a lock on a door—it prevents access, but to data
instead of a room. Locks are used to isolate activity during a transaction and are automatically released
when a transaction is concluded through a COMMIT or a ROLLBACK.
There are two basic types of interactions with data, which call for two different types of locks. When you read a piece of data, you don’t care if other people also read it. This type of data sharing is the core of a multiuser database. This requirement calls for a shared lock, sometimes known as a read lock. Many different people can read the same data, and many shared locks can be placed on an individual row. Shared locks prevent the problems of non-repeatable reads.

But since there is only one physical copy of a row in a database, you cannot have more than one user writing to a row at a time. To enforce this limitation, and to avoid problems like lost updates, the database uses exclusive locks, sometimes known as write locks. An exclusive lock prevents more than one user from accessing a piece of data.
You may have noticed our strict use of the phrase “piece of data” rather than “row.” Locks are a physical
entity placed on a physical entity. A lock can be placed on a row, or the page that row resides in. As you
will quickly realize in the discussion of contention that follows, page locks can cause a lot more performance
degradation than a row lock. Oracle was one of the first databases to support row locking, but other
databases have gradually implemented this level of lock granularity.

Since an exclusive lock only allows a single user for a piece of data, the database cannot issue an exclusive
lock for a piece of data that already has a shared lock on it. This limitation makes sense from the point of
the user who is writing data, as well as the user who is reading the data. If the database allowed another
user to change the data in the middle of your transaction, the result could be either a lost update, a
nonrepeatable read, or some phantom data.

Concurrent User Integrity Problems

Transactions

The transaction is the key tool used to deal with both the issue of data integrity and to address the problems
created by multiple simultaneous requests for the same data. Atransaction is defined as a unit of work. Within
a transaction, all of the SQL activity aimed at a database is guaranteed to be completed, and at the same time.
You can begin a transaction either explicitly, with the BEGIN TRANSACTION syntax, or implicitly, by issuing an
SQL statement. You end a transaction by either writing the changes to the database and making them visible
to other users, with the keyword COMMIT, or by erasing all the work in the transaction, with the keyword
ROLLBACK. The code for a simple transaction is shown in the following example, with the optional keywords BEGIN TRANSACTION included:
BEGIN TRANSACTION;
UPDATE USER_ACCT SET BALANCE = BALANCE + :nAmount WHERE ACCT_ID = :nDestAcct;
UPDATE USER_ACCT SET BALANCE = BALANCE - :nAmount WHERE ACCT_ID = :nSourceAcct;

COMMIT;

This code illustrates the classic use of a transaction. The transaction is taking money from one account and
moving it to another account. You would not want one part of this transaction to succeed and the other
part to fail. Although the SQL that adds an amount to the destination account actually comes before the
SQL that subtracts the same amount from a source account, the order of statements in the transaction
doesn’t matter. In a transaction, either the results of all the statements are applied permanently to the
database or all the transactions are rolled back with the syntax ROLLBACK.
Within transactions, a ROLLBACK rolls back all the changes made by the transaction. There may be times
when you do not want to roll back all the changes, just some of them. To handle these situations, you can
use the SAVEPOINT syntax. A ROLLBACK either rolls back all the work for the entire transaction or just to
the point of the last checkpoint. In the following example, the first piece of SQL shown is adding a line to
an overall order. The application allows a user to delete just that line of an order without destroying the
entire order. The SAVEPOINT statement sets a point for the end of a rollback in this scenario.
.
.
INSERT INTO ORDER_HEADERS (CUSTOMER_NAME, ORDER_HEADER_ID) VALUES (:sCustomerName,
:sOrderHeaderID);
SAVEPOINT first_insert;
INSERT INTO ORDER_LINES (ORDER_HEADER_ID, ITEM_ID, QUANTITY) VALUES
(:sOrderHeaderID, :sItemID, :nQuantity);
IF ORDER_LINE_DELETED
ROLLBACK TO first_insert;
ENDIF;
.
.
The transaction provides not only the mechanism to ensure logical data integrity but also the container
for implementing user isolation. Until a transaction is committed, the changes made by the transaction
are not visible to any other users in the database. But multiple users, accessing the same sets of data, can
create problems of data integrity.

Parallel Operations

Have you moved recently? If you moved yourself, you probably found that having more people working
together resulted in reducing the overall time that it took to complete the job. Of course, there was no
doubt some overhead involved in getting everyone to work together, and there may have been a point
where getting even more people involved actually increased the time to complete the job.
Oracle implements a similar type of work sharing called parallelism. Parallelism makes it possible for Oracle
to split up the work of a specific SQL statement among multiple worker tasks to reduce the elapsed time to
complete the SQL operation.

Oracle has had the capability since version 7, although the scope of this functionality has been continuously
improved. Oracle now supports parallel operations for queries, updates, and inserts, as well as for backup,
recovery, and other operations. Parallelism for batch-type jobs like loading data is also supported. Parallel
operations are only available with the Enterprise Edition of Oracle 10g Database.
Oracle implements parallelism on the server, without requiring any particular code to work. This final
section will look at how Oracle implements parallel operations to improve performance.

Managing Parallel Server Processes

The Oracle database has a pool of parallel server processes available for execution. Oracle automatically
manages the creation and termination of these processes. The minimum number of parallel processes is
specified in the initialization parameter PARALLEL_MIN_SERVERS. As more parallel execution processes
are requested, Oracle starts more parallel execution processes as they are needed, up to the value specified
in the PARALLEL_MAX_SERVERS initialization parameters. The default for this parameter is 5, unless the
PARALLEL_AUTOMATIC_TUNING parameter is set to TRUE, in which case the default is set to the value of
the CPU_COUNT parameter times 10.

Using Bind Variables

The previous performance tip centered around how the Oracle database returns data to the user. This next
tip has to do with the exact way that the Oracle database processes SQL statements. Unlike the previous
tip, which did not affect your application code, this area requires that you implement your code in a par-
ticular way to reduce the use of resources and improve the performance of your Oracle database.
Earlier in this chapter, we discussed how Oracle stores SQL statements in the shared pool area of memory.
When the Oracle database receives an SQL statement, it checks the shared pool to see if an optimized execution plan already exists for the statement. If the plan exists in the pool, the plan is simply retrieved from memory, which is much more efficient than reoptimizing the statement. Use of the shared pool helps Oracle to scale for large numbers of users and perform well with any load.
If you have to reoptimize every statement, each statement adds to the overall workload of the target Oracle
database. This means that the overhead on your system increases with the number of statements and that,
eventually, you will run into resource limitations that will decrease performance. If every statement has to
go through the complete cycle of execution, your scalability will be limited.
Fortunately, a real-world application is not a series of unique SQL requests. In fact, most applications use
the same SQL statements over and over again. Theoretically, this would mean that the repeated SQL will
be picked up from the shared pool, which is much less expensive from a resource standpoint. Since Oracle
needs exclusive access to some resources when optimizing an SQL statement, optimizing SQL statements
cannot be done in parallel, so the more optimizations Oracle has to do, the greater the elapsed time for a
query to be processed.
The way to reduce this potential bottleneck is to help your Oracle database to avoid performing hard
parses as much as possible. You can help this to occur by using bind variables to help re-use SQL
statements.
Remember that the method used to identify a statement is a comparison of the hash algorithm created
from the statement. The value of this hash is derived from the characters in the statement.

Consider the following two statements:
SELECT ENAME FROM EMP WHERE EMP_ID = 7
SELECT ENAME FROM EMP WHERE EMP_ID = 5
You can quickly tell that these two statements should use identical optimizer plans. If the execution plan
for the first statement is still in the shared pool, Oracle should use it, right? Unfortunately, Oracle may not
be able to find the plan, since the hash value created by the second statement will very likely be different
from the hash value created by the first statement, based on the different characters.
At this point, bind variables come to the rescue. A bind variable is a placeholder in an SQL statement.
Oracle can process a statement containing a bind variable but can only execute the statement when it
receives the value of the variable.
You identify a bind variable by preceding it with a colon in the SQL statement. The previous SQL statements
could both be represented by the single SQL statement following, which uses a bind variable.
SELECT ENAME FROM EMP WHERE EMP_ID = :n_EmpID
If you use this syntax twice, instead of using the two different SQL statements shown previously, Oracle
will be able to retrieve the execution plan from the shared pool, saving lots of resources.
The statements used to illustrate the use of bind variables previously are not that complex, so you may
doubt how much the use of bind variables could help. But remember that your own SQL statements are
considerably more complex, where the creation of an execution plan could likely be the biggest resource
hog in the entire sequence of SQL processing. Also, remember that not only will your application likely
repeat the same SQL statement over and over again but that this effect is multiplied by the use of your
application by many different users.
Those of you who think of yourself as primarily as programmers and don’t give no never mind about
databases may be rising up in protest at this point. There is some common wisdom that says the code
required to use bind variables, a PreparedStatement call in Java, executes slower than the code to execute
a hard-coded statement, a Statement call in Java. We certainly can’t argue this simple fact (although oth-
ers have), but remember that there is more to performance than simply the time required to execute a call.
The use of bind variables can have an enormous impact on the time Oracle needs to return data to the
user. Since performance is an aggregate, it doesn’t make sense to save time in the execution of a single
call if those savings are going to be outweighed by slower performance from the database.

The bottom line is that you should train yourself to use bind variables whenever you have SQL statements
that only differ in the value of a particular item in the statement and that are repeatedly executed in your
application.

Retrieval Performance

The final, and in many ways the most important, event in the SQL processing cycle is the return of data to
the application that requested it. On one level, the speed of this final step is determined by your network,
since the amount of bits that can move from the server to the client is ultimately limited by the amount of
network bandwidth. The effects of this potential limitation cannot be overcome. But you can affect how
much work the Oracle database performs before it starts returning data to the user.

Well, how much work does Oracle have to do before it returns data? You would think the answer to this
question would be self-evident — “As much as it needs to do to get the data.” But how much data does
Oracle need to get before it starts to send data back to the application?

As developers, we tend to think of the end results of an action. Auser requests data, so he or she obviously
must want all of that data to do what he or she has to do. But users tend to be more, shall we say, immediate
in their outlook. For us, performance is the time it takes to complete an SQL operation, such as a query. For a user, performance is how long they wait before something comes back to them.
You can take advantage of this dichotomy by setting the way that Oracle returns data. You can specify
that Oracle should start returning rows to the user as soon as it gets the rows, or you can specify that
Oracle will only start returning rows to the user once it has collected all the rows for the query.
You instruct your Oracle database as to which approach to take by setting a parameter called OPTIMIZER_MODE.
The two settings for OPTIMIZER_MODE that are relevant to this example are ALL_ROWS and FIRST_ROWS, which tell Oracle to only return data once all rows have been fetched or as soon as it can, respectively. You can also use either one of these values as a hint for a particular query.
The best choice for this parameter obviously depends on your application. If a user is unable to do any
work until he or she receives all the data, or if you don’t want the user to do any work until he or she
receives all the data, the ALL_ROWS parameter is the right choice. In applications that typically fetch data
for the user to peruse and possible use, FIRST_ROWS may deliver better perceived performance without
much logical downside. If your application is not retrieving large amounts of data, this particular optimizer
choice shouldn’t really affect performance.

Regardless of the setting of this parameter, there are some times when Oracle will properly wait until it
has retrieved all rows until it returns any rows. One case is when a query includes aggregate values.
Oracle knows that it has to get all the rows before it can calculate aggregate values, so no rows will be
returned until all rows have been retrieved and the calculations performed.

Another case is when you ask for the rows to be returned in sorted order. Normally, Oracle cannot
return the first rows until the sort has been performed, since the sort determines what the first row is,
not the order that the rows are retrieved from the database. The exception to this rule is when the query
has requested a sort order that is already implemented in an index. For instance, a user may request
employee names in alphabetical order based on the last name, and there is an index that sorts the rows
on that criterion. The Oracle database knows that the index has already sorted the rows, so it does not
have to sort them and the first rows can be returned as soon as they are retrieved.

Receiving Data

Aquery is a request for data. In response to a query, the Oracle database prepares a result set that satisfies
the conditions of the query. An application retrieves the rows of the result set by performing a series of
fetch operations to return rows.

When you are using Java to access Oracle data, the Java API implements a pre-fetch. A pre-fetch returns
a designated number of rows with a fetch command, when needed. By default, the pre-fetch returns 10
rows, but you can change the default for an individual connection programmatically.

When a fetch is first executed for a result set, the return includes 10 rows from the result set. For the next
nine fetch operations, the data is already residing at the client. In this scenario, the 11th fetch result would
retrieve another 10 rows.

Submitting SQL Statements

Submitting a statement is a single line of application code, but the Oracle database performs a series of
actions in response to this submission before it responds to the request. The individual actions performed
depend on the type of SQL statement submitted—a data definition language (DDL) statement for creating
or modifying database objects, a write operation (INSERT, UPDATE, or DELETE), or a query using the
SELECT verb. The actions taken for each of these are detailed in the following table.

Establishing a Cursor

Once a connection is established, the next step is to open a cursor for your SQL statements. Acursor is a
connection to a specific area in the Program Global Area (PGA) that contains information about a specific
SQL statement.

The cursor acts as the intermediary between the SQL statements your application submits and the actions
of the Oracle database as a result of those statements. The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement. For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set. This
important piece of information is also called a cursor, in that the cursor for a particular result set is the
pointer to the current row. But the cursor exists before any rows have been returned for a query, which is
why advancing the cursor to the next row at the start of retrieving data places the cursor on the first row
in the result set.
You don’t necessarily have to explicitly open a cursor to execute an SQL statement, because the Oracle
database can automatically take care of this for you. As with most aspects of application programming,
explicit control over cursor creation and use will give you more options for implementing your logic effi-
ciently. You may want to open more than one cursor to improve the operation of your application. For
instance, if you have an SQL statement that is repeatedly used, you might want to open a cursor for the
statement so that Oracle will not have to re-parse the statement. Of course, the Oracle database uses its
own internal caches to hold parsed versions of SQL statements also, so creation of individual cursors
may not be necessary for optimal performance in many cases.

Connecting to a Database

The first step in accessing an Oracle database from your application is to establish a connection to the
database. This connection is the path that acts as the path from your client application to a shadow process
in the Oracle database that handles the SQL requests from your application.

Normally, a connection goes from a client machine over a network to a server machine. The connection is
implemented on top of a network protocol, such as TCP/IP. The connection request is actually received by
the Oracle Listener, which listens on a port for connection traffic. Once the connection is established, the
Listener passes requests to the shadow process associated with the connection.


If you are using Java as your program language, the connection will be executed with a driver, which is
software designed to handle the complexities of communication over the network.

A connection to an Oracle database is always created on behalf of a user. Consequently, establishing a
connection requires identifying and authenticating that user with a username and password.

A connection to an Oracle database is referred to as a session. The session is the overriding context for all
SQL interactions that take place. When a connection is either terminated or is lost for any other reason,
the context for that session, including any information in any uncommitted transactions within that session,
is lost.

Every session is supported by a shadow process on the Oracle server. Normally, this means that every
session has its own process. But, as you can imagine, each shadow process uses some server memory
resources, so the scalability of an individual Oracle instance might be limited by the number of sessions
it can support. To address this issue, Oracle has a feature known as shared servers, which was referred to
as multithreaded servers, or MTS, before Oracle9i.
When a connectioncomes into the Listener, it passes the request to a dispatcher. The dispatcher assigns the request to a session that can be shared. Once the request is completed, the session becomes available to service other requests.

Use of shared servers is completely transparent to your application. A single Oracle instance can use a
combination of shared servers and dedicated servers—the instance has different connection identifiers
for a dedicated session or a shared session.

When should you use a shared server? As with most issues involving performance, the answer depends
on the particulars of your particular implementation.

If you are running out of memory because of a large number of connections, shared servers can help to
address the problem by lowering the overall amount of memory required for sessions. Obviously, some
overhead is also involved with the use of a dispatcher and the functions it performs. This overhead can
be balanced against the more limited use of resources required by the shared server architecture.
Typically, a shared server can do the most good for applications that require periodic access to the database,
since the connections used by these applications will not be performing work much of the time, and as such
they are candidates to be shared with other sessions. Abrowsing application would fit this description, while
a heads-down transaction processing application would not. The good news is that you can switch between
using shared sessions and dedicated sessions by simply changing the connection parameters, so it is fairly
easy to test the effect of shared sessions on your overall application performance.
You have to establish a connection to your Oracle database instance before you can send any SQL to the
instance, but you do not necessarily have to create a connection each time you want to use one. You can
reuse connections inside of an application, or a portion of an application, or you can use connection pooling
to maintain a set of connections that a user can grab when he or she needs it. Using a connection manager
with Java

The Processing Cycle for SQL

To process an SQL statement you submit to your Oracle database, the database goes through several
phases as part of the execution of the statement:
❑ Connect to the database, to establish communication between your client application and
the Oracle database server.
❑ Create a cursor, to hold the context for an SQL statement.
❑ Submit an SQL statement to the Oracle database for processing, which includes checking
the validity of the statement and potentially creating an optimized execution plan for it.
❑ Receive data back from the Oracle database in response to the submission of a query.

XML Data Type

Store XML in CLOB XMLType

With the CLOB XMLType you can store XML documents as a Character Large OBject.
Using the CLOB XMLType storage there is no need for validation of the XML.
The XML does have to be well-formed, and it's optional to validate the XML document against an XML schema or DTD yourself.

With this type Oracle implicitly stores the XML in a CLOB column. The original XML is preserved including whitespace All restrictions on a CLOB column apply also to this XMLType.

Use CLOB storage for XMLType when:
- you are interested in storing and retrieving the whole document.
- you do not need to perform piece-wise updates on XML documents.

Code examples:

create table XmlTest( id number
                    , data_xml XMLType)
    XmlType data_xml STORE AS CLOB;
                    
                
insert into XmlTest(id, data_xml) values
( 1
, XMLType('<company>
             <department>
               <id>10</id>
               <name>Accounting</name>
             </department>
           </company>'));

Date Data Types

The third major category of data types is the DATE data type. As with numeric data types, Oracle stores all
dates in a single internal representation. However, the format used for entering and returning dates is
determined by the NLS_DATE_FORMAT value for the instance. You can set this value in the initialization file
for the instance, or you can change it with an ALTER SESSION command for an individual database session.
The default format used by into Oracle for dates is DD-MMM-YY HH:MI:SS, where DD is a two-digit
representation of the day number, MMM is a three-character representation of the month name, and YY
is a two-digit representation of the year. HH, MI, and SS are two-digit representations of hours, minutes,
and seconds, respectively. If you do not specify a time portion of a date, these values default to 0.
Oracle does store dates with four-digit centuries, although you have to use a date format other than the
default format to enter and display four-digit centuries.
The three date data types are as follows:
❑ DATE—Includes the day, month, year, hour, minute and second specification.
❑ TIMESTAMP(n)—Can extend the standard DATE data type with fractional seconds. The number
of decimal places for the fractional seconds component is indicated with the n variable.
❑ TIMESTAMP(n) WITH [LOCAL] TIMEZONE—Includes the time zone of either the database server
or the client (when using the LOCAL keyword).
You can perform simple arithmetic on date values, where integers represent whole days and fractions
represent portions of days. If COLTIME contains a value of December 31, 2003, at 6 P.M., COLTIME + 1
would equal January 1, 2004, at 6 P.M., and COLTIME + .5 would equal January 1, 2004, at 6 A.M.
Oracle9i introduced two new date data types used for date arithmetic.
❑ INTERVAL YEAR TO MONTH — Can store an interval of years and months
❑ INTERVAL DAY TO SECOND — Can store an interval of days, hours and seconds
You can use the TO_DATE function to covert character values into dates. Oracle includes a set of extended date
functions that allow you to manipulate dates, such as creating a date value from character representations of
days, months, and years.

Numeric Data Types

the Oracle database stores all numbers in the same internal format. As a
developer, you may define numeric values in your applications with all sorts of numeric characteristics,
such as DECIMAL, INT, or SMALLINT, but the storage of those values is limited by the data types of the
columns in the database.

NUMBERThe NUMBER data type is the basic numeric data type used in the Oracle database. Data with this data
type is stored with up to 38 digits of precision. You can specify the precision and the scale of a NUMBER
column with the syntax:
NUMBER(p,s)
where p is the precision of the column and s is the scale of the column. p can be any number up to 38.
The s variable can be either positive, which indicates the number of digits to the right of the decimal
point that will be stored, or negative, which will round the number up by the number of places to the
left of the decimal point. If you do not define a scale, the scale is assumed to be 0. The following table
shows the effect of precision and scale on a value.

Character Data Types

The Oracle database supports a number of different data types for storing character data. All of these data
types interpret data entered and retrieved as character values, but each of the data types has additional
characteristics as well.

CHARCHAR columns can have a length specification after the data type or are assigned the default length of 1.
A CHAR column will always use the assigned space in the database. If a column is defined as CHAR(25),
every instance of that column will occupy 25 bytes in the database. If the value entered for that column
does not contain 25 bytes, it will be padded with spaces for storage and retrieval.

VARCHAR2 and VARCHARVARCHAR2 columns also can have a length specification, but this data type only stores the number of
characters entered by the user. If you define a column as VARCHAR2(25) and the user only enters 3 char-
acters, the database will only store those three characters. Because of this distinction, there may be some
issues with comparison between a CHAR column and a VARCHAR2 column.
The following SQL code snippet adds the same value, ‘ABC’, to two columns: ABCCHAR, which is described
as a CHAR column with 20 characters, and ABCVARCHAR, which is described as a VARCHAR2 column with 20
characters.

INSERT INTO TEST_TABLE COLUMNS(ABCCHAR, ABCVARCHAR) (‘ABC’, ‘ABC’);
IF ABCCHAR = ABCVARCHAR

This final test will fail, since it is comparing ABCCHAR, which has a value of “ABC                  “ with ABCVARCHAR,
which has a value of “ABC”. These comparison problems could be resolved by truncating the ABCCHAR
column, but it is much easier to just avoid them by using VARCHAR2 for normal character columns. The
VARCHAR2 data type will also save storage space, which improves overall performance.
The VARCHAR data type is synonymous with the VARCHAR2 data type, but Oracle recommends using
VARCHAR2 to protect against possible future changes in the behavior of the VARCHAR data type.

NCHAR and NVARCHAR2The Oracle database can store character data from many different languages, including Chinese or Japanese,
which require 2 bytes of storage for each character. These double-byte languages use the NCHAR and
NVARCHAR2 data types, which allow for the extended storage needed for the characters in these languages.
Prior to Oracle9i, using these data types would automatically indicate that each character required 2
bytes of storage. With Oracle9i, you can specify the storage for a column either in bytes or as a number
of characters. If you define a column with characters, Oracle automatically allocates the appropriate
number of bytes for storage of the data.

LONGThe CHAR and NCHAR data types have a limit of 2,000 characters, while the VARCHAR, VARCHAR2, and
NVARCHAR data types have a limit of 4,000 characters. The LONG data type allows you to enter up to 2 GBs
of data in a single column. The use of the LONG data type has been superceded by the CLOB and NCLOB
data types described next, since they have fewer restrictions on their use than the older LONG data type.

BLOB, CLOB, and NCLOBLike the LONG data type, these three data types, collectively referred to a LOB data types, are designed to
hold more that 4,000 characters. The BLOB data type holds binary data, the CLOB data type holds charac-
ter data, and the NCLOB data type holds double-byte information, just as the NVARCHAR2 data type does.
With Oracle10g, a single CLOB or NCLOB data type can hold up to 128 TBs (!) of data; prior to that release,
the same data types could hold up to 4 GBs of data.
The data for a column of this data type is not stored in an actual row; instead, the column holds a pointer
to the location of the data. LOB columns can participate in transactions, but if you want to manipulate the
data in one of these columns, you have to use specific calls in the PL/SQL built-in package DBMS_LOB. The
use of this package is beyond the scope of this book, since many readers may not be using these long data
types in their applications, but it is covered in the Oracle documentation set.

BFILEThe BFILE data type allows you to have a column that points to a storage location outside of the Oracle
database’s internal storage. You can also attain some of this functionality by using external tables, which
were introduced in Oracle9i. Both BFILEs and external tables can only be used for reading data, and
BFILEs cannot be used in transactions.

Data Organization

Tablespaces

The tablespace is another logical organizational unit used by the Oracle database, which acts as the
intermediary between logical tables and physical storage. Every table or index, when created, is placed
into a tablespace. Atablespace can contain more than one table or index, or the table or index can be
partitioned across multiple tablespaces, as described later in the section on partitions. A tablespace maps
to one or more files, which are the physical entities used to store the data in the tablespace. A file can
only belong to one tablespace. A tablespace can contain row data or index data, and a table and an index
for a table can be in different tablespaces.
Atablespace has a number of attributes, such as the block size used by the tablespace. This block size is the
smallest amount of data retrieved by a single I/O (input/output) operation by the Oracle database. Your
database administrator (hopefully) selected an appropriate block size for a tablespace, which depends on a
wide range of factors, including the characteristics of the data and the block size of the underlying operating
system.

Prior to Oracle9i, you could only have a single block size for an entire database. With Oracle9i, multiple
block sizes are supported within a single database, although each block size must have its own data
buffers in the SGA.

A tablespace is the basic administrative unit in an Oracle database. You can take a tablespace online or
offline (that is, make it available or unavailable through the Oracle database instance) or back up and
recover a tablespace. You can make a tablespace read-only to prevent write activity to the tablespace.
You can also use a feature called transportable tablespaces as a fast way to move data from one database
to another. The normal way to take data out of one Oracle database and move it to another Oracle
database is to export the data and then import the data. These processes operate by reading or writing
each row. A transportable tablespace allows you to move the entire tablespace by simply exporting
descriptive information about the tablespace from the data dictionary. Once you have exported this
information, you can move the files that contain the tablespace and import the descriptive information
into the data dictionary of the destination database (this is described in detail in Chapter 6, “The Oracle Data
Dictionary.” This method of moving data is significantly faster than importing and exporting data.

The 10g release of the Oracle database includes the ability to use transportable tablespaces between
Oracle databases on different operating system platforms.



Segments and Extents

Individual objects in the database are stored in segments, which are collections of extents. Data blocks are
stored in an extent. An extent is a contiguous piece of disk storage. When Oracle writes data to its data
files, it writes each row to a data block that has space available for the data. When you update information,
Oracle tries to write the new data to the same data block. If there is not enough space in that block, the
updated data will be written to a different block, which may be in a different extent.
If there are no extents that have enough space for new information, the Oracle database will allocate a
new extent. You can configure the number and size of extents initially and subsequently allocated for
a tablespace.

Partitions

As mentioned previously, you can use partitions to spread data across more than one tablespace. Apartition
is a way to physically segregate the data in a table or index based on a value in the table or index. There are
four ways you can partition data in an Oracle database:
❑ Range partitioning, which divides data based on a range of values, such as putting customers
with a last name starting with the letters A–G in one partition, the customers with last names
starting with the letters H–M in another partition, and so on.
❑ Hash partitioning, which divides data based on the result of a hashing algorithm. Hash parti-
tioning is used when range partitioning would result in unbalanced partitions. For instance, if
you are partitioning a table based on a serial transaction identifier, the first partition will com-
pletely fill before the second partition is used. A hash algorithm can spread data around more
evenly, which will provide greater benefits from partitioning.
❑ Composite partitioning, which uses a hash algorithm within each specific range partion.
❑ List partitioning, which partitions based on a list of values. This type of partitioning is useful when a
logical group, such as a list of states in a region, cannot be easily specified with a range description.

Partitions are defined when you create a table or an index. Since each partition of a table can be placed in
a different tablespace, you can perform maintenance on a single partition, such as backup and recovery
or moving tablespaces.



Partitions can also contribute to the overall performance of your Oracle database. The Oracle query optimizer is aware of the use of partitions, so when it creates a plan to execute a query, the plan will skip partitions that have been eliminated from the query with selection criteria. In addition, partitions can be in different tablespaces, which can be in different parts of your disk storage, which can reduce disk head contention during retrievals.

Real Application Clusters

As you know, a single server can have one or more CPUs within it. You can also use a technique called
clustering to combine more than one server into a larger computing entity. The Oracle database has sup-
ported the ability to cluster more than one instance into an single logical database for over 15 years.
Oracle combines multiple instances into a cluster. Since Oracle9i, Oracle has called these clusters Real Application Clusters, commonly referred to as RAC.
As Figure 1-2 shows, RAC instances share a common source of data, which allows RAC to provide
greater database horsepower for scalability while also providing higher levels of availability.

One of the best features of RAC is that you, as an application developer, don’t even have to think about
it, since a RAC implementation uses the same API and SQL that a single instance uses. You also don’t
have to worry about whether your Oracle application will be deployed to a single instance or a cluster
when you are designing and creating your application, since there are virtually no specific performance
considerations that only affect a RAC installation. As a developer, you don’t really have to know anything
more about Real Application Clusters.

Downloading and Including jQuery

Click the download link and save the JavaScript file to a new working folder, ready for playing with. You’ll need to put it where our HTML files can see it: commonly in a scripts or javascript directory beneath your site’s document root To make it all work, we need to tell our HTML file to include the jQuery library.
<head>
 <title>Hello jQuery world!</title>
 <script type='text/javascript' src='jquery-1.4-min.js'></script>
 <script type='text/javascript' src='script.js'></script>
</head>
The first script tag on the page loads the jQuery library, and the second script tag points to a script.js file, which is where we’ll run our own jQuery code.

JQuery

Coming soon

Memory Used by an Instance

System Global Area
The System Global Area (SGA) is an area of memory that is accessible to all user processes of an Oracle
instance. Three main areas are used by the SGA:
❑ The redo log buffer holds information used for recovery until the information can be written to
the redo log.
❑ The shared pool holds information that can be shared across user processes, such as execution
plans for SQL statements, compiled stored procedures, and information retrieved from the
Oracle data dictionary.
❑ The database buffer pools are a group of memory areas that are used to hold data blocks. A data
block held in a buffer pool can be accessed much more rapidly than going to disk to retrieve
the block, so efficient use of these memory pools is essential for achieving optimal performance.
Following are the three basic database buffer pools:
❑ The DEFAULT pool holds all database objects, unless otherwise specified.
❑ The KEEP pool is used to hold objects in memory if specified for a table or index.
❑ The RECYCLE pool is used for objects that are not likely to be reused again.
Keep the following two points in mind:
❑ For all of these pools, the Oracle instance uses a Least Recently Used (LRU)
algorithm to determine what data blocks to swap out.
❑ In addition to these main pools, the Oracle instance can also use a large pool,
which is used for shared server, backup and recovery, and I/O operations. You
can either configure a large pool or Oracle can create it automatically, if you
configure adaptive parallelism, which is described further in Chapter 2.

Program Global Area
The Program Global Area (PGA) is an area of memory that is just available to a single server process.
The PGA contains items like user variables and cursor information for an individual user’s SQL state-
ment, such as the number of rows that have been retrieved so far. The PGA is also used for sorting data
for an individual user process.
Each time a new SQL statement is received for a user, a new space in the PGA has to be initialized. To
avoid this overhead, transaction-intensive applications, such an online transaction processing (OLTP)
applications, typically use a small set of SQL statements that are continually reused by a user process,
reducing this potential source of overhead.
You can have a single server process handle more than one user process by configuring shared servers,
which are described in Chapter 2, “Using SQL.”

Files Supporting an Instance

There are a number of files that are used by an instance. The basic files for storing data are discussed in the
next section on data organization. An Oracle instance has its own set of files that do not store user data
but are used to monitor and manage the Oracle instance itself.

Initialization Files
Many parameters exist that affect the way that your Oracle instance operates. These parameters are
typically set and maintained by a database administrator and, as such, are beyond the scope of this book.
The initial values for these parameters are kept in initialization files.

Prior to Oracle9i, each instance had its own specific initialization file called INIT.ORA, as well as an
optional file with additional configuration information called CONFIG.ORA. Starting with Oracle9i, you
could have a virtual initialization file called the SPFILE, which could be shared with multiple instances.
SPFILE was especially handy for two reasons: You could change a configuration parameter for a running
instance and not have to save the change to the SPFILE, which means that the value of the parameter
would be reset to the stored value on the next startup. You can also use the SPFILE with Real Application
Clusters, discussed later in this chapter, where multiple instances worked together in a cluster and shared
the same initialization procedures.

Control File
The control file is used to store key information about an instance, such as the name of the instance, the
time the database was created, and the state of backup and log files for the database. An Oracle instance
requires a control file in order to operate. Although a control can be rebuilt, most Oracle installations use
multiple copies of the control file to avoid this possibility.

Redo Log Files
One of key features of a relational database is its ability to recover to a logically consistent state, even in
the event of a failure. Every relational database, including Oracle, uses a set of redo log files. These files
keep track of every interaction with the database. In the event of a database failure, an administrator can
recover the database by restoring the last backup and then applying the redo log files to replay user
interactions with the database.

Redo log files eventually fill up and roll over to start a new volume. You can set up Oracle to avoid writing
over existing logfiles by creating the database to automatically archive log files in ARCHIVELOG mode,
which is discussed in detail in the Oracle documentation.
Since redo logs are crucial for restoring a database in the event of a failure, many Oracle shops set up an
instance to keep multiple copies of a redo log file.

Rollback Segments
Unlike all other major databases, Oracle also uses rollback segments to store previous versions of data in
the database. The use of rollback segments makes it possible for the Oracle database to avoid the use of
read locks, which can significantly reduce performance degradation based on multiuser access as well as
providing a consistent view of data at any particular point in time. For more on rollback segments and
how they support multiuser concurrency, see Chapter 3, “Handling Multiple Users,” which covers this
important topic in detail.

Because rollback segments track every change to data, the rollback segments are updated as soon as a
change is made. This real-time update lets the Oracle database delay writing its redo log files until a
time when these writes can be done efficiently.

Oracle 10g includes the ability to designate an automatic UNDO tablespace, which assigns the responsibility
for managing rollback segments to the Oracle database itself.

What’s so good about jQuery?

You’ve read that jQuery makes it easy to play with the DOM, add effects, and execute
Ajax requests.

Cross browsing
One of the biggest benefits of jQuery is that it handles a lot of infuriating cross-browser issues for you.
Support CSS3
jQuery also fully supports the upcoming CSS3 selector specification.
Utilities
Also included is an assortment of utility functions that implement common functions useful for writing jQuery (or are missing from JavaScript!): string trimming, the ability to easily extend objects, and more.
Support() function
One noteworthy utility is the supports function, which tests to find certain features are available on the current user’s browser
Widgets and Effects
JQuery has already been used to make some impressive widgets and effects.
Plugin
The jQuery team has taken great care in making the jQuery library extensible. By including only a core set of features while providing a framework for extending the library, they’ve made it easy to create plugins that you can reuse in all your jQuery projects, as well as share with other developers.
Clean markup
 jQuery makes it a cinch to completely rid your markup of inline scripting, thanks to its ability to easily hook elements on the page and attach code to them in a natural

Css

Coming soon

Html

Coming soon

JEE

Coming soon

JSE

Coming soon

Strut

Coming soon

JSF

Coming soon

Spring

Coming soon

Hibernate

Coming soon

The architecture of Linux

Linux consists of a central set of programs that run the PC on a low level, referred to as the kernel, and hundreds (if not thousands) of additional programs provided by other people and various companies. Technically speaking, the word Linux refers explicitly just to the core kernel program. However, most peoplegenerally refer to the entire bundle of programs that make up the operating system as Linux.

Although most of us refer to Linux as a complete operating system, the title “Linux” hides a lot of confusing
but rather important details. Technically speaking, the word Linux refers merely to the kernel file: the central
set of programs that lie at the heart of the operating system. Everything else that comes with a typical ver-
sion of Linux, such as programs to display graphics on the screen or let the user input data, is supplied by
other people, organizations, or companies. The Linux operating system is the combination of many disparate
projects.

The GNU organization, in particular, supplies a lot of vital programs and also system library files, without
which Linux wouldn’t run. These programs and files were vital to the acceptance of Linux as an operating
system in its early days. Because of this, and the fact that Linux completed a long-running goal of the GNU
project to create a Unix-like operating system, some people choose to refer to Linux as GNU/Linux.

Characteristics of Servlets

Java servlets are fundamental Java EE platform components that provide a request/response interface for both Web requests and other requests such as XML messages or file transfer functions.

Servlets Use the Request/ResponseModel

Java servlets are a request/response mechanism: a programming construct designed to respond to
a particular request with a dynamic response generated by the servlet’s specific Java implementation. Servlets may be used for many types of request/response scenarios, but they are most often employed in the creation of HyperText Transfer Protocol (HTTP) responses in a web application. In this role, servlets replace other HTTP request/response mechanisms such as Common Gateway Interface (CGI) scripts.

The simple request/response model becomes a little more complex once you add chaining and filtering capabilities to the servlet specification. Servlets may now participate in the overall request/response scenario in additional ways, either by preprocessing the request and passing it on to another servlet to create the response or by postprocessing the response before returning it to the client. Later in this chapter, we discuss servlet filtering as a mechanism for adding auditing, logging, and debugging logic to your web application.

Processes Supporting an Instance

A number of processes are associated with an instance and perform specific tasks for the instance.

Listener
The Listener is a process that listens on the network for requests coming in to an Oracle instance. AListener
process can support one or more Oracle instances. The Listener acts as the intermediary between user
requests from remote machines and the instance. The failure of the Listener will mean that the instance it
supports is not accessible by a remote client—such as any application that accesses the instance.

Background Processes
Awhole set of processes run in the background on an Oracle server, supporting the actions of the Oracle
instance. The main processes are listed here, with the standard acronyms of the processes in parentheses:
❑ Database Writer (DBWR). Writes data blocks from the database buffers, as described in the fol-
lowing section.
❑ Log Writer (LGWR). Writes redo log information from the redo log buffer in memory to the
redo log on disk.
❑ System Monitor (SMON). Monitors the health of all components of an Oracle instance, and
helps to recovery the database when it is restarted after a crash.
❑ Process Monitor (PMON). Watches individual user processes that access the Oracle instance
and cleans up any resources left behind by an abnormal termination of a user process.
❑ Archiver (ARC). Writes a copy of a filled redo log file to an archive location. An Oracle instance
can have up to 10 Archiver processes.

Z

A/ When is zygote formed ?
B/ When male and female gamete join together.

Y

A/ what skill do  you have ?
B/ I have only youthful enthusiasm.

X

A/ What did the doctor ask you to do?
B/ Take an chest X-ray.

W

A/ How did she look like when you asked her about that?
B/ She smiled wryly.

V

A/ Who will be affected most in case of food poisoning?
B/ Young children are especially vulnerable for that.

U

A/ Do we have any plan for these machines?
B/ Yes, I think we should utilize them instead of throwing them away.

S

A/ How do you thin about the accidence?
B/ I think this is a systematic destroy.

R

A/ How does the car look?
B/ It looks old and rusty.

P

A/ Where can we see pyramid?
B/ In Egypt

Q

A/Where did you find this ideas?
B/ I quoted it from minister 's speech.

O

A/ What is most important to us?
B/ We inhale oxygen every day.

N

A/ How do the children show their affection to their mother?
B/ They nuzzle up against their mother.

M

A/ Do you know why she got married with a foreigner?
B/ That is really a mystery to me.

L

A/ Did you find out the lyric of the song?
B/ Not yet

K

A/ What field are you good at?
B/ I have good knowledge at painting and music.

J

A/ Oh sorry, I am not intentionally.
B/ Yeah, no need to justify yourself to me

A/Are you confident with your decision?
B/ Let 's see. My result will justify my decision.

I

A/ Excuse me, Can I buy each item separately?
B/ Sorry, you must buy in pair.

H

A/ What did she do to make you remember the accidence?
B/ She used a necklace to hypnotize me.

G

A/ Good morning guys
B/ Good morning teacher

A/ How is the end of the film?
B/ The bad guy gets shot.

F

A/ Did you care much about punctuation?
B/ No, I am not fussy about that.

A/ What do you usually think about yourself?
B/ I always think about way to get rich, and not fussy about appearance.

E

A/ Di you take an eyesight test before buying the glasses?
B/ Oh sure.

The Instance

The Oracle instance is the collection of processes that handle requests from a client for a data.

An Oracle instance is either started as part of the process of booting a server or can be started explicitly
with commands. Although you start an instance with a single command, there are actually three distinct
steps in the startup process:
❑ Starting the instance process itself
❑ Mounting the database, which consists of opening the control files for the instance
❑ Opening the database, which makes the database available for user requests

An instance can be stopped with another command or via the system console, which follows the same
sequence of events in reverse. You can stop an instance gracefully by stopping users from logging on to
the database and only shutting down when the last active user has logged off, or you can simply stop the instance, which may result in incomplete transactions.

Architecture

The Oracle database consists of two main parts: the instance, which is the software service that acts as an
intermediary between application requests and its data, and the actual data files, which are where the data is
kept. The instance is a dynamic process and uses a variety of tasks and memory to support its operations. The data files are stored on disk, so the data itself will survive most service interruptions, except for catastrophic media failure.

D

A/ Are you lazy at work?
B/ Absolutely not, I am very dynamic.

A/ Do you think that the group dynamics will change according to the company 's policy?
B/ Sure

C

A/ What is the most famous kind of sport in your hometown?
B/ Probably cycling. Lots of teenagers ride bicycle in my hometown.