
HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK
Queries, Sessions
Query to check table lock in Oracle database.
GOAL
How do I know if an Oracle table is locked or not.
DEMO
DEMO: HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK
RELATED
HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE
SOLUTION
Following DBT_ROW_LOCKS query gives details of all locks.
E.g., the output of the query
USERNAME SID SERIAL# PID STATUS BLOCKED BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ ------------------ ---------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 284 52279 30569 INACTIVE NO 292 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 36 36732 30629 INACTIVE NO 211 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 4 47113 30681 ACTIVE AND BLOCKED YES 284 154 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY AAAStsAAHAAAAFfAAA 3 ROW_X (SX): Row Exclusive Table Lock
Written At
08 MAY 202219:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0
Tags In
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum