Oracle 11g explain plan

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?

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")

Hi Roger, something new about my problem? Regards

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

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.

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

Thanks for the quick feedback! :)