Getting Started - Oracle Database Transactions and Locking Revealed - page 4

EODA@ORA12CR1> print x

USERNAME CREATED
--------------- ---------
GSMCATUSER 01-OCT-14
ANONYMOUS 01-OCT-14
XDB 01-OCT-14
...
DBSNMP 01-OCT-14
ORACLE_OCM 01-OCT-14

36 rows selected.

In this example, we created a test table, T , and loaded it with some data from the ALL_USERS table. We opened a
cursor on that table. We fetched no data from that cursor: we just opened it and have kept it open.

■ Bear in mind that Oracle does not “pre-answer” the query. it does not copy the data anywhere when you open
a cursor—imagine how long it would take to open a cursor on a one-billion-row table if it did. the cursor opens instantly
and it answers the query as it goes along. in other words, the cursor just reads data from the table as you fetch from it.

Note

In the same session (or maybe another session would do this; it would work as well), we proceed to delete all
data from the table. We even go as far as to COMMIT work on that delete action. The rows are gone—but are they? In
fact, they are retrievable via the cursor (or via a FLASHBACK query using the AS OF clause). The fact is that the resultset
returned to us by the OPEN command was preordained at the point in time we opened it. We had touched not a single
block of data in that table during the open, but the answer was already fixed in stone. We have no way of knowing
what the answer will be until we fetch the data; however, the result is immutable from our cursor's perspective. It is
not that Oracle copied all of the preceding data to some other location when we opened the cursor; it was actually the
DELETE command that preserved our data for us by placing it (the before image copies of rows as they existed before
the DELETE ) into a data area called an undo or rollback segment (more on this shortly).

Transactions
A transaction comprises a unit of database work. Transactions are a core feature of database technology. They are part
of what distinguishes a database from a file system. And yet, they are often misunderstood and many developers do
not even know that they are accidentally not using them.
Transactions take the database from one consistent state to the next consistent state. When you issue a COMMIT ,
you are assured that all of your changes have been successfully saved and that any data integrity checks and rules have
been validated. Oracle's transactional control architecture ensures that consistent data is provided every time, under
highly concurrent data access conditions.

Redo and Undo
Key to Oracle's durability (recovery) mechanism is redo, and core to multiversioning (read consistency) is undo.
Oracle uses redo to capture how the transaction changed the data; this allows you to replay the transaction (in the
event of an instance crash or a media failure). Oracle uses undo to store the before image of a modified block; this
allows you to reverse or rollback a transaction.