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: _at_OracleSK

> "l.flatz_at_bluewin.ch" <l.flatz_at_bluewin.ch> 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

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