Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Oracle locking up..?

Oracle locking up..?

From: Noddy <rodi_at_bbs.no>
Date: 13 Feb 2004 05:54:45 -0800
Message-ID: <a62a27da.0402130554.22956637@posting.google.com>

We have an application that updates a row in a table. Sometimes Oracle does not respond for over 10 seconds. This is after an insert. It seems that there is a lock somewhere. This is a multi user system. Records can be added 3+ pr. second.

We have a procedure stored that does the actuall insert. Documented below. As you can see the insert is pretty straight forward. (look at the last section in the procedure).

What can cause this situation in Oracle;

Do Oralce log whether or not a table has been locked and for how long ?

Thanx. Noddy


      procedure add_user_contentprovider( v_userid IN varchar,
   			 						   v_contentproviderid IN varchar,									  
v_account_number IN varchar,						           v_email IN
varchar,								   v_firstname IN varchar,							   v_lastname IN
archar,
									           v_address_line1 IN varchar,							  
v_address_line2 IN varchar,							   v_city IN varchar,								  
v_postalcode IN varchar,							   v_country IN varchar,
								                 v_phonenumber IN varchar,							 v_modifier
IN varchar,								 v_updatestatus OUT varchar)
	is
	   t_status varchar(10):=null;
	   t_login varchar(255) := null;
	begin
		    /* is the user deleted */
			deleted_user(v_userid,t_status);
			if t_status is not null then
			  v_updatestatus := t_status;
			  return;
			end if;

			provider_exists(v_contentproviderid, t_status);
			if t_status is not null then
			  v_updatestatus := t_status;
			  return;
			end if;

			select login_id into t_login
			from customer_biller
			where biller_login_id = v_contentproviderid
			and account_number = v_account_number;


/* if t_login is not null the agreement exists */
if t_login is not null then v_updatestatus := '23005'; return; end if;
/* if not data found the agreement doesn't exist */
exception when no_data_found then v_updatestatus := null;
/* everything is ok, insert the agreement */
insert into customer_biller (login_id,biller_login_id,account_number,email, first_name,last_name,phone,address_line_1, address_line_2,city,zip_code,country, status, customer_biller_login_id, activation_date, request_date, confirm_date, CREATOR, MODIFIER, PASSPHRASE) values(v_userid,v_contentproviderid,v_account_number, v_email,v_firstname,v_lastname,v_phonenumber, v_address_line1,v_address_line2,v_city,v_postalcode,v_country, 'PENDING', v_userid, DATE '1900-01-01', DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier, 'PASSPHRASE'); end;

end; Received on Fri Feb 13 2004 - 07:54:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US