Oracle Cloud Infrastructure
Coming soon ...
Apps R12.2
  • R12.2 Architecture
  • Cloning Error (RC-50208)
  • Apps R12.1
  • Major changes from 11i
  • R12:HTTP Debug Logging
  • Compile Apps Schema invalid objects in R12
  • Apps 11i
  • Apps Components and Architecture
  • Concurrent managers
  • Patching
  • Using AD Patch
  • Using AD Control
  • FNDCPASS utility
  • Single-node Installation
  • Multi-node Installation
  • Find Apps Version
  • Cloning
  • Upgrade 11.5.9 to 11.5.10.2
  • Upgrade from 11.5.10.2 to R12
  • Upgrading 9i to 10g with 11i
  • 11i/R12 General Topics
  • AppsDBA System Management Utilities Guide
  • Identifying Long Idle Sessions
  • Identifying High Active Sessions
  • Change hostname for EBS
  • Oracle 12c Database
  • Oracle12c PSU Apply
  • Oracle12c Datafile moved Online
  • Oracle 11g Database
  • Upgrade 10g to 11g R1
  • Upgrade 11.2.0.2 to 11.2.0.3
  • Database 9i-10g
  • Top 99 Responsibilities of a DBA
  • General Info
  • Database Patching
  • 10g:ASM
  • 10g:Data Pump
  • 10g:Data Guard Installing
  • 10g:Rollback Monitoring
  • 10g:Flashback Table
  • Tablespace Management
  • Materialized Views
  • 10g:Enterprise Manager
  • 10g:Upgrade
  • Error:Ora-01631
  • DBA Scripts
  • Disk I/O,Events,Waits
  • Tablespace Information
  • Session Statistics
  • Hit/Miss Ratios
  • User Information
  • Rollback Segments
  • Full Table Scans
  • Contention/Locking
  • Redo Log Buffer
  • Data Dictionary Info
  • Oracle10g Application Server
  • Oracle10g Application Installation
  • (Re)Securing OAS Control
  • Oracle AS10g null protocol issue
  • Oracle Backup & Recovery
  • RMAN
  • RMAN Setup
  • Backup Recovery
  • Flash Recovery Area
  • Oracle10g Discoverer with Apps
    Coming soon ..............
    Troubleshooting
  • Discoverer Troubleshooting
  • Access EBS in mozile
  • Linux and Unix Platforms
  • How To configure NFS
  • Unix/Linux Command
  • Change hostname in Linux
  • SENDMAIL configuration
  • This Oracle Application DBA Portal is the biggest knowledge gateway for the people in the world of Oracle...
    Monday, August 3, 2009
    Contention/Locking
    Contention/Locking
    Here are some scripts related to Contention/Locking .

    Lock Info
    LOCK INFORMATION NOTES:



    OS User - Name of operating system user
    OS PID - Operating system process id
    Oracle User - Name of Oracle user
    Oracle ID - Oracle session id
    Lock Type - Type of lock
    Lock Held - Current lock held
    Lock Requested - Type of lock requested
    Status - Status of object (Blocking, Not Blocking, Global)
    Object Owner - Owner of the object
    Object Name - Name of the object

    select OS_USER_NAME os_user,
    PROCESS os_pid,
    ORACLE_USERNAME oracle_user,
    l.SID oracle_id,
    decode(TYPE,
    'MR', 'Media Recovery',
    'RT', 'Redo Thread',
    'UN', 'User Name',
    'TX', 'Transaction',
    'TM', 'DML',
    'UL', 'PL/SQL User Lock',
    'DX', 'Distributed Xaction',
    'CF', 'Control File',
    'IS', 'Instance State',
    'FS', 'File Set',
    'IR', 'Instance Recovery',
    'ST', 'Disk Space Transaction',
    'TS', 'Temp Segment',
    'IV', 'Library Cache Invalidation',
    'LS', 'Log Start or Switch',
    'RW', 'Row Wait',
    'SQ', 'Sequence Number',
    'TE', 'Extend Table',
    'TT', 'Temp Table', type) lock_type,
    decode(LMODE,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', lmode) lock_held,
    decode(REQUEST,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', request) lock_requested,
    decode(BLOCK,
    0, 'Not Blocking',
    1, 'Blocking',
    2, 'Global', block) status,
    OWNER,
    OBJECT_NAME
    from v$locked_object lo,
    dba_objects do,
    v$lock l
    where lo.OBJECT_ID = do.OBJECT_ID
    AND l.SID = lo.SESSION_ID

    SQL Lock Info
    SQL ASSOCIATED WITH LOCK NOTES:



    Oracle User - Name of the oracle user
    SID - Oracle session id
    Serial# - Serial# of the process
    Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)
    Held - Type of lock held
    Requested - Type of lock requested
    ID1 - Resource identifier #1
    ID2 - Resource identifier #2
    SQL - SQL statement

    select sn.USERNAME,
    m.SID,
    sn.SERIAL#,
    m.TYPE,
    decode(LMODE,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive') lock_type,
    decode(REQUEST,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive') lock_requested,
    m.ID1,
    m.ID2,
    t.SQL_TEXT
    from v$session sn,
    v$lock m ,
    v$sqltext t
    where t.ADDRESS = sn.SQL_ADDRESS
    and t.HASH_VALUE = sn.SQL_HASH_VALUE
    and ((sn.SID = m.SID and m.REQUEST != 0)
    or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
    (select s.ID1, s.ID2
    from v$lock S
    where REQUEST != 0
    and s.ID1 = m.ID1
    and s.ID2 = m.ID2)))
    order by sn.USERNAME, sn.SID, t.PIECE

    Labels:

    posted by Srinivasan .R @ 11:20 PM  
    0 Comments:
    Post a Comment
    << Home
     
    About Me

    Name: Srinivasan .R
    Home: Chennai, India

    About Me:
    I am working as an Oracle Applications DBA specializing in EBS 11i/R12 with Over 14+ years of experience, mainly in different versions of Oracle Database & Application administration on various platforms like HP-UX, SOLARIS, AIX, Red hat Linux & Windows
    See my complete profile
    High Availability
  • Oracle10g RAC Installation
  • A Quick Reference for Oracle Database 10g RAC on Linux and Unix Platforms
  • Implementing Oracle 10g RAC with ASM on AIX
  • Locked objects for whole RAC
  • Monitor Memory RAC
  • Sessions RAC
  • Install Oracle 11g RAC On Linux
  • Migrating Oracle10g DB to ASM
  • Helpful Links
  • Good Metalink Notes
  • Discoverer:Metalink Notes
  • Logs Scripts:Metalink Notes
  • Support:Metalink Notes
  • Previous Post
  • Full Table Scans
  • SENDMAIL configuration
  • Rollback Segments
  • User Information
  • Hit/Miss Ratios
  • Session Statistics
  • Tablespace Information
  • Disk I/O, Events, Waits
  • General Info
  • Migrating Oracle10g DB to ASM
  • Archives
    Download Software
  • Oracle 11g
  • Oracle 10g
  • 10g Express Edition
  • Oracle 9i
  • Oracle Apps
  • Oracle Linux
  • Oracle VM
  • App Server
  • Solaris
  • Fedora
  • Fedora
  • OpenSUSE
  • Ubuntu
  • Advertisement Links
    INTUIT Technology

    MACHS DATA

    Add Ons
    Locations of visitors to this page

    Add to Google Reader or Homepage

    Template by
    Sreene



    Oracle Application DBA Portal