nerdexam
Oracle

1Z0-052 · Question #134

View the Exhibits and examine lock waits. Users HR and SH complain that their transactions on one of the application tables, EMP, are waiting for response. Which action would you take to release the…

The correct answer is A. Kill the session of the user SCOTT with session ID118. When one session holds a lock that blocks other sessions, the DBA must kill the blocking session to release the lock and unblock the waiters.

Managing Data and Concurrency

Question

View the Exhibits and examine lock waits. Users HR and SH complain that their transactions on one of the application tables, EMP, are waiting for response. Which action would you take to release the lock and enable users HR and SH to continue with their transactions?

Exhibits

1Z0-052 question #134 exhibit 1
1Z0-052 question #134 exhibit 2

Options

  • AKill the session of the user SCOTT with session ID118.
  • BIssue manual checkpoint using the ALTER SYSTEM command.
  • CModify the profile used by user SCOTT to reduce the CONNECT_TIME limit.
  • DFlush the Shared Pool to remove the SQL statement causing "wait" in memory.

How the community answered

(27 responses)
  • A
    74% (20)
  • B
    4% (1)
  • C
    7% (2)
  • D
    15% (4)

Why each option

When one session holds a lock that blocks other sessions, the DBA must kill the blocking session to release the lock and unblock the waiters.

AKill the session of the user SCOTT with session ID118.Correct

SCOTT's session (ID 118) holds the lock on the EMP table, preventing HR and SH from completing their transactions. Killing SCOTT's session using ALTER SYSTEM KILL SESSION immediately releases all locks held by that session, allowing the waiting transactions to acquire the lock and proceed. This is the standard and direct DBA resolution for a blocking lock situation.

BIssue manual checkpoint using the ALTER SYSTEM command.

A manual checkpoint flushes dirty buffers to disk and updates the control file checkpoint position, but has no effect on session-level row or table locks.

CModify the profile used by user SCOTT to reduce the CONNECT_TIME limit.

Modifying CONNECT_TIME in a profile limits the duration of future connections but does not terminate the currently active blocking session or release its held locks.

DFlush the Shared Pool to remove the SQL statement causing "wait" in memory.

Flushing the Shared Pool invalidates cached SQL execution plans and cursors in memory, but session locks are held at a separate layer and are unaffected by this operation.

Concept tested: Killing blocking sessions to resolve Oracle lock waits

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SYSTEM.html

Topics

#lock waits#kill session#enqueue#concurrent transactions

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice