ORA-02403: plan table does not have correct format#.

[Former Member]

Hi all,

i m getting below given error in SM21 when i m trying to execute Explain plan from ST05.

Database error 2403 for XPL

ORA-02403: plan table does not have correct format#.

i dropped and created a plan_table at oracle level and it is working fine at oracle level.

SQL> explain plan for select count(*) from sapr3.bsak;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

-

---

Plan hash value: 1946670104

-

---

Id

Operation

Name

Rows

Cost (%CPU)

Time

-

---

0	SELECT STATEMENT		1	4 (0)	00:00:01
1	SORT AGGREGATE		1
2	INDEX FULL SCAN	BSAK~Z01	2839	4 (0)	00:00:01

-

---

9 rows selected.

when i fired select count(*) from bsak at SAP level , its giving me error.

this is s development system and everything is working fine with QAS system.

can anyone help me with my problem?

[stefan_koehler · Accepted solution]

Hello Milind,

which oracle database version do you use?

On version up to and including 9i, use the utlxplan.sql script to createthe plan table as instructed below.
SQL> @?/rdbms/admin/utlxplan
On 10g and above there is a new script - catplan.sql - to create the plan
table that creates a public plan table as a global temporary table
accessible from any schema.
SQL> @?/rdbms/admin/catplan
Note that the plan table format can change between versions so ensure
that it is created using the utlxplan script from the current version.

Depending on your SAP release you maybe can also implement sapnote #1303908. With this sapnote you are able to use the preferred oracle 10g plan table with "catplan". If you can't use it .. please use the utlxplan to create your plan table (drop the old PLAN_TABLE in your SAP schema and run that script again in the SAP schema).

Regards

Stefan

[Former Member · Accepted solution]

hi Stefan,

thnaks for the reply.

my oracle version is 10g.

earlier i executed ulxplan.sql to create explain plan.

i was not aware of catplan.sql ...

i fired the same and it threw errors as given below.

SQL> @?/rdbms/admin/catplan

create type dbms_xplan_type

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create type dbms_xplan_type_table

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create type sql_plan_row_type

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create type sql_plan_table_type

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create sequence ora_plan_id_seq$

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create global temporary table plan_table$

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

create type sql_plan_stat_row_type

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

No errors.

then i again tried to execute explain plan in st05 at SAP level and it again threw me same error.

i checked the noted given by you , but its for relaese 620 and above where as my system is on 46C.

Regards,

Milind Desai

[stefan_koehler · Accepted solution]

Hello Milind,

> i checked the noted given by you , but its for relaese 620 and above where as my system is on 46C.

If this note was not valid for your release - why did you run the script catplan.sql?

As i wrote "If you can't use it .. please use the utlxplan to create your plan table"

> earlier i executed ulxplan.sql to create explain plan.

Ok but in which schema? This is very important ... you need to run this stuff in the SAP schema .. you can also drop the PLAN_TABLE (in the SAP schema), execute the transaction ST05 and perform an EXPLAIN PLAN. In my SAP release (ECC 6.0) the plan table is created automatically by this transaction, if it does not exist.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

i have created plan_table in sapr3 schema...but still getting the same error

cebci:oraceb 2> env|grep -i schema

dbs_ora_schema=SAPR3

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

Regards,

Milind Desai

[stefan_koehler · Accepted solution]

Hello Milind,

the environment parameter dbs_ora_schema has nothing to do with the database login through SQL*Plus.

You can check the current user easily with this query after you have logged in through SQL*Plus:

SQL> SELECT USER FROM dual;
....
-- Login with the correct user looks like this
shell> sqlplus SAPR3/<Password>

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

sorry for replying you too late.

with ur suggesstions i was able to solve my problem.

explain plan is agaibn working fine in ST05 after the creation of plan_table in SAPR3 schema.

Thanks a lot for kind help

Have a wonderful day

Regards,

Milind Desai

[Former Member]

Thanks Stefan for the kind help of yours.

My problem is solved