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
"l.flatz@xxxxxxxxxx" <l.flatz@xxxxxxxxxx> hat am 14. Juni 2018 um 14:36
geschrieben:
Hi,
you might know Kerry´s classic blog:
http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/.
Normally my work around for explain plan issues is to run the query and use
dbms_xplan.display_cursor.
Now I am working in an environment where I must not run a query, but I can do
explain plan.
But still I think I can not tolerate explain plan weaknesses.
I think it should be possble to use DBMS_SQL to parse a statement and receive
a proper plan without actually running the statement.
Then use dbms_xplan.display_cursor.
Before I spent time, has anybody done it already?
Regards
Lothar
