JProfiler Setup

set Both x86 and x64 on the LD_LIBRARY_PATH

/home/demo/test/jprofiler4/bin/linux-x86:/home/demo/test/jprofiler4/bin/linux-x64

if not following error happens

Error occurred during initialization of VM
Could not find agent library on the library path or in the local directory: jprofilerti

TM enq: Locking

If an index does not exists on a foreign key column then,
when an update happens on that foreign key column the referenced column on the parent table is locked. No update will be possible on this column. Other columns in the parent table are not affected.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));

begin
for i in 1 .. 10
loop
insert into x values (i, i + 10);
end loop;
end;
/

begin
for i in 1 .. 5
loop
insert into y values (i, i + 5);
end loop;
end;
/

session 1
update y set c = 8 where d=10;

1 row updated.

session 2
update x set a = 12 where a = 7;

hangs.....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

lock_id1 gives the object id of the child table which has the lock

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

quering v$lock it is possible to find the parent table as well.

There's also a insert, update/delete combination which will leave one session hanging.

session 1
insert into x values (11,21);

session 2
update x set a = 12 where a = 7;

or

delete from x where b = 18;

hangs....

query dba_waiters from session 1
select lock_type,mode_held,mode_requested,lock_id1 from dba_waiters;

LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738

select object_name from user_objects where object_id=227738;
object_name
-----------
Y

running the delete or update statement first and insert statement second doesn't cause this blocking.

SQL Plus Input Ouput

Input to sqlplus

set verify off
set serveroutput on
accept new_dept_name char prompt 'Enter new department name '
declare
dept_name varchar2(100);
begin
dept_name := &new_dept_name; -- assign the value taken from user
end;
/

SET AUTOPRINT ON
variable maximum_department_number number
declare
max_dept_num number;
begin
select max(deptno) into max_dept_num from dept;
:maximum_department_number := max_dept_num; -- pl/sql value assigned to sqlplus variable
end;
/

set autoprint print the sqlplus variable at the end of the execution
to explicitly print

print maximum_department_number