Data Changes Flashcards Preview

Oracle DB12c > Data Changes > Flashcards

Flashcards in Data Changes Deck (21):
1

When is the logbuffer refreshed?

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

2

What can end a change?

COMMIT or ROLLBACK

3

What is the process for instance recovery?

Recover data files

Apply redo logs files (roll forward)

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

4

What is the process for a transaction?

- 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.

5

UNDO_MANAGEMENT

Requires restart

MANUAL or AUTO

6

UNDO_TABLESPACE

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.

7

UNDO_RETENTION

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.

8

Flashback Query

Query data as it existed in the past.

9

Flashback version query

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

10

Flashback transaction

Backs out a transaction and its dependent transactions

11

Flashback Table

Point in time recovery of an individual table

12

RETENTION GUARANTEE

Not an initparam. Used in ALTER like this:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE

Prioritizes undo retention over DML failure.

13

V$UNDOSTAT

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.

14

SELECT … FOR UPDATE

Select that locks corresponding rows.

15

Table lock mode: Row Share

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

16

Table lock mode: Row Exclusive

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

17

Table lock mode: Share

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

18

Table lock mode: Share Row Exclusive

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

19

Table lock mode: Exclusive

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

20

NOWAIT mode

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.

21

WAIT mode

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.