Oracle Database Administration/Data Concurrency

This lesson introduces Oracle database data concurrency.

Objectives and Skills
Objectives and skills for the data concurrency portion of Oracle Database Administration I certification include:
 * Managing Data and Concurrency
 * Monitor and resolve locking conflicts

Readings

 * 1) Oracle: Database Concepts Data Concurrency and Consistency

Multimedia

 * 1) YouTube: How to kill a user session connected to an Oracle database

Activities

 * 1) Resolve locking conflicts using Enterprise Manager Database Control.
 * 2) Connect to the database and identify the current session using the following query:
 * 3) Connect to the database in a second session and identify the session using the following query:
 * 4) In the first session, begin a transaction using the following query:
 * 5) In the second session, begin a transaction using the following query:
 * 6) Use Enterprise Manager Database Control / Performance / Blocking Sessions to view blocking sessions. Note the Session ID and Serial Number of the idle session.
 * 7) Select Kill Session and then Show SQL.
 * 8) Kill the idle session and then refresh Blocking Sessions.
 * 9) Return to the two sessions and observe the results.
 * 10) Identify the current session using the following query:
 * 11) In the session that is still active, roll back the transaction using the following query:
 * 12) Resolve locking conflicts using SQL.
 * 13) Connect to the database and identify the current session using the following query:
 * 14) Connect to the database in a second session and identify the session using the following query:
 * 15) In the first session, begin a transaction using the following query:
 * 16) In the second session, begin a transaction using the following query:
 * 17) Connect to the database in a third session and identify the blocking and blocked sessions using the following queries:
 * 18) Identify the SID and Serial# of the blocking session using the following query:
 * 19) Kill the blocking session by filling in the SID and Serial# in the following query:
 * 20) Return to the two sessions and observe the results.
 * 21) Identify the current session using the following query:
 * 22) In the session that is still active, roll back the transaction using the following query:
 * 23) Create a deadlock.
 * 24) Connect to the database and identify the current session using the following query:
 * 25) Connect to the database in a second session and identify the session using the following query:
 * 26) In the first session, begin a transaction using the following query:
 * 27) In the second session, begin a transaction using the following query:
 * 28) In the first session, extend the transaction using the following query:
 * 29) In the second session, extend the transaction using the following query:
 * 30) In the session that detects a deadlock, roll back the remaining transaction using the following query:
 * 31) In the session that is still active, roll back the transaction using the following query: