Can Explain Plan not use "INTO toad_plan_table" by default?

Toad for Oracle - Beta
1

Howdy,

I’m working on an upgrade from Oracle 11.2 to 12.1 (12.2 not supported by the Oracle-supplied app), and I’m testing out some new functionality regarding explain plans on a physical standby open for read (Active Data Guard). Unfortunately, the restriction is that the “INTO table_name” part of EXPLAIN PLAN cannot be used on a read-only database in 12.1. So, this will cause an ORA-16000:

EXPLAIN PLAN SET STATEMENT_ID=‘bleah’ INTO PLAN_TABLE FOR select * from dual;

…whereas this one succeeds:

EXPLAIN PLAN SET STATEMENT_ID=‘whee’ FOR select * from dual;

Even though they are semantically the same, the “INTO” clause in the former causes it to blow colorful ORA-16000 chunks onto my pretty monitor.

The default SYS.PLAN_TABLE$ appears to supersede the TOAD_PLAN_TABLE I have defined in my other databases. So is it possible to default to no “INTO TOAD_PLAN_TABLE” clause if the options are left blank? Maybe a DB version restriction, if that’s not too heavy or requires elevated privs on connection.

Just a fun thought on a Friday afternoon.

Thanks!
Rich

2

it’s a little late for 12.12, but I can look at that once we get into betas again.

3

That’s all I could ask for John. Thanks!

Hope the folks in Florida, Georgia, and the Carolinas are safe!

4

Hey Rich,

I am making a change for next beta that if your plan table is specified in the options as “PLAN_TABLE”, then we’ll leave the INTO clause out completely it in the “explain plan set” commands. That way I don’t have to add any more widgets or checkboxes to the options window.

Give it a try and let me know if that solves your problem.

-John

5

Thanks John! I’ll check it out when it’s released…

Rich

6

Adding this reply to remind me that I still need to recreate my 12.1 Active DG read-only physical standby to test this…

[sigh]

Rich