Oracle Locks FAQ

FAQ about Detecting and Resolving Locking Conflicts [ID 15476.1]
  Questions and Answers
     1. How does Oracle handle locking?
     2. How to find the resource definitions?
     3. Which lock modes are required for which table action?
     4. How compatibility of locks work
     5. Which views can be used to detect locking problems?
     6. Which tools are there to diagnostic locking issues?
     7. How to detect locking situations?
     8. How to resolve locking situations?
     9. How to resolve deadlock situations?
     10. Unusual locking problems
     11. How to use dbms_lock?
     References


Row Exclusive vs. Share - What is the difference between the following types of locks? [ID 266720.1]
     RESEARCH / REFERENCES
     EXCLUSIVE VS. SHARED
     Exclusive:
     Shared:
     ROW LEVEL LOCK
     WHEN LOCK CREATED
     ENQUEUE MECHANISM
     ROW EXCLUSIVE VS. ROW SHARE
     DETECTING LOCKS
          @$ORACLE_HOME/rdbms/admin/utllockt.sql


            select * from v$locked_object;
      
         col OS_USER_NAME head OS_USER for a7 trunc
            col ORACLE_USERNAME head ORCL_USER for a10 trunc
         select   ORACLE_USERNAME,OS_USER_NAME,OBJECT_NAME,locked_mode,session_id
            from all_objects a ,sys.V_$LOCKED_OBJECT b
            where a.OBJECT_ID=b.object_id;

     METALINK NOTES
     LOCKING BUG
  References




HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK [ID 122793.1]

METHOD 1: SYSTEMSTATE ANALYSIS

  For Oracle 9.2.0.1 or higher

        $ sqlplus '/ as sysdba'
          oradebug setmypid
          oradebug unlimit
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          quit

METHOD 2: EXAMINE THE X$KGLLK TABLE

Note:1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK 

VIEW: "V$LOCK" Reference Note [ID 29787.1]

Note:1020012.6 SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO

Note:1020011.6 SCRIPT: LOW COMPLEXITY LOCKING INFO

Some good articles about Oracle

1 面对一个全新的环境,作为一个Oracle DBA,首先应该了解什么?
2 Oracle GridControl 11gR1 for Linux 安装和配置指南
3 exadata x2-8的技术方向终于向正确的方向有所改进
4 Oracle Support Master Note for 10g Enterprise Manager Grid Control Agent Performance & Core Dump issues (Doc ID 1087997.1)
5 Working with Materialized Views Creation, Refresh and Monitoring
6 Starting and Stopping Oracle Enterprise Manager 11g Grid Control
7 Grid Control 11 Known Issues, Bugs and Features
8 Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]
9 No Response from the Server, Does it Hang or Spin? [ID 68738.1]
10 Data Gathering for Troubleshooting RAC Issues [ID 556679.1]
11 What is enq: TX - row lock contention?
12 enq: TX - row lock contention
13 Use Batch File to Create Filename Using Date and Time
14 EMD upload error: Upload was successful but collections currently disabled - disk full
15 SQL Loader
16 Script to Detect Tablespace Fragmentation [ID 1020182.6]
17 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
18 Troubleshooting Oracle Clusters and Oracle RAC