Re: Perl Issues - checking
Date: Fri, 22 Feb 2008 15:47:52 -0600
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
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;
if tableName is null then
open testCur for 'select null from ' || tableName; close testCur;
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;
Jack C. Applewhite - Database Administrator
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
Re: Perl Issues - checking
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
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$ owhere 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 NigelReceived on Fri Feb 22 2008 - 15:47:52 CST