Agrered. I might need Stefans code at an other occaision. Did not know you can
do this.
For the cureent task it seems to be overkill.
Well, thanks for pointing out the fetch bit. I guess it does not hurt to do a
fetch as long as nobody sees the result. :-)
----Ursprüngliche Nachricht----
Von : jonathan@xxxxxxxxxxxxxxxxxx
Datum : 19/06/2018 - 11:23 (GMT)
An : contact@xxxxxxxx, l.flatz@xxxxxxxxxx
Cc : oracle-l@xxxxxxxxxxxxx
Betreff : Re: Re: Explain Plan and Security
Fantastic bit of geekery from Stefan!.
Lothar, you may find that the underlying library bundles the open, execute and
first fetch, so you may not get a plan until actually do the first fetch.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
Sent: 19 June 2018 09:53
To: contact@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Re: Explain Plan and Security
Hi Stefan,
thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not
fetch the data.
Sorry that this is so prosaic.
regards
Lothar
----Ursprüngliche Nachricht----
Von : contact@xxxxxxxx
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-l@xxxxxxxxxxxxx, l.flatz@xxxxxxxxxx
Betreff : Re: Explain Plan and Security
Hey Lothar,
today I had a little bit time to geek out and here is a working test case for
what I've mentioned here
(https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1).
******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');
-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r
-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS
OUTLINE PEEKED_BINDS LAST'));
-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available
anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS
OUTLINE PEEKED_BINDS LAST'));
******************
The process aborts with "ORA-03113: end-of-file on communication channel" right
before/at fetching the data - so this should exactly fit your needs / security
requirements :-)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
