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.