Re: Oracle locking up..?

Oracle Locking up is somewhat vague. I assume you mean someone is issuing sql and it takes forever to return.

You or your DBA should check the data dictionary for locks in tables like DBA_LOCKS, DBA_BLOCKERS, DBA_WAITERS, DBA_DML_LOCKS. If you search the web you could probably find some scripts to run in cron to use this information to identify problems. Also you can use statspack or oracle OEM if you have it installed to see the problems. But that is a whole new area of discussion.

Also, it looks like your Code has hard coded literal values in it, which can slow execution. Oracle likes bind variables to speed parsing. I think these may give a problem, not sure about the exception though (v_updatestatus := '23005')(v_updatestatus := null)

"Noddy" <rodi_at_bbs.no> wrote in message news:a62a27da.0402130554.22956637_at_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;
>
> * Table lock... ? How does Oracle lock tables when updating.
> * ..
>
> 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 - 22:49:49 CET