Re: Perl Issues - checking

From: <JApplewhite_at_austinisd.org>
Date: Fri, 22 Feb 2008 15:47:52 -0600
Message-ID: <OF6951EC98.FB5A4BB9-ON862573F7.00759449-862573F7.0077BCCA@austinisd.org>

Nigel,

Wow, thanks for all the thought you put into that reply.

I'll keep that in mind, but am really pressing the Developers to "do the right thing" and put some bloody Exception Handling in their code -- not repeatedly checking that long-existing tables still exist.

If they can't / won't, I'll ask the creator of our librarly of PL/SQL utility packages to add a function that returns 1 or 0 so it can be called from Perl.
Here's the one we currently have that returns Boolean. I think it's pretty slick and, according to HotSOS Profiler, takes only 24ms (with 3 db buffer cache accesses) vs the 125ms (with 6,405 db buffer cache accesses) of the Select From All_Tables query.

function existsTable(tableName in varchar2) return boolean is

   type RefCurType         is ref cursor;
   testCur                 RefCurType;

begin

   if tableName is null then

      return false;
   end if;
   open testCur for 'select null from ' || tableName;    close testCur;
   return true;
exception

   when others then

      declare
         error_code    number := sqlcode;
         error_msg     varchar2(300) := sqlerrm;
      begin
         if error_code = -942 then
            close testCur;
            return false;
         else
            null;
         end if;
      end;

end existsTable;

Jack C. Applewhite - Database Administrator Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)

Nigel Thomas <nigel_cl_thomas_at_yahoo.com> Sent by: oracle-l-bounce_at_freelists.org
02/22/2008 01:45 PM
Please respond to
nigel_cl_thomas_at_yahoo.com

To
oracle-l <oracle-l_at_freelists.org>
cc

Subject
Re: Perl Issues - checking

Jack
ALL_TABLES is a meaty view - look at all the tables and X$ tables it joins. Have you considered making a stripped down version which returns only what you need?
- if you want tables owned by current user select name
from obj$
where type#=2
and owner# = userenv('SCHEMAID')
- if you want tables granted directly to user create view TABLES_I_CAN_SEE as

select u.name owner
     , o.name table_name
from sys.user$ u, sys.tab$ t, sys.obj$ o

where o.owner# = u.user#
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 0
  and bitand(o.flags, 128) = 0
  and (o.owner# = userenv('SCHEMAID')

       or o.obj# in 
            (select oa.obj# 
             from sys.objauth$ oa 
             where grantee# = userenv('SCHEMAID') -- this finds direct 
grants only
          -- where grantee in ( select kzsrorol from x$kzsro) -- the 
original subquery checks roles
            ) 
       or /* user has system privileges */ 
         exists (select null from v$enabledprivs 
                 where priv_number in (-45 /* LOCK ANY TABLE */, 


                                       -47 /* SELECT ANY TABLE */, 

                                       -48 /* INSERT ANY TABLE */, 

                                       -49 /* UPDATE ANY TABLE */, 

                                       -50 /* DELETE ANY TABLE */) 

                 ) 
      ) 

/

Should reduce the consistent gets more than a little - and depending on your role-based access model, you may be able to lose the system privilege subquery. Possibly your problem is aggravated by the sheer number of entries in objauth$.  

If the app is always run in schemas which don't own the tables, you could flatten out the query - don't compare o.owner# to SCHEMAID and convert the o.obj# in subquery into a join.  

You may be concerned about maintainability over Oracle releases. However OBJ$, TAB$ and USER$ are at the core of the online dictionary and the fundamentals haven't changed for as long as I can remember - certainly not since 5.0; if these change on a database upgrade I expect it will be the least of your problems (fancy migrating 150k tables?)    

Regards Nigel

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 22 2008 - 15:47:52 CST