Data Changes

Question 1

When is the logbuffer refreshed?

Answer 1

The log buffer is 1/3rd full

Log buffer has 1mb of redo data

Three seconds since last flush

Commit has been performed

Checkpoint occurs

Question 2

What can end a change?

Answer 2

COMMIT or ROLLBACK

Question 3

What is the process for instance recovery?

Answer 3

Recover data files

Apply redo logs files (roll forward)

Apply undo data from undo tbspc (roll back uncommitted changes).

Question 4

What is the process for a transaction?

Answer 4

• Statement is issued
• Optimizer determines best way to retrieve data and pushes it to buffer cache
• Change is made to buffer cache data (uncommitted yet)
• Old data is written to undo tbspc and to redo log buffer.
• Any other users see the DB as DB+Undo (old state before commit)
• Redo log buffer gets flushed at least every 3 seconds to redo log files by LGWR.
• Upon commit, committed change is written to redo log buffer.
• Upon checkpoint, changes (dirty blocks in buffer cache) are written to data files by DBWn.

Question 5

UNDO_MANAGEMENT

Answer 5

Requires restart

MANUAL or AUTO

Question 6

UNDO_TABLESPACE

Answer 6

When in automatic undo mgmt mode, this specifies the undo tbspc to use for read consistency.

If this initparam is not specified, the first available undo tbspc is assigned at startup. Use SHOW PARAMETER to see which one it chose.

Question 7

UNDO_RETENTION

Answer 7

Specifies, in seconds, how long committed undo info should be retained until it is overwritten.

Setting to 0 turns on automatic undo retention tuning, tuned to satisfy the longest running query to date.

Question 8

Flashback Query

Answer 8

Query data as it existed in the past.

Question 9

Flashback version query

Answer 9

Query versions of rows that existed for a specified window of time

Question 10

Flashback transaction

Answer 10

Backs out a transaction and its dependent transactions

Question 11

Flashback Table

Answer 11

Point in time recovery of an individual table

Question 12

RETENTION GUARANTEE

Answer 12

Not an initparam. Used in ALTER like this:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE

Prioritizes undo retention over DML failure.

Question 13

V$UNDOSTAT

Answer 13

Collects 10-minute snapshots of the undo tbspc consumption.

OEM uses it in conjunction with UNDO_RETENTION parameter and DB block size to provide an optimal undo tbspc size.

Question 14

SELECT … FOR UPDATE

Answer 14

Select that locks corresponding rows.

Question 15

Table lock mode: Row Share

Answer 15

Permits concurrent access to locked table but prohibits exclusive table locks from other users.

Question 16

Table lock mode: Row Exclusive

Answer 16

Permits concurrent access to locked table but prohibits exclusive or share mode locks from other users. Default for UPDATE, INSERT, DELETE.

Question 17

Table lock mode: Share

Answer 17

Permits concurrent queries but prohibits updates to the table. Required and used by default on CREATE INDEX.

Question 18

Table lock mode: Share Row Exclusive

Answer 18

Permits queries but prohibits Share mode locks from other users or row updates.

Question 19

Table lock mode: Exclusive

Answer 19

Permits queries but prohibits DML from other users. Required and default for DROP TABLE.

Question 20

NOWAIT mode

Answer 20

LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT

Works for any mode. Returns control to user if locks exist rather than waiting through the lock queue.

Question 21

WAIT mode

Answer 21

LOCK TABLE employees IN EXCLUSIVE MODE WAIT 60

Works for any mode. If locks exist, it enters the lock queue but exits and returns control after the specified number of seconds.