Identifying PL/SQL Objects

731
,’No grant to PUBLIC allowed for ‘
||DICTIONARY_OBJ_OWNER||’.’
||DICTIONARY_OBJ_NAME);
END IF;
END LOOP;
END;
In the preceding example, the DDL event is a
GRANT
statement issued by user engineering.
The code examines the grantee list, and if it finds the special user/role
PUBLIC
, an exception
is raised, causing the grant to fail. Table 13.2 shows the DDL trigger events.
TABLE 13.2 DDL Trigger Events
Event When It Fires
[BEFORE/AFTER] ALTER
When an
ALTER
statement changes a database object
[BEFORE/AFTER] ANALYZE
When the database gathers or deletes statistics or validates
the structure of an object
[BEFORE/AFTER] ASSOCIATE
STATISTICS
When the database associates a statistic with a database
object with an
ASSOCIATE STATISTICS
statement

[BEFORE/AFTER] AUDIT
When the database records an audit action (except FGA)
[BEFORE/AFTER] COMMENT
When a comment on a table or column is modified
[BEFORE/AFTER] CREATE
When the database object is created
[BEFORE/AFTER] DDL
In conjunction with any of the following:
ALTER
,
ANALYZE
,
ASSOCIATE STATISTICS
,
AUDIT
,
COMMENT
,
CREATE
,
DISASSOCIATE

STATISTICS
,
DROP

GRANT
,
NOAUDIT
,

RENAME
,
REVOKE
, or
TRUNCATE
[BEFORE/AFTER]
DISASSOCIATE STATISTICS
When a database disassociates a statistic type from a database
object with a
DISASSOCIATE STATISTICS
statement
[BEFORE/AFTER] DROP
When a
DROP
statement removes an object from the database
[BEFORE/AFTER] GRANT
When a
GRANT
statement assigns a privilege
[BEFORE/AFTER] NOAUDIT
When a
NOAUDIT
statement changes database auditing
[BEFORE/AFTER] RENAME
When a
RENAME
statement changes an object name
[BEFORE/AFTER] REVOKE
When a
REVOKE

statement rescinds a privilege
[BEFORE/AFTER] TRUNCATE
When a
TRUNCATE
statement purges a table
95127c13.indd 731 2/17/09 2:49:22 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
732

Chapter 13
N
Managing Data and Undo
Database Trigger Events
Database event triggers fire when the specified database-level event occurs. Most of these
triggers are available only before or after the database event, but not both.
The following example creates an after-server error trigger that sends an email notifica-
tion when an ORA-01555 error occurs:
CREATE OR REPLACE TRIGGER Email_on_1555_Err
AFTER SERVERERROR ON DATABASE
DECLARE
mail_conn UTL_SMTP.connection;
smtp_relay VARCHAR2(32) := ‘mailserver’;
recipient_address VARCHAR2(64) := ‘’;
sender_address VARCHAR2(64) := ‘’;
mail_port NUMBER := 25;
msg VARCHAR2(200);
BEGIN
IF USER = ‘SYSTEM’ THEN
-- Ignore this error
NULL;

ELSIF IS_SERVERERROR (1555) THEN
-- compose the message
msg := ‘Subject: ORA-1555 error’;
msg := msg||’Snapshot too old err at ‘||systimestamp;
-- send email notice
mail_conn := UTL_SMTP.open_connection(smtp_relay
,mail_port);
UTL_SMTP.HELO(mail_conn, smtp_relay);
UTL_SMTP.MAIL(mail_conn, sender_address);
UTL_SMTP.RCPT(mail_conn, recipient_address);
UTL_SMTP.DATA(mail_conn, msg);
UTL_SMTP.QUIT(mail_conn);
END IF;
END;
Be careful when using database triggers. Fully test them in development before deploying
them to production. Table 13.3 shows the database trigger events.
TABLE 13.3 Database Trigger Events
Event When It Fires
AFTER LOGON
When a database session is established—only the
AFTER
trigger is
allowed
BEFORE LOGOFF
When a database session ends normally—only the
BEFORE
trigger is
allowed
95127c13.indd 732 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Identifying PL/SQL Objects

733
Event When It Fires
AFTER STARTUP
When the database is opened—only the
AFTER
trigger is allowed
BEFORE SHUTDOWN
When the database is closed—only the
BEFORE
trigger is allowed
AFTER SERVERERROR
When a database exception is raised—only the
AFTER
trigger is
allowed
AFTER SUSPEND
When a server error causes a transaction to be suspended—only the
AFTER
trigger is allowed
Enabling and Disabling Triggers
The database automatically enables a trigger when you create it. After creating a trigger,
you can disable (temporarily prevent it from firing) or reenable it. You can disable and
enable triggers by name with an
ALTER TRIGGER
statement. Here are two examples:
ALTER TRIGGER after_ora60 DISABLE;
ALTER TRIGGER load_packages ENABLE;
Alternatively, you can enable and disable multiple DML triggers with an

