Hello, my name is Anton Kravchenko. I’m Oracle DBA in Sydney office. After initial months, full of new impressions, charmed by the professionals that are working here in Pythian, i’ve decided that this is the time now when I need to share some of my knowledge, some of my little things, that I was using before. Today I’m going to show two simple procedures that I found useful for me some time ago. I understand that there can be something better, more effective, more elegant solution and I’ll be appreciate for any feedback.
In Pythian we have a lot of different clients, with their unique databases’ schemes. Sometimes some of the clients ask us to implement scripts that change objects in their schemes. And in order to prevent the situation when these scripts hung just because some object is pinned or one or several of the object dependencies are pinned, here is the two simple procedure, that can help to check what we will get during our maintenance.
First procedure helps us to understand whether the object pinned or not:
CREATE OR REPLACE
procedure pinned_obj (obj_owner varchar2, obj_name varchar2, obj_type varchar2) is
cursor c1 is
select distinct decode(a.kglnaown,null,'NO OWNER',a.kglnaown) own, substr(a.kglnaobj,1,30) obj,
b.kgllkhdl kglhandle
from x$kglob a, dba_kgllock b, v$db_object_cache c
where a.kglhdadr=b.kgllkhdl
and a.kglhdexc=c.executions
and upper(c.type)=obj_type
and b.kgllkmod=2 and kgllktype='Pin'
and upper(a.kglnaobj) like obj_name;
cursor c2 is
select distinct a.sid, a.serial#, a.username, a.osuser, a.terminal, a.program, a.module, b.kgllkhdl
from v$session a, dba_kgllock b where a.saddr=b.kgllkuse and b.kgllkmod=2 and b.kgllktype='Pin';
v_owner varchar2(30);
v_name varchar2(30);
v_type varchar2(50);
v_kglhandle RAW(8);
vv_kgllkhdl RAW(8);
k number:=0;
m number:=0;
v_sid number;
v_serial number;
v_username varchar2(30);
v_osuser varchar2(30);
v_terminal VARCHAR2(30);
v_program VARCHAR2(48);
v_module VARCHAR2(48);
begin
open c1;
loop
fetch c1 into v_owner, v_name, v_kglhandle;
if c1%NOTFOUND and k=m then
dbms_output.put_line('Object '||obj_owner||'.'||obj_name||' has no pins at the moment');
exit when c1%NOTFOUND;
else
if c1%NOTFOUND and k<m and m=1 then
exit when c1%NOTFOUND;
else
if c1%NOTFOUND and k<m and m=2 then
exit when c1%NOTFOUND;
end if;
end if;
end if;
dbms_output.put_line('Object '||obj_owner||'.'||obj_name||' is pinned by:');
open c2;
loop
fetch c2 into v_sid, v_serial, v_username, v_osuser, v_terminal,v_program, v_module, vv_kgllkhdl;
if c2%NOTFOUND and k=m then
m:=1;
exit when c2%NOTFOUND;
else
if c2%NOTFOUND and m=2 then
exit when c2%NOTFOUND;
end if;
end if;
if vv_kgllkhdl=v_kglhandle
then
dbms_output.put_line(v_sid||' '||v_serial||' '||v_username||' '||v_osuser||' '||v_terminal||' '||v_program||' '||v_module);
m:=2;
end if;
end loop;
close c2;
end loop;
close c1;
end;
this procedure is executed with three parameters:
SQL> exec pinned_obj(<object_owner>,<object_name>,<object_type>);
If this procedure tells us that the object that interested us has no pins at the moment, we are on the half way to be sure that the code that we are going to implement during our maintenance can be safely executed.
The other part of the problem, that each object could have one or more dependent objects and these dependent objects (if they are pinned) could be invalidated after our intrusion. To understand what is going on amongst all dependencies, we can use another simple procedure:
CREATE OR REPLACE procedure pinned_dep (obj_owner varchar2, obj_name varchar2, obj_type varchar2) is
cursor c1 is
select distinct owner, name, type from DBA_DEPENDENCIES
where referenced_owner=obj_owner
and referenced_NAME=obj_name
and referenced_type=obj_type;
cursor c2(owner_c1 varchar2, obj_c1 varchar2) is
select distinct decode(a.kglnaown,null,'NO OWNER',a.kglnaown) own,
substr(a.kglnaobj,1,30) obj from x$kglob a, dba_kgllock b
where a.kglhdadr=b.kgllkhdl
and b.kgllkmod=2 and kgllktype='Pin' and a.kglnaown=owner_c1
and a.KGLNAOBJ=obj_c1;
cursor c3(owner_c2 varchar2, name_c2 varchar2, type_c1 varchar2) is
select distinct object_type, owner
from all_objects where object_name=name_c2
and owner=owner_c2 and object_type=type_c1;
dep_own c1%rowtype;
v_owner varchar2(30);
v_name varchar2(30);
v_type varchar2(50);
dep_owner varchar2(30);
dep_name varchar2(30);
dep_type varchar2(17);
vv_owner varchar2(30);
k number:=0;
m number:=0;
begin
dbms_output.put_line('Pinned dependencies for object '||obj_owner||'.'||obj_name||':');
open c1;
loop
fetch c1 into dep_owner, dep_name, dep_type;
if c1%NOTFOUND and k=m then
dbms_output.put_line('Object '||obj_owner||'.'||obj_name||' has no dependencies at the moment');
exit when c1%NOTFOUND;
else
if c1%NOTFOUND and k<m and m=1 then
dbms_output.put_line('Object '||obj_owner||'.'||obj_name||' has dependencies but no pins at the moment');
exit when c1%NOTFOUND;
else
if c1%NOTFOUND and k<m and m=2 then
exit when c1%NOTFOUND;
end if;
end if;
end if;
open c2(dep_owner, dep_name);
loop
fetch c2 into v_owner, v_name;
if c2%NOTFOUND and k=m then
m:=1;
exit when c2%NOTFOUND;
else
if c2%NOTFOUND and k<m and m=1 then
exit when c2%NOTFOUND;
else
if c2%NOTFOUND and m=2 then
exit when c2%NOTFOUND;
end if;
end if;
end if;
if v_owner=dep_owner and v_name=dep_name then
open c3(v_owner, v_name, dep_type);
loop
fetch c3 into v_type, vv_owner;
exit when c3%NOTFOUND;
dbms_output.put_line(v_type||' - '||vv_owner||'.'||v_name);
end loop;
close c3;
m:=2;
end if;
end loop;
close c2;
end loop;
close c1;
end;
the syntax is the same:
SQL> exec pinned_obj(<object_owner>,<object_name>,<object_type>);
The most expecting result here is that the object has no dependencies at the moment, or at least that this object has dependencies but no pins at the moment, which tells us that our script can be executed safely (of course if it was tested by the client, but this is out of our scope).
All these scripts were created about 3 years ago and tested on Oracle 9i and 10g. And maybe nowadays there are some more efficient ways to resolve this issue. Glad if someone will show me some of these ways.
1 Comment. Leave new
Such a useful post Anton. Thanks for sharing