explain plan error [message #196860] |
Sun, 08 October 2006 13:37  |
learningTiger
Messages: 2 Registered: October 2006
|
Junior Member |
|
|
i am trying to use the " explain plan".
the statement is executed and the plan is written to the plan_table.
when i try to read the plan using
select * from table(dbms_xplan.display); i get the error :
ERROR at line 1:
ORA-00904: invalid column name which points to display.
how do i resolve this. what mistake am i doing?
|
|
|
|
Re: explain plan error [message #196882 is a reply to message #196860] |
Mon, 09 October 2006 00:39   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Which version of the database are you using?
@anacedent: (On a 9i db) The OP used valid sql-syntax. dbms_xplan.display is a pipelined function.
Your code yields an error.
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | HASH JOIN | |
|* 3 | TABLE ACCESS FULL | VER_POL_MUTATIES | 1
etc..
SQL> select * from dbms_xplan;
select * from dbms_xplan
ORA-04044: procedure, function, package, or type is not allowed here
|
|
|
|
Re: explain plan error [message #197099 is a reply to message #197062] |
Tue, 10 October 2006 01:18  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Did you, perhaps, create the PLAN_TABLE using UTLXPLAN.SQL of a different Oracle version than the one you are using at the moment? For example, as you're on 9i now, did you create this table using 8i UTLXPLAN.SQL file? This *could* end up with such an error (i.e. table description changed between versions).
Another possibility is that the original query is invalid - check does it run properly without trying to explain it - just see whether all columns in the statement are valid.
|
|
|