ALTER TABLE

statement, like this:
ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;
You can also create a trigger with the
ENABLE
or
DISABLE
clause.
ENABLE
is the default.
You can query the
STATUS
column of the
DBA_TRIGGERS
view to find out whether a trig-
ger is enabled or disabled.
Using and Administering PL/SQL Programs
Oracle 11g comes bundled with hundreds of built-in packages that give you significant
capabilities for administering your database. Many features in the database are imple-
mented through one or more of these built-in packages. To use the job scheduler, collect
and manage optimizer statistics, implement fine-grained auditing, send email from the
database, and use Data Pump or Log Miner, you must engage built-in packages. As you
gain experience, you will use these built-in packages more extensively.
These are some of the commonly used built-in catalog packages:
DBMS_STATS
ÛN
DBMS_METADATA
ÛN

DBMS_MONITOR
ÛN
TABLE 13.3 Database Trigger Events (continued)
95127c13.indd 733 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
734

Chapter 13
N
Managing Data and Undo
UTL_FILE
ÛN
UTL_MAIL
ÛN
To view the names and parameter lists for stored programs (except triggers), use the
S QL*Plu s
DESCRIBE
command like this:
describe dbms_monitor
-- some output is deleted for brevity
PROCEDURE SESSION_TRACE_DISABLE
Argument Name Type In/Out Default?
--------------- ------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
--------------- ------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT

WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
PLAN_STAT VARCHAR2 IN DEFAULT
You can see in this output from
DESCRIBE
that the packaged procedure
DBMS_MONITOR
con-
tains several procedures, including
SESSION_TRACE_DISABLE
and
SESSION_TRACE_ENABLE
.
Furthermore, you can see the names, datatypes, and in/out mode for each parameter
(
SESSION_ID
,
SERIAL_NUM
, and so on).
An extensive list of Oracle built-in PL/SQL packages is available in the
manual “Oracle Database PL/SQL Packages and Types Reference 11g
Release 1 (11.1) Part Number B28419-03.” Fortunately, you don’t have to
know all these programs for the certification exam!
A PL/SQL program may be invalidated when a dependent object is changed through the
ALTER
command. The database automatically recompiles the package body the next time it
is called, but you can choose to compile invalid PL/SQL programs yourself and thus elimi-
nate the costly recompile during regular system processing. To explicitly compile a named
SQL program, use the
ALTER…COMPILE

statement, like this:
ALTER PROCEDURE archive_orders COMPILE;
ALTER FUNCTION is_weekend COMPILE;
ALTER PACKAGE table_util COMPILE;
95127c13.indd 734 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Locks and Resolving Lock Conflicts

735
ALTER PACKAGE table_util COMPILE BODY;
ALTER TRIGGER fire_me COMPILE;
Other objects, such as views or types, are similarly compiled.
Oracle 11g implements a finer-grained dependency control; hence, if the package speci-
fication is not changed, the PL/SQL objects that reference the functions and procedures of
the package are not invalidated when only the package body is changed.
Monitoring Locks and Resolving
Lock Conflicts
In any database with many users, you will eventually have to deal with locking conflicts
when two or more users try to change the same row in the database. In the following sec-
tions, I’ll present an overview of how locking works in the Oracle Database, how users are
queued for a particular resource once it is locked, and how Oracle classifies lock types in
the database. Then, I’ll show you a number of ways to detect and resolve locking issues; I’ll
also cover a special type of lock situation: the deadlock.
Understanding Locks and Transactions
Locks prevent multiple users from changing the same data at the same time. Before one or
more rows in a table can be changed, the user executing the DML statement must obtain a
lock on the row or rows; a lock gives the user exclusive control over the data until the user
has committed or rolled back the transaction that is changing the data.
In Oracle 11g, a transaction can lock one row, multiple rows, or an entire table.
Although you can manually lock rows, Oracle can automatically lock the rows needed at

