[This topic is migrated from our old forums. The original author name has been removed]
I have 1 schema on ORACLE 10 and if i try to see explain plan - everything is OK.
So i have same schema on 11g and if I try "explain plan" i got the error:
... Physical database connection acquired for: Animals new 2
14:15:48 [SELECT - 0 row(s), 0.000 secs] [Error Code: 904, SQL State: 42000] ORA-00904: "ANIMALS"."PLAN_TABLE"."STATEMENT_ID": invalid identifier
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
Ideas?
Hi,
What exact 11g version are you using and what DbVisualizer version?
I just tried with the following which works fine with DbVisualizer 6.5.9:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
It would be great if you can enable debug of DbVisualizer and re-run the explain. Read more in:
http://www.dbvis.com/products/dbvis/doc/faq/faq.jsp#4.14
Post the debug output or email it.
Regards
Roger
support@minq.se
a
anonymous
said
about 15 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Oracle JDBC driver 11.1.0.7.0
DbVisualizer Personal 6.5.8
Java VM: Java HotSpot(TM) Client VM
Java Version: 1.6.0_11
Java Vendor: Sun Microsystems Inc.
OS Name: Windows 2003
OS Arch: x86
OS Version: 5.2
15:24:24 [DEBUG AWT-EventQueue-1 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual"
15:24:24 [DEBUG AWT-EventQueue-1 C.invoke] DefaultEditor-0: T4CConnection.createStatement()
15:24:24 [DEBUG pool-97-thread-1 B.?] DefaultEditor-0: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual")
15:24:24 [DEBUG Thread-69 H.execute] Executing...
15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null'
15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement()
15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("DECLARE objExists NUMBER; BEGIN SELECT count(0) INTO objExists FROM all_all_tables WHERE table_name = 'PLAN_TABLE' AND owner = 'ANIMALS'; IF objExists = 0 THEN EXECUTE IMMEDIATE 'create table ANIMALS.plan_table (statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(30), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30)) '; END IF; END;")
15:24:24 [DEBUG Thread-69 H.execute] Executing...
15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null'
15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement()
15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("explain plan set statement_id='dbvis-1252931064349' into ANIMALS.plan_table for select * from ADM_SYSTEM_CLASSIF where code=10")
15:24:24 [DEBUG Thread-69 H.execute] Executing...
15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null'
15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement()
15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("select substr(operation || ' (' || options || ')', 1, 30 ) as Phase, ID, PARENT_ID, OPERATION, OPTIONS, COST, OBJECT_OWNER, OBJECT_NAME, ANIMALS.plan_table.* from ANIMALS.plan_table start with id=0 and statement_id='dbvis-1252931064349' connect by prior id=parent_id and prior statement_id=statement_id")
SQLState(42000) vendor code(904)
15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: EXCEPTION -> java.sql.SQLException: ORA-00904: "ANIMALS"."PLAN_TABLE"."STATEMENT_ID": invalid identifier
15:24:24 [DEBUG AWT-EventQueue-1 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual"
15:24:24 [DEBUG AWT-EventQueue-1 C.invoke] DefaultEditor-0: T4CConnection.createStatement()
15:24:24 [DEBUG pool-101-thread-1 B.?] DefaultEditor-0: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual")
Roger Bjärevall
said
about 15 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Hi,
Sorry for the delay. We don't currently have Oracle 11g R2 available since it is so new but will install it the next days. I'll get back to you.
Regards
Roger
a
anonymous
said
about 15 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Hi Roger,
something new about my problem?
Regards
Roger Bjärevall
said
about 15 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Hi,
Sorry for the delay.
We're waiting for the Windows edition of 11g2.
Regards
Roger
a
anonymous
said
almost 15 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Hi there,
I have the exact same problem on an 11gR2 Linux Database. Actually the plan table is created, and DBVis also inserts data in the plan table, but then the error mentioned appears. A Fix would be nice...
Sebastian
Edited by: Sebastian Graf on 09.12.2009 16:51
Roger Bjärevall
said
almost 15 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Sebastian,
Unfortunately we've been unable to try this out.
After you execute the Explain Plan feature in DbVisualizer there should be a PLAN_TABLE in the schema. What columns are in it (post the rows listed in the Columns tab for that table).
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Hi,
Could you take a look at this again? I'm evaluating DbVis, just installed Oracle 11.2, and got this error on the following query:
select * from SCOTT.EMP;
It should be easy to replicate (hopefully)...I just enabled the Scott user and ran this query. Query runs fine; the explain plan fails with the error. I looked in the SYS/PLAN_TABLE$ table (not sure if that's the right location.?), but the table is empty.
Thanks.
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Dan,
This is fixed in the next version planned for release in June.
Best Regards
Roger
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Hi,
This is now fixed in DbVisualizer 7.0.7.
Best Regards
Roger
a
anonymous
said
almost 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Hi Roger,
this is indeed fixed but IMHO DbVisualizer shouldn't create the PLAN_TABLE at all in 11g.
From that release in fact a GLOBAL TEMPORARY TABLE is automatically created and available to all users:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_5127.htm
Being a temporary table, it's automatically emptied at the end of the session, so there's no need to delete records from it after the plan generation.
It's created in SYS as PLAN_TABLE$ and there's a public synonym PLAN_TABLE pointing to it. You can verify:
SELECT * FROM all_tables
WHERE owner = 'SYS'
AND table_name = 'PLAN_TABLE$';
SELECT * FROM all_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = 'PLAN_TABLE';
As that's available, I don't see the point of spreading copies of PLAN_TABLE around on every schema where you run an explain plan.
Thank you and kind regards
Kamal
Roger Bjärevall
said
almost 14 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Kamal,
You're right. The reason we still stick with the old PLAN_TABLE implementation is that is works with all versions of Oracle that DbVisualizer supports, from 8 to 11g.
We have on the todo list to make this version dependent instead.
Best Regards
Roger
a
anonymous
said
almost 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Oracle 11g explain plan
Thanks for the quick feedback! :)
Roger Bjärevall
said
over 13 years ago
[This reply is migrated from our old forums.]
Re: Oracle 11g explain plan
Hi,
Just to inform that PLAN_TABLE$ is now supported in the latest DbVisualizer 8.0 version.
Regards
Roger
anonymous