Re: Re: Re: Explain Plan and Security

Set feedback only
In sql*plus 12.2+ would achieve the same

On Tue, 19 Jun 2018 at 10:42, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> "So long as nobody sees the result".
>
> In that case I think you could "set pause on" from SQL*Plus, start the
> query running, wait a bit for the parse to complete, then press ctrl-C.
> The plan might be available, but no data will have been displayed.
>
> To be tested, of course.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> Sent: 19 June 2018 10:34:44
> To: Jonathan Lewis
> Cc: contact_at_soocs.de; oracle-l_at_freelists.org
> Subject: Re: Re: Re: Explain Plan and Security
>
> 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_at_jlcomp.demon.co.uk
> Datum : 19/06/2018 - 11:23 (GMT)
> An : contact_at_soocs.de, l.flatz_at_bluewin.ch
> Cc : oracle-l_at_freelists.org
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> Sent: 19 June 2018 09:53
> To: contact_at_soocs.de
> Cc: oracle-l_at_freelists.org
> 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_at_soocs.de
> Datum : 19/06/2018 - 10:19 (GMT)
> An : oracle-l_at_freelists.org, l.flatz_at_bluewin.ch
> 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: _at_OracleSK
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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