Lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Lock

  1. #1
    Join Date
    Aug 2000
    Posts
    33

    Lock

    Hi,

    I have an experience on a routine basis, mostly in the high peak time, one of the table is getting locked. But its happening very frequently now these days. Could any one help me out to find out this and solving it. any tools that you recommend to identify this.
    Please help
    Thanks in advance.

    Ravi

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    I wrote a "audit" query on v$sqlarea to check what type of sql ddl OR DML is performed on a daily basis on my db. You can use this query and just change your filters in the "where" clause to include your preferences
    Hope it helps !

    SELECT s.SQL_TEXT,s.FIRST_LOAD_TIME AS TIME
    FROM V$SQLAREA s
    ---CHANGES MADE ON THIS DAY SYSDATE - &
    WHERE SUBSTR(s.FIRST_LOAD_TIME,1,10)=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')
    --USERS THAT USE PLSQL AND NOT THE application
    AND s.MODULE='PL/SQL Developer'
    --- SQL THAT CHANGED PHYSICAL OBJECTS
    AND( S.SQL_TEXT LIKE 'ALTER%' OR S.SQL_TEXT LIKE 'alter%'
    OR S.SQL_TEXT LIKE 'DROP%' OR S.SQL_TEXT LIKE 'drop%'
    OR s.sql_text like 'create%' OR S.SQL_TEXT LIKE 'CREATE%')
    -----SQL THAT CHANGES DATA
    OR (s.SQL_TEXT like 'update%' or s.SQL_TEXT like'UPDATE%'
    OR s.SQL_TEXT like 'INSERT INTO%' OR s.SQL_TEXT like 'insert into%'
    OR s.SQL_TEXT like 'delete%' or s.SQL_TEXT like 'DELETE%')
    ---- object type (table)
    -- and s.SQL_TEXT like 'SECURITY.LOG_DDL%'
    Able was I ere I saw Elba

  3. #3
    Join Date
    Aug 2000
    Posts
    33
    Thanks, I will check it up.

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by helloravi
    Hi,

    I have an experience on a routine basis, mostly in the high peak time, one of the table is getting locked. But its happening very frequently now these days. Could any one help me out to find out this and solving it. any tools that you recommend to identify this.

    OEM and TOAD tools, read the Oracle docs for locks and enqueues.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Also, you can query the v$locked_object to see what objects are locked and who does this.
    Last edited by Lily_Liu_2004; 11-04-2005 at 01:57 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width