the lowest possible level to ensure data integrity and minimize conflicts with other transac-
tions that may need to access other rows in the table.
In Table 13.4, both updates to the
EMPLOYEES
table return to the command prompt
immediately after the
UPDATE
because the locks are on different rows in the
EMPLOYEES
table
and neither session is waiting for the other lock to be released.
TABLE 13.4 Concurrent Transactions on Different Rows of the Same Table
Session 1 Time Session 2
update employees set salary =
salary * 1.2 where employee_id =
102;
11:29
update employees set manager = 100
where employee_id = 109;
commit;
11:30
commit;
95127c13.indd 735 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
736

Chapter 13
N
Managing Data and Undo
Packaged Applications and Locking

The HR department recently purchased a benefits-management package that interfaced
well with our existing employee-management tables; however, once HR started using the
application, other users who accessed the employee tables started complaining of severe
slowdowns in updates to the employee information.
Reviewing the CPU and I/O usage of the instance did not reveal any problems, and it wasn’t
until we looked at the locking information that we noticed a table lock on the employees table
whenever the benefits-management features were being used! The benefits-management
application was written to work on a number of database platforms, and the least capable
of those platforms did not support row locking. As a result, no one could make changes
to the employees table whenever an employee’s benefits were being changed, and every-
one had to wait for the benefits changes to complete. Fortunately, the parameter file for
the benefits-management package had an option to specify Oracle as the target platform;
after setting the specific database version in the package’s parameter file, the package
was smart enough to use row locking instead of table locking whenever the employee
table needed to be updated.
Queries never require a lock. Even if another transaction has locked several rows or an
entire table, a query always succeeds, using the prelock image of the data stored in the undo
tablespace.
If multiple users require a lock on a row or rows in a table, the first user to request the
lock obtains it, and the remaining users are enqueued using a first-in, first-out (FIFO)
method. At a
SQL>
command prompt, a DML statement (
INSERT
,
UPDATE
,
DELETE
, or
MERGE

)
that is waiting for a lock on a resource appears to hang, unless the
NOWAIT
keyword is used
in a
LOCK
statement.
The
WAIT
and
NOWAIT
keywords are explained in the next section, “Maxi-
mizing Data Concurrency.”
At the end of a transaction, when either a
COMMIT
or a
ROLLBACK
is issued (either explic-
itly by the user or implicitly when the session terminates normally or abnormally), all locks
are released.
Maximizing Data Concurrency
Rows of a table are locked either explicitly by the user at the beginning of a transaction or
implicitly by Oracle, usually at the row level, depending on the operation. If a table must
95127c13.indd 736 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Locks and Resolving Lock Conflicts

737
be locked for performance reasons (which is rare), you can use the
LOCK


TABLE
command,
specifying the level at which the table should be locked.
In the following example, you lock the
EMPLOYEES
and
DEPARTMENTS
tables at the highest
possible level,
EXCLUSIVE
:
SQL> lock table hr.employees, hr.departments
in exclusive mode;
Table(s) Locked.
Until the transaction with the
LOCK
statement either commits or rolls back, only queries
are allowed on the
EMPLOYEES
or
DEPARTMENTS
table.
In the sections that follow, I will review the lock modes, as well as show you how to avoid the
lock enqueue process and terminate the command if the requested resource is already locked.
Lock Modes
Lock modes provide a way for you to specify how much and what kinds of access other
users have on tables that you are using in DML commands. In Table 13.5, you can see the
types of locks that can be obtained at the table level.
TABLE 13.5 Table Lock Modes

Table Lock Mode Description
ROW

SHARE
Permits concurrent access to the locked table but prohibits other
users from locking the entire table for exclusive access.
ROW

EXCLUSIVE
Same as
ROW

SHARE
but also prohibits locking in
SHARE
mode. This
type of lock is obtained automatically with standard DML com-
mands such as
UPDATE
,
INSERT
, or
DELETE
.
SHARE
Permits concurrent queries but prohibits updates to the table; this
mode is required to create an index on a table and is automatically
obtained when using the
CREATE


INDEX
statement.
SHARE

ROW

EXCLUSIVE
Used to query a whole table and to allow other users to query the
table but to prevent other users from locking the table in
SHARE

mode or updating rows.
EXCLUSIVE
The most restrictive locking mode; permits queries on the locked
table but prohibits DML by any other users. This mode is required
to drop the table and is automatically obtained when using the
DROP

TABLE
statement.
Manual lock requests wait in the same queue as implicit locks and are satisfied in a FIFO
manner as each request releases the lock with an implicit or explicit
COMMIT
or
ROLLBACK
.
95127c13.indd 737 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
738


Chapter 13
N
Managing Data and Undo
You can explicitly obtain locks on individual rows by using the
SELECT

…

FOR

UPDATE

