set linesize 150
set pagesize 2000
set ver off
set serveroutput on

begin
dbms_output.put_line('CONSULTA DE PLAN DE EJECUCIÓN EN CACHE');
end;
/

--consulta de plan de ejecución en cache
SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE SQL_ID = '&1'
ORDER BY PIECE;

select * from TABLE(dbms_xplan.display_cursor('&1', 0, 'ADVANCED'));