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.
Thanks in advance.
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 - &
--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
Thanks, I will check it up.
Originally Posted by helloravi
OEM and TOAD tools, read the Oracle docs for locks and enqueues.
"What is past is PROLOGUE"
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.
Click Here to Expand Forum to Full Width