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

Other related posts: