Home » SQL & PL/SQL » SQL & PL/SQL » URGENT!!! How to resolve ORA-04020 Exception
| URGENT!!! How to resolve ORA-04020 Exception [message #1304] |
Thu, 18 April 2002 22:28  |
Karthik
Messages: 63
Registered: February 2000
|
Member |
|
|
Hi,
I have triggers for certain tables all of which insert a record into a log table whenever an insert, update or delete operation occurs in those tables. The log table has a primary key log_id. the trigger gets the maximum log_id & increments it & enters a new record onto the log table with that log_id & enters other details like time, query_type etc into the log table. During the execution of these triggers i get the following problem.
Can anybody plz get a solution to this asap:
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SYS.STANDARD
SO: 803d9b40, type: 35, owner: 802dc328, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=803d9b40 handle=804d016c request=X
call pin=0 session pin=0
user=80115880 session=80115880 count=0 flags=[[00]] savepoint=186256
LIBRARY OBJECT HANDLE: handle=804d016c
name=CTXSYS.TEXTINDEXMETHODS
hash=806f2d90 timestamp=04-09-2002 09:29:12
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[[02000000]]
kkkk-dddd-llll=0000-0257-0257 lock=S pin=S latch=2
lwt=804d0184[[803d9b50,803d9b50]] ltm=804d018c[[804d018c,804d018c]]
pwt=804d019c[[804d019c,804d019c]] ptm=804d01f4[[804d01f4,804d01f4]]
ref=804d0174[[804d0174,804d0174]] lnd=804d0200[[804e52ec,807d48b8]]
Thanx
Karthik
|
|
|
|
| Re: URGENT!!! How to resolve ORA-04020 Exception [message #1309 is a reply to message #1304] |
Fri, 19 April 2002 08:19  |
Sreedhar Palepu
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
Hi Karthik,
It would help if you were to post the code of your trigger. As I understand your trigger(s) when fired from different tables are acquiring the log table for DML operation and placing a lock at the row level and more than one trigger is performing this job and creating locks. I would suggest if the trigger code is similar, create a stored procedure common to all the triggers and have each trigger call the stored procedure. Also, if you were to use a sequence for id column of the log table, it would avoid the excess checking process of log_id and incremting it.
Hope this helps...Sreedutt
|
|
|
|
| Re: URGENT!!! How to resolve ORA-04020 Exception [message #1324 is a reply to message #1309] |
Sun, 21 April 2002 20:15 |
Karthik
Messages: 63
Registered: February 2000
|
Member |
|
|
My trigger is as follows( & all the triggers follow the same pattern):
create or replace trigger trig_role_master_m
after insert or update or delete on role_master_m
for each row
declare
cnt number;
log_id number;
prev_data varchar2(4000);
next_data varchar2(4000);
oper_type varchar2(20);
oper_cond varchar2(500);
begin
log_id := 1;
select count(*) into cnt from audit_log_m;
if (cnt <> 0) then
select max(audit_log_id) into log_id from audit_log_m;
log_id := log_id + 1;
end if;
prev_data := 'Role_Id = ' || :old.role_id || ',Role_Name = ' || :old.role_name ||
',Role_Desc = ' || :old.role_desc || ',Created_User_Id = ' || :old.created_user_id || ',Created_Dt = ' || :old.created_dt || ',Last_Update_Dt = ' || :old.last_update_dt ;
next_data := 'Role_Id = ' || :new.role_id || ',Role_Name = ' || :new.role_name ||
',Role_Desc = ' || :new.role_desc || ',Created_User_Id = ' || :new.created_user_id || ',Created_Dt = ' || :new.created_dt || ',Last_Update_Dt = ' || :new.last_update_dt ;
oper_type := 'update';
if inserting then
oper_type := 'insert';
end if;
if deleting then
oper_type := 'delete';
end if;
oper_cond := '';
if (oper_type <> 'insert') then
oper_cond := 'where role_id = ' || :old.role_id;
end if;
if deleting then
insert into audit_log_m
values(log_id,'ROLE_MASTER_M',prev_data,next_data,sysdate,oper_type,oper_cond,:old.created_user_id);
else
insert into audit_log_m
values(log_id,'ROLE_MASTER_M',prev_data,next_data,sysdate,oper_type,oper_cond,:new.created_user_id);
end if;
end;
Here the table audit_log_m has the foll. columns:
AUDIT_LOG_ID, TABLE_NAME, PREVIOUS_DATA_DESC, CHANGED_DATA_DESC, CHANGED_DT, OPERATION_TYPE, OPERATION_CONDITION, USER_ID
Thanx :)
Karthik
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 00:48:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