statement, as you can see in the following example:
SQL> select * from hr.employees
where manager_id = 100
for update;
Not only does this query show the rows that satisfy the query conditions, but it also
locks the selected rows and prevents other transactions from locking or updating these
rows until a
COMMIT
or
ROLLBACK
occurs.
NOWAIT Mode
Using
NOWAIT
in a
LOCK

TABLE

statement returns control to the user immediately if any locks
already exist on the requested resource, as you can see in the following example:
SQL> lock table hr.employees
in share row exclusive mode
nowait;
lock table hr.employees
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL>
This is especially useful in a PL/SQL application if an alternate execution path can
be followed if the requested resource is not yet available.
NOWAIT
can also be used in the
SELECT

…

FOR

UPDATE
statement.
WAIT Mode
You can tell Oracle 11g to wait a specified number of seconds to acquire a DML lock. If
you do not specify a
NOWAIT
or
WAIT
clause, then the database waits indefinitely if the table
is locked by another user. In the following example, Oracle will wait for 60 seconds to

acquire the lock. If the lock is not acquired within 60 seconds, an error is returned.
SQL> lock table hr.employees
in share row exclusive mode
wait 60;
DDL Lock Waits
When DML statements have rows locked in a table or if the table is manually locked by a
user, DDL statements on the table fail with the ORA-00054 error. To have the DDL state-
ments wait for a specified number of seconds before throwing the ORA-00054 error, you
95127c13.indd 738 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Locks and Resolving Lock Conflicts

739
can set the initialization parameter
DDL_LOCK_TIMEOUT
. The default value is 0, which means
the error is issued immediately. You can specify a value up to 1,000,000 seconds.
SQL> alter table hr.employees modify salary number (15,2);
alter table hr.employees modify salary number (15,2)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> show parameter ddl_lock
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
SQL>
Detecting and Resolving Lock Conflicts
Although locks are a common and sometimes unavoidable occurrence in many databases,
they are usually resolved by waiting in the queue. In some cases, you may need to resolve

the lock problem manually (such as if a user makes an update at 4:59 p.m. and does not
perform a
COMMIT
before leaving for the day).
In the next few sections, I will describe in more detail some of the reasons that lock
conflicts occur and how to detect lock conflicts, and I’ll discuss a more specific and serious
type of lock conflict: a deadlock.
Understanding Lock Conflicts
In addition to the proverbial user who makes a change at 4:59 p.m. and forgets to perform
a
COMMIT
before leaving for the day, other more typical lock conflicts are caused by long-
running transactions that perform hundreds, thousands, or even hundreds of thousands of
DML commands in the overnight batch run but are not finished updating the tables when
the normal business day starts. The uncommitted transactions from the overnight batch
jobs may lock tables that need to be updated by clerical staff during the business day, caus-
ing a lock conflict.
Another typical cause of lock conflicts is using unnecessarily high locking levels. In the
“Packaged Applications and Locking” sidebar earlier in this chapter, we described a third-
party application that routinely locked resources at the table level instead of at the row level
to be compatible with every SQL-based database on the market. Developers may unneces-
sarily code updates to tables with higher locking levels than required by Oracle 11g.
Detecting Lock Conflicts
Detecting locks in Oracle 11g using EM Database Control makes your job easy; you don’t
need to query against
V$SESSION
,
V$TRANSACTION
,
V$LOCK

, and
V$LOCKED_OBJECT
to see who
95127c13.indd 739 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
740

Chapter 13
N
Managing Data and Undo
is locking what resource. You can click the Instance Locks link on the Performance tab
of EM Grid Control. In Figure 13.1, you can see the tables locked by the user
SCOTT
after
executing the following statement:
SQL> lock table hr.employees, hr.departments
in exclusive mode;
Table(s) Locked.
FIGURE 13.1 The Instance Locks screen in EM Database Control
95127c13.indd 740 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Locks and Resolving Lock Conflicts

741
SCOTT
has an
EXCLUSIVE
lock on both the
EMPLOYEES
and

DEPARTMENTS
tables. You can
drill down on the locked object by clicking one of the links in the Object Name column;
similarly, you can review other information about
SCOTT
’s session by clicking one of the
links in the Session ID column.
If the
HR
user performs the following SQL,
HR
’s session will wait until the
SCOTT
user
releases the locks:
SQL> UPDATE employees SET salary = 0 WHERE salary IS NULL;
On the EM Grid Control screen, choose Blocking Locks from the drop-down view, and
you can see that user
SCOTT
is blocking the
HR
user, as shown in Figure 13.2.
FIGURE 13.2 The blocking locks shown in EM Database Control
The data dictionary view
DBA_LOCK
is very handy for the DBA to look for
locks and whether any session is blocking other users. A session with
value of
Blocking
in the

