Lock conflicts in the Oracle 10g database

Learning objective

After completing this topic, you should be able to detect and resolve lock conflicts.

Exercise overview

In this exercise, you're required to intervene in the resolution of conflicting user sessions.

This involves the following tasks:

• detecting a lock conflict
• resolving a lock conflict

A database user is trying to update a row in a table, but the database has "frozen". The user has asked you, as DBA, to solve the problem. You suspect that the difficulty is being caused by a lock conflict.

Task 1: Detecting a lock conflict

You need to find out what is causing the lock conflict.

Use Enterprise Manager to detect which session is causing the lock conflict.

Steps list

Instructions
1. Click the Blocking Sessions link under Additional Monitoring Links

Task 2: Resolving a lock conflict

You've identified the HR session 230 as causing the conflict, but you cannot get in touch with the user to ask them to roll back or commit the transaction.

You need to resolve the locking conflict in favor of the session that has requested access to the table.

Use Enterprise Manager to view session details and terminate session 230.

Steps list

Instructions
1. Click 230 under Session ID
2. Click Kill Session
3. Click Yes