This practice shows how to terminate a blocking session by using the new
MAX_IDLE_BLOCKER_TIME initialization parameter.
Before starting any new practice, refer to the Practices Environment recommendations.
Step 1 : Set up the environment with two sessions
Step 2 : Set
MAX_IDLE_BLOCKER_TIME to two minutes
Step 3 : Test
HR session, update the employees' salary.
SYSTEM session, set all employees' commission percentage to 0. The statement waits for the lock resources held on the row by
HR be released.
After two minutes, observe that the statement is executed.
Back in the
HR session, query the result of the salaries update.
The session was automatically terminated because it held a resource for longer than two minutes.
Step 4 : Examine the DIAG trace files
View the DIAG trace file.
You can also view the PMON trace file.
SYSTEM session, query the employees' commission percentage.