BLOCKING_OTHERS
column may have to be manu-
ally terminated using the
ALTER SYSTEM KILL SESSION
statement.
DBA_
WAITERS
is another view that shows only the sessions that are waiting on a
lock. This view shows the holding session and the waiting session.
95127c13.indd 741 2/17/09 2:49:23 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
742

Chapter 13
N
Managing Data and Undo
Understanding and Resolving Deadlocks
Resolving a lock conflict, the user can either
COMMIT
or
ROLLBACK
the current transaction.
If you cannot contact the user and it is an emergency, you can select the session holding
the lock and click the Kill Session button on the Instance Locks screen of the EM Database
Control (refer to Figure 13.1, earlier in this chapter). The next time the user whose session
has been killed tries to execute a command, the error message “ORA-00028: Your session
has been killed” is returned. Again, this is an option of last resort: you’ll lose all the state-
ments executed in the session since the last
COMMIT
.

A more serious type of lock conflict is a deadlock. A deadlock is a special type of lock
conflict in which two or more users are waiting for a resource locked by the other users. As
a result, neither transaction can complete without some kind of intervention: the session
that first detects a deadlock rolls back the statement waiting on the resource with the error
message “ORA-00060: Deadlock detected while waiting for resource.” Oracle automati-
cally resolves deadlocks without user/DBA intervention.
In Table 13.6, two sessions are attempting to update a row locked by the other session.
TABLE 13.6 Deadlock Scenario
Session 1 Time Session 2
update employees set salary =
salary * 1.2 where employee_id =
102;
11:29
update employees set manager = 100
where employee_id = 109;
update employees set salary =
salary * 1.2 where employee_id =
109;
11:44
11: 50
update employees set manager = 100
where employee_id = 102;
Prior to 11:44, session 1 and session 2 updated two different rows in the database and
did not commit the transaction. At 11:44, session 1 issued an
UPDATE
statement against
the same row locked by session 2. This causes session 1 to hang, waiting for the lock to
be released by session 2. The lock held by session 2 will be released only when session 2
performs a commit or rollback. At 11:50, when session 2 is trying to update a row already
locked by session 1, you have a deadlock situation: session 1 waiting on session 2 and ses-

sion 2 waiting on session 1. When this situation forms, Oracle throws out the ORA-00060
error and fails the statement. Remember, the transaction is not rolled back, because only
the statement is in error. In our example, session 2 will get the ORA-00060 error when the
update at 11:50 is issued, but session 1 will wait until session 2 commits or rolls back.
95127c13.indd 742 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Undo Management

743
Leveraging Undo Management
Whenever a process or a user session changes data in the database, Oracle saves the old
value as it existed before it was modified as undo data. This provides a number of benefits
to the database user:
It lets the user change their minds and roll back, or undo, the change to the database.
ÛN
It supports read-consistent queries. Once a query starts, any changes to the query’s
ÛN
underlying tables are not reflected in the query’s results.
It supports flashback query, an Oracle feature introduced in Oracle9
ÛN
i. Flashback query
allows a user to see how a table looked at some point in the past. As long as the undo
data still exists for the requested point of time, flashback queries are possible.
In the following sections, I present all aspects of undo management. First, I will show
how transactions are related to undo management and how undo records are stored in an
undo tablespace along with some of the features supported by undo records. Next, I will
show you how to set up the initialization parameters to specify a target for how much
undo is retained in the undo tablespace; in addition, I will show you the commands needed
to guarantee that undo space is available for
SELECT

statements at the expense of DML
commands.
Monitoring an undo tablespace is not unlike monitoring any other tablespace: you want
to make sure you have enough undo space in the tablespace to satisfy all types of user trans-
actions but not so much that you’re wasting space that can be used for objects in other
tablespaces. Therefore, I will present some methods to accurately calculate the optimal
amount of undo space you will need. Finally, I will review the notification methods you can
use to proactively alert you to problems with the undo tablespace.
Understanding Undo Segments
Undo segments, also known as rollback segments, are similar to other segments in the
database, such as table or index segments, in that an undo segment consists of extents,
which in turn consist of data blocks. Also, an undo segment contains data similar to that
stored in a table. However, that is where the similarity ends. Undo segments must be stored
in a special type of tablespace called an undo tablespace. Although a database can have
more than one undo tablespace, only one undo tablespace can be active at any one time.
Undo segments contain undo information about one or many tables involved in a transac-
tion. Also, undo segments automatically grow and shrink as needed, acting as a circular
buffer—transactions that fill up the extents in an undo segment can wrap around to the
beginning of the segment if the first extent is not being used by an active transaction.
At the beginning of a transaction—in other words, when the first DML command is
issued after a previous
COMMIT
or a user first connects to the database—the transaction is
assigned to an undo segment in the undo tablespace. Any changes to any table in the trans-
action are recorded in the assigned undo segment. The names of the current active undo
95127c13.indd 743 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
744

Chapter 13

N
Managing Data and Undo
segments can be retrieved from the dynamic performance view
V$ROLLNAME
, as you can see
in the following query:
SQL> select * from v$rollname;
USN NAME
---------- ----------------------
0 SYSTEM
1 _SYSSMU1_1192467665$
2 _SYSSMU2_1192467665$
3 _SYSSMU3_1192467665$
4 _SYSSMU4_1192467665$
5 _SYSSMU5_1192467665$
6 _SYSSMU6_1192467665$
7 _SYSSMU7_1192467665$
8 _SYSSMU8_1192467665$
9 _SYSSMU9_1192467665$
10 _SYSSMU10_1192467665$
11 rows selected.
The data dictionary view
DBA_ROLLBACK_SEGS
shows both active (online)
and inactive (offline) undo segments in both the
SYSTEM
and undo
tablespaces.
The undo segment with an undo segment number (
USN

) of 0 is an undo segment reserved
for exclusive use by system users such as
SYS
or
SYSTEM
or if no other undo segments are
online and the data being changed resides in the
SYSTEM
tablespace. In this example, nine
other undo segments are available in the undo tablespace for user transactions.
The dynamic performance view
V$TRANSACTION
shows the relationship between a trans-
action and the undo segments. In the following query, you begin a transaction and then
join
V$TRANSACTION
to
V$ROLLNAME
to find out the name of the undo segment assigned to
the transaction:
SQL> set transaction name ‘Update clerk salaries’;
Transaction set.
SQL> update hr.employees set salary = salary * 1.25
where job_id like ‘%CLERK’;
44 rows updated.
95127c13.indd 744 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Undo Management

745

SQL> select xid, status, start_time, xidusn seg_num,
r.name seg_name
from v$transaction t join v$rollname r
on t.xidusn = r.usn
where t.name = ‘Update clerk salaries’;
XID STATUS START_TIME SEG_NUM SEG_NAME
---------------- ------- ----------------- ------- --------------------
05001100DD020000 ACTIVE 09/25/08 03:03:34 5 _SYSSMU5_1192467665$
1 row selected.
The column
XID
is the internally assigned, unique transaction number assigned to this
transaction, and it is assigned the undo segment
_SYSSMU5_1192467665$
. The column
XIDUSN

(aliased as
SEG_NUM
in the query) is the undo segment number for
_SYSSMU5_1192467665$
.
A transaction can reside in only one undo segment; it cannot be moved to another undo
segment. However, many different transactions can use the same undo segment.
If an extent in the assigned undo segment fills up and more space is required, the next
available extent is used; if all extents in the segment are needed for current transactions, a
new extent is allocated for the undo segment.
All undo segments are owned by
SYS
, regardless of who is making changes in a transac-

tion. Each segment must have a minimum of two extents; the maximum number of extents
in an undo segment is high: for an undo tablespace with a block size of 8KB, the default
maximum number of extents per undo segment is 32,765.
During a media failure with an undo tablespace, the tablespace can be recovered using
archived and online redo log files just as with any other tablespace; however, the instance
must be in a
MOUNT
state to recover an undo tablespace.
Tablespace recovery is discussed in Chapter 16, “Recovering the Database.”
Using Undo Data
Undo data is the old value of data when a process or user changes data in a table or an
index. Undo data serves four purposes in an Oracle Database:
User rollback of a transaction
ÛN
Read consistency of DML operations and queries
ÛN
Database recovery operations
ÛN
Flashback functionality
ÛN
95127c13.indd 745 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
746

Chapter 13
N
Managing Data and Undo
User Transaction Rollback
In Chapter 8, “Introducing Oracle Database 11g Components and Architecture,” you learned
about transactions and how they are managed within the database architecture. At the user

level, you might have one or hundreds of DML commands (such as
DELETE
,
INSERT
,
UPDATE
,
or
MERGE
) within a particular transaction that need to be undone by a user or a process that
is making changes to one or more tables. Undoing the changes within a transaction is called
rolling back part or all of the transaction. The undo information needed to roll back the
changes is called, appropriately, the rollback information and is stored in a special type of
tablespace called an undo tablespace.
When an entire transaction is rolled back, Oracle undoes all the changes since the begin-
ning of the transactions, using the saved undo information in the undo tablespace, releases
any locks on rows involved in the transaction, and ends the transaction.
If a failure occurs on the client or a network, abnormally terminating the user’s connec-
tion to the database, undo information is used in much the same way as if the user explicitly
rolled back the transaction, and Oracle undoes all the changes since the beginning of the
transaction, using information saved in the undo tablespace.
Read Consistency
Undo also provides read consistency for users who are querying rows involved in a DML
transaction by another user or session. When one user starts to make changes to a table
after another user has already begun a query against the table, the user issuing the query
will not see the changes to the table until after the query has completed and the user issues
a new query against the table. Undo segments in an undo tablespace are used to reconstruct
the data blocks belonging to the table to provide the previous values of the rows for any
user issuing
SELECT

statements against the table before the DML statements’ transaction
commits.
For example, the user
KELSIEJ
begins a transaction at 3 p.m. that contains several long-
running DML statements against the
EMPLOYEES
table; the statements aren’t expected to finish
until 3:15 p.m. As each DML command is issued, the previous values of each row are saved
in the transaction’s undo segment. At 3:05 p.m., the user
SARAHCR
issues a
SELECT
against
the
EMPLOYEES
table; none of the changes made so far by
KELSIEJ
are visible to
SARAHCR
. The
undo tablespace provides the previous values of the
EMPLOYEES
table to
SARAHCR
and any
other users querying the
EMPLOYEES
table between 3 p.m. and 3:15 p.m. Even if
SARAHCR

’s
query is still running at 3:20 p.m., the query still appears as it did at 3 p.m. before
KELSIEJ

started making changes.
INSERT
statements use little space in an undo segment; only the pointer
to the new row is stored in the undo tablespace. To undo an
INSERT
state-
ment, the pointer locates the new row and deletes it from the table if the
transaction is rolled back.
95127c13.indd 746 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Undo Management

747
In a few situations, either
SARAHCR
’s query or
KELSIEJ
’s DML statements might fail,
because the undo tablespace is not sized correctly or because the undo retention period is
too short.
You can also apply read consistency to an entire transaction instead of just a single
SELECT
statement by using the
SET

TRANSACTION

statement as follows:
SQL> set transaction read only;
Transaction set.
Until the transaction is either rolled back or committed, all queries in the transaction see
only those changes to other tables that were committed before the transaction began. Only
the following statements are permitted in a read-only transaction:
SELECT
ÛN
statements without the
FOR

UPDATE
clause
LOCK
ÛN

TABLE
SET
ÛN

ROLE
ALTER
ÛN

SESSION
ALTER
ÛN

SYSTEM
In other words, a read-only transaction cannot contain any statement that changes data

in a table, regardless of where the table resides. For example, although an
ALTER

USER
com-
mand does not change data in the
USERS
or any other non-
SYSTEM
tablespace, it does change
the data dictionary tables and therefore cannot be used in a read-only transaction.
Monitoring, Configuring, and Administering Undo
Compared with configuring rollback operations in releases previous to Oracle9i, managing
undo in later versions of Oracle requires little intervention. However, two particular situ-
ations will trigger intervention: either not enough undo space to handle all active transac-
tions or not enough undo space to satisfy long-running queries that need undo information
for read consistency. Running out of undo space for transactions generates messages such
as “ORA-01650: Unable to extend rollback segment”; long-running queries whose undo
entries have been reused by current transactions typically receive the “ORA-01555: Snap-
shot too old” message.
In the following sections, I will show you how to configure the undo tablespace using
two initialization parameters:
UNDO_MANAGEMENT
and
UNDO_TABLESPACE
. I will also present
the methods available for monitoring the health of the undo tablespace, as well as using
EM Database Control’s Undo Advisor to size or resize the undo tablespace. Using the
dynamic performance view
V$UNDOSTAT

, you can calculate an optimal size for the undo
tablespace if the Undo Advisor is not available. Finally, I will show you how to guarantee
that long-running queries will have undo entries available, even if it means that a DML
transaction fails, by using the
RETENTION

GUARANTEE
option.
95127c13.indd 747 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
748

Chapter 13
N
Managing Data and Undo
Configuring the Undo Tablespace
Manual undo management is not recommended, although it is still available in Oracle
11g. Instead, use manual undo management only for compatibility with Oracle8i or ear-
lier. Automatic undo management is the default for the Oracle 11g database. To configure
automatic undo management, use the initialization parameters
UNDO_MANAGEMENT
,
UNDO_
TABLESPACE
, and
UNDO_RETENTION
.
UNDO_MANAGEMENT
The parameter
UNDO_MANAGEMENT

specifies the way in which undo data is managed in
the database: either manually using rollback segments or automatically using a single
tablespace to hold undo information.
The allowed values for
UNDO_MANAGEMENT
are
MANUAL
and
AUTO
. To change the undo-
management mode, you must restart the instance. This parameter is not dynamic, as you
can see in the following example:
SQL> alter system
set undo_management = manual;
set undo_management = manual
*
ERROR at line 2:
ORA-02095: specified initialization parameter cannot be modified
If you are using an spfile, you can change the value of this parameter in the spfile only
and then restart the instance for the parameter to take effect, as follows:
SQL> alter system
set undo_management = manual scope=spfile;
System altered.
UNDO_TABLESPACE
The parameter
UNDO_TABLESPACE
specifies the name of the undo tablespace to use for read
consistency and transaction rollback.
You can create an undo tablespace when the database is created; you can resize it later
or create a new one later. In any case, only one undo tablespace can be active at any given

time, unless the value of
UNDO_TABLESPACE
is changed while the old undo tablespace still
contains active transactions. In this case, the old undo tablespace remains active until the
last transaction using the old undo tablespace either commits or rolls back; all new transac-
tions use the new undo tablespace.
If
UNDO_TABLESPACE
is not defined but at least one undo tablespace exists in the data-
base, the first undo tablespace discovered by the Oracle instance at startup is assigned to
95127c13.indd 748 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Undo Management

749
UNDO_TABLESPACE
. You can find out the name of the current undo tablespace with the
SHOW

PARAMETER
command, as in the following example:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
----------------------- ----------- --------------------
undo_tablespace string UNDOTBS1
For most platforms, if an undo tablespace is not explicitly created in the
CREATE

DATABASE


command, Oracle automatically creates one with the name
SYS_UNDOTBS
.
Here is an example of how you can switch the undo tablespace from
UNDOTBS1
to
UNDO_BATCH
:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
-------------------------- ----------- -------------------
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=undo_batch;
System altered.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
-------------------------- ----------- -------------------
undo_tablespace string UNDO_BATCH
UNDO_RETENTION
The parameter
UNDO_RETENTION
specifies, in seconds, how long undo information that has
already been committed should be retained until it can be overwritten. This is not a guaran-
teed limit: if the number of seconds specified by
UNDO_RETENTION
has not been reached and if
a transaction needs undo space, already committed undo information can be overwritten.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ----------

undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
To guarantee undo retention, you can use the
RETENTION

GUARANTEE
key-
words for the undo tablespace, as you will see later in this chapter in the
section “Guaranteeing Undo Retention.”
95127c13.indd 749 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
750

Chapter 13
N
Managing Data and Undo
Setting
UNDO_RETENTION
to zero turns on automatic undo retention tuning. Oracle con-
tinually adjusts this parameter to retain just enough undo information to satisfy the longest-
running query to date. If the undo tablespace is not big enough for the longest-running query,
automatic undo retention retains as much as possible without extending the undo tablespace.
In any case, automatic undo retention attempts to maintain at least 900 seconds, or 15 min-
utes, of undo information.
Regardless of how long undo information is retained, it falls into one of three categories:
Uncommitted undo information This is undo information that is still supporting an
active transaction and is required in the event of a
ROLLBACK
or a transaction failure. This

undo information is never overwritten.
Committed undo information Also known as unexpired undo, this is undo information
that is no longer needed to support an active transaction but is still needed to satisfy the
undo retention interval, as defined by
UNDO_RETENTION
. This undo can be overwritten, how-
ever, if an active transaction needs undo space.
Expired undo information This is undo information that is no longer needed to support
an active transaction and is overwritten when space is required by an active transaction.
Here is an example of how you can change undo retention from its current value to 12 hours:
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------ ----------- -----------------------
undo_retention integer 600
SQL> alter system set undo_retention = 43200;
System altered.
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------ ----------- -----------------------
undo_retention integer 43200
Unless you use the
SCOPE
parameter in the
ALTER

SYSTEM
command, the change to
UNDO_RETENTION
takes effect immediately and stays in effect the next time the instance is
restarted.

Monitoring the Undo Tablespace
Undo tablespaces are monitored just like any other tablespace: if a specific set of space thresh-
olds is not defined, the database default values are used; otherwise, a specific set of thresholds
95127c13.indd 750 2/17/09 2:49:24 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.