CREATE PACKAGE my_types AS

TYPE expl_cur IS REF CURSOR;

END;


CREATE OR REPLACE PROCEDURE explain_it (p_plan_id IN VARCHAR2,

                             p_sql     IN VARCHAR2,

                             p_plan    OUT my_types.expl_cur) AS

    l_sqlstr VARCHAR2(32767);

BEGIN

   l_sqlstr := 'SELECT LPAD('' '',2*(LEVEL-1))||OPERATION||'' ''||OPTIONS||'' ''||OBJECT_NAME||'' ''||';

   l_sqlstr := l_sqlstr||'DECODE(ID,0,''COST = ''||POSITION) "QUERY PLAN" FROM plan_table ';

   l_sqlstr := l_sqlstr||'START WITH ID = 0 AND STATEMENT_ID = :b1 ';

   l_sqlstr := l_sqlstr||'CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = :b2';


   EXECUTE IMMEDIATE 'EXPLAIN PLAN SET statement_id = '''||p_plan_id||''' FOR '||p_sql;



   OPEN p_plan FOR l_sqlstr USING p_plan_id, p_plan_id;

END;[/code]l_sqlstr can be whatever statement you like to use to display a plan, including dbms_xplan.display if it is available.  If you are on 9i, you can dispense with the package, and use a SYS_REFCURSOR instead of the package cursor.  Then grant execute on the procedure to the users who need to explain the views.



SQL> CREATE VIEW t_v AS SELECT * FROM t; 
  
View created.
 
SQL> GRANT CREATE SESSION TO test IDENTIFIED BY test;
 
Grant succeeded.
 
SQL> GRANT SELECT ON t_v TO test;
 
Grant succeeded.
  
SQL> GRANT EXECUTE ON explain_it TO test;
 
Grant succeeded.
 
SQL> connect test/test;
Connected.
SQL> EXPLAIN PLAN SET statement_id = 'test' FOR
  2  SELECT * FROM ops$oracle.t_v;
SELECT * FROM ops$oracle.t_v
                          *
ERROR at line 2:
ORA-01039: insufficient privileges on underlying objects of the view
 
SQL> VAR my_cur REFCURSOR;
SQL> EXEC ops$oracle.explain_it('test', 'SELECT * FROM ops$oracle.t_v', :my_cur)
 
PL/SQL procedure successfully completed.
 
SQL> PRINT my_cur
 
QUERY PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT   COST =
  TABLE ACCESS FULL T
HTH

John