D. The last ROLLBACK statement rolls back all DML statements since SAVEPOINT SAVE_4; the last UPDATE was executed before the SAVEPOINT to SAVE_4, therefore the change made by the last UPDATE is unchanged, and the salary remains 1500.

C. The ALTER USER command changes data, even though it resides in the data dictionary; no data in a table can be changed in a READ ONLY transaction.

A, C. You cannot dynamically change the parameter UNDO_MANAGEMENT after the instance has started. You can, however, change the UNDO_TABLESPACE parameter to switch to another undo tablespace while the instance is up and running.

A. Guaranteed undo retention can be set at the tablespace level by using the RETENTION GUARANTEE clause with either the CREATE TABLESPACE or ALTER TABLESPACE command. Only undo tablespaces can have this attribute.

A. When database activity is at its peak, the V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE, can be used to calculate an optimal undo tablespace size. Also, the Undo Advisor in the EM Database Control can provide the same optimal tablespace size in a GUI environment.

E. SHARE mode permits concurrent queries but prohibits updates to the locked table. SHARE mode is required to create an index on the table.

B. The highest level at which a user can request a lock is the table level; the only other lock level available to a user is a row level lock. Users cannot lock at the block or schema level.

D. At 11:45, both sessions are waiting for the row locked by the other session. Within a short but predetermined amount of time, Oracle rolls back the statement that detected the deadlock, which could be either session and is not dependent on when each of the transactions started or attempted to update rows locked by other users.

B. The column XIDUSN in the view V$TRANSACTION can be joined with the column USN in V$ROLLNAME to retrieve the column NAME in V$ROLLNAME containing the rollback segment name.

A. If an undo tablespace is not explicitly created in the CREATE DATABASE command, Oracle automatically creates one with the name SYS_UNDOTBS.

B, C. Locks are resolved at the user level by either committing or rolling back the transaction holding the lock. Also, the DBA can kill the session holding the lock as a last resort.

A, D. If a transaction fills up an undo segment, either a new extent is allocated for the undo segment or other extents in the segment are reused if the undo data in those extents is no longer needed by other transactions using the same undo segment. Transactions cannot cross segment boundaries in an undo tablespace nor can they move to another segment.

B. Regardless of the type of lock requested, NOWAIT is required if you want the command with the lock request to terminate immediately if a lock is already held on the table.

D. At 10:01, Session 1 waits for Session 2. At 10:05, a deadlock will occur; Oracle detects the deadlock and cancels one of the statements.

B. Undo information is required for instance recovery, but only to roll back uncommitted transactions after the online redo logs roll forward.

C. Undo segments are always owned by SYS.

B. The online redo log files are used to roll forward after an instance failure; undo data is used to roll back any uncommitted transactions.

A. The dynamic performance view V$TRANSACTION contains the column XIDUSN, which is the undo segment number in the current undo tablespace.

B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, as the DBA will not receive another alert until the next day when another query fails.

D. The Undo Advisor screen uses the desired time period for undo data retention and analyzes the impact of the desired undo retention setting.