If a row has changes that were not committed at the start of query the database

If a row has changes that were not committed at the

This preview shows page 60 - 68 out of 99 pages.

If a row has changes that were not committed at the start of query, the database server constructs a read- consistent image of the row by retrieving the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.
Image of page 60
Read Consistency Transaction Read Consistency Read consistency is always provide from SQL statement. However, you can request read consistency for a read-only transaction by issuing the following command at the beginning of the transaction: SET TRANSACTION READ ONLY; Or, you can request read consistency for a transaction performing DML by issuing the following command at the beginning of the transaction: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; In either case, the database server provides data that is consistent from the start of the transaction. Using SERIALIZABLE can have a negative impact on performance.
Image of page 61
Types of Undo Segments SYSTEM : Used for objects in the SYSTEM tablespace Non-SYSTEM : Used for objects in other tablespace : Auto Mode : Requires an UNDO tablespace Manual Mode : Private : Acquired by a single instance Public : Acquired by any instance Deferred : Used when tablespaces are taken offline immediate, temporary , or for recovery
Image of page 62
Types of Undo Segments SYSTEM Undo Segment The SYSTEM undo segment is created in the SYSTEM tablespace when a database is created. This undo segment can be used only for changes made to objects in the SYSTEM tablespace. The SYSTEM undo segment exists and works the same in both manual and auto mode.
Image of page 63
Types of Undo Segments Non-SYSTEM Undo Segments A database that has multiple tablespaces needs at least one Non- SYSTEM undo segment for manual mode or one UNDO tablespace for auto mode. Manual Mode In manual mode, a non-system undo segment, which is created by the database administrator, can be used for changes made to objects in any non-system tablespace. There are two types of non-system undo segments.
Image of page 64
Types of Undo Segments Private Private undo segments are segments that are brought online by an instance because they are listed in the parameter file. They can be brought online explicitly by issuing an ALTER ROLLBACK SEGEMENT command.
Image of page 65
Types of Undo Segments Public Public undo segments form a pool of undo segments available in a database. Public undo segments are normally used with the database Parallel Server to create a pool of undo segments that can be used by any of the Parallel Server instances. Deferred Undo Segments Deferred undo segments may be created when a tablespace is brought offline. They are used to roll back transactions when the tablespace is brought back online. They are dropped automatically when they are no longer needed. Because deferred undo segments are maintained by the database server, no maintenance is required on your part.
Image of page 66
Automatic Undo Management : Concepts Undo data is managed using an UNDO tablespaces.
Image of page 67
Image of page 68

You've reached the end of your free preview.

Want to read all 99 pages?

  • Fall '15
  • mac

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture