Needles and Pins

Sep 21, 2011 / By Anton Kravchenko

Tags: , , , ,

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.

One Response to “Needles and Pins”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>