Lock

[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.
Please help
Thanks in advance.

Ravi

[robertbalmer]

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%'

[helloravi]

Thanks, I will check it up.

[dbasan]

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.

[Lily_Liu_2004]

Also, you can query the v$locked_object to see what objects are locked and who does this.