0065-0067

Previous Table of Contents Next

Page 65

If other users want to see the data while the change is pending, they always have access to the previous version from the rollback segment. They are provided with a read-consistent version of the data. Once the change is committed, the modified version of the data is available.

Rollback segments are always owned by the user SYS, and no Oracle user can access them for viewing.

Rollback segments use storage in the same way as other segments in terms of extents. With a rollback segment, however, you must initially allocate a minimum of two extents instead of only one.

The first rollback segment is created automatically when the database is first created and has a name of SYSTEM, and it uses storage in the first tablespace, which also has a name of SYSTEM.

CAUTION

It can get confusing that Oracle uses the same name for three different types of objects; the first tablespace is called SYSTEM, the first rollback segment is called SYSTEM, and one of the first Oracle accounts created is called SYSTEM. They are different types of objects, so do not confuse them.

Temporary Segments

Temporary segments use storage in the database files to provide a temporary work area for intermediate stages of SQL processing and for large sort operations.

Oracle creates temporary segments on-the-fly , and they are automatically deleted when the SMON background process no longer needs them. If only a small working area is required, Oracle does not create a temporary segment but instead uses a part of the PGA (the program global area) memory as a temporary work area.

The following operations might cause Oracle to create a temporary segment:

• Creating an index
• Using the ORDER BY, DISTINCT, or GROUP BY clauses in a SELECT statement
• Using the set operators UNION, INTERSECT, and MINUS
• Creating joins between tables
• Using some subqueries

The database administrator can control which tablespaces contain the temporary segments on a user-by-user basis.

Page 66

Bootstrap/Cache Segment

A bootstrap or cache segment is a special type of object on the database that is used to perform an initial load of the data dictionary cache in the shared pool area of the SGA.

Oracle uses the cache segment only when the instance first starts and does not use it again until the instance restarts. Once the segment is used to perform the initial load of the data dictionary cache, the remainder of the cache in memory is steadily populated as statements are executed against the database.

Protecting Your Data

This section discusses the idea of saving or reversing out changes made ”committing and rolling back.

Transactions, Commit, and Rollback

Database changes are not saved until the user explicitly decides that the insert, update, and delete statements should be made permanent. Up until that point, the changes are in a pending status, and any failures, such as a machine crash, will reverse the changes.

A transaction is an atomic unit of work comprising one or more SQL statements; it begins when the user first connects to the database and ends when a COMMIT or ROLLBACK statement is issued. Upon a COMMIT or ROLLBACK, the next transaction automatically begins. All the statements within a transaction are either all saved (committed) or all reversed (rolled back).

Committing a transaction makes changes permanent in the entire transaction to the database, and once committed, the changes cannot then be reversed. Rolling back reverses all the inserts , updates, deletes in the transaction; again, once rolled back, those changes cannot then be committed. Internally, the process of committing means writing out the changes recorded in the SGA's redo log buffer cache to the online redo log files on disk. If this disk I/O succeeds, the application receives a message indicating a successful commit. (The text of the message changes from one tool to another.) The DBWR background process can write out the actual Oracle data blocks in the SGA's database buffer cache at a later time. If the system should crash, Oracle can automatically reapply the changes from the redo logs files even if the Oracle data blocks were not written back to the database files before the failure.

CAUTION

DDL statements such as CREATE TABLE will automatically issue a COMMIT, even if the DDL statement itself fails.

Oracle also implements the idea of statement-level rollback. If a single statement fails during a transaction, the entire statement will fail. In other words, an INSERT statement for 1,000 rows

Page 67

will insert either all 1,000 rows or none at all; the entire statement works, or nothing happens. If a statement does fail within a transaction, the rest of the statements in the transaction are still in a pending state and must be committed or rolled back.

If a user process terminates abnormally (the process is killed , for example), the PMON background process automatically rolls back changes. Any changes that the process had committed up to the point of failure remain committed, and only those changes for the current transaction are rolled back.

All locks held by the transaction are automatically released when the transaction commits or rolls back or when the PMON background process rolls back the transaction. In addition, other system resources (such as rollback segments) are released for other transactions to use.

Savepoints enable you to set up markers within a transaction so that you have the option of rolling back just part of the work performed in the transaction. You can use savepoints in long and complex transactions to provide the reversing option for certain statements. However, this causes extra overhead on the system to perform the work for a statement and then reverse the changes; usually, changes in the logic can produce a more optimal solution. When Oracle performs a rollback to a savepoint, the rest of the statements in the transaction remain in a pending state and must be committed or rolled back. Oracle releases the locks taken by those statements that were rolled back.

Data Integrity

Data integrity is about enforcing data validation rules ”such as checking that a percentage amount is between 0 and 100 ”to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves (and the same rules were checked repeatedly in different programs). Oracle, however, enables you to define and store these rules against the database objects to which they relate so that you need to code them only once so they are enforced whenever any kind of change is made to the table, regardless of which tool issues the insert, update, or delete statement. This checking takes the form of integrity constraints and database triggers.

Integrity Constraints

Integrity constraints enforce business rules at the database level by defining a set of checks for the tables in your system. These checks are automatically enforced whenever you issue an insert, update, or delete statement against the table. If any of the constraints are violated, the insert, update, or delete statement is rolled back. The other statements within the transaction remain in a pending state and can be committed or rolled back according to application logic.

Because integrity constraints are checked at the database level, they are performed regardless of where the insert, update, delete statement originated ”whether it was an Oracle or a non-Oracle tool. Defining checks using these constraints is also quicker than performing the same checks using SQL. In addition, the information provided by declaring constraints is used by the Oracle optimizer to make better decisions about how to run a statement against the table. The Oracle

Previous Table of Contents Next