Useful shortcuts for vi editor

Tuesday, 16 May 2017

ORA-2049 examples

Error
ORA-2049 timeout: distributed transaction waiting for lock

SQL> sho parameter distr
-- Default: 60 sec

e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row

IN -B- Database
select * from employees@a_dblink where employee_id = 100

update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049 

To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5

Then restart the instance to perform.

To wait just one second in -B- Database:
select* from  employees@a_dblink
where employee_id = 100 
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked

Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;

commit;

Not to wait:
select* from  employees@a_dblink
where employee_id = 100 
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked

Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;

commit;