Explain Plan ORA-00942: table or view does not exist

Toad for Oracle
1

This may be a repeat question.

The Toad Explain plan returns the message:
ORA-00942: table or view does not exist

Toad 14.2.104.1069
Oracle 19.2

Toad Options has Explain Plan Schema as blank and Table is PLAN_TABLE.
There is a PUBLIC SYNONYM PLAN_TABLE on SYS.PLAN_TABLE$ which is a global temp table.

When I log in as a DBA the TOAD Explain Plan works.
The error only happens when I connect as a schema owner user.

The user can do an Oracle EXPLAIN PLAN ON [sql stmt]
The user can do
select * from plan_table;
delete from plan_table;

The user can do an Explain Plan in Oracle SQL Developer, but that's not as pretty as what Toad can deliver.

These didn't work
Toad Options has Explain Plan Schema as [dba_user] and Table is PLAN_TABLE and replacing the PUBLIC SYNONYM (the SYSDBA might send me a strongly worded email).
Toad Options has Explain Plan Schema as [user] and Table is PLAN_TABLE (create table)

How do I get this to work?

2

try leaving the Explain Plan schema blank in Toad options.

If that doesn't solve it, turn on spool SQL to find the statement that is throwing the error.

Database -> Spool SQL -> Spool to screen

1 Like
3

Excellent answer.

A user must be much more than a SCHEMA_OWNER to use the Toad Explain Plan. They need select on v$sql_plan and maybe more. I can take it from here.
grant SELECT_CATALOG_ROLE to RL__SELECT_DBA.
-- this is a local role that already has many DBA grants. The schema user is granted this.

Tested and it works.

-- Session: CLIENT_DB_MAINT_USER[PCCP_USER1]@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-CANARY-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP90.WORLD)))
-- Timestamp: 13:23:57.881
declare
v_ignore raw(100);
v_oldhash number;
v_hash number;
begin
v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
:outHash := v_hash;
end;
:SQLText(VARCHAR[29],IN)='select count(*) from employee'
:outHash(FLOAT,OUT)=4215716605

-- Session: CLIENT_DB_MAINT_USER[PCCP_USER1]@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-CANARY-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP90.WORLD)))
-- Timestamp: 13:23:57.923
select min(child_number) cn
from v$sql_plan
Where hash_value = :sqlhv;
-- ORA-00942: table or view does not exist
:sqlhv(VARCHAR[10],IN)='4215716605'

-- Session: CLIENT_DB_MAINT_USER@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-ORIOLE-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP86.WORLD)))
-- Timestamp: 13:25:43.689
with ps as
(select 1 as inst_id, sid, serial#, qcsid, qcserial#
from v$px_session
where qcserial# is not null)
Select PS.QCSID as parent_session_sid, PS.QCSERIAL# as parent_session_serial, PS.inst_id as parent_session_instid, rawtohex(s.SADDR) as SADDR, s.SID, s.SERIAL#, s.AUDSID, rawtohex(s.PADDR) as PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, TO_SINGLE_BYTE(s.OSUSER) as OSUSER, s.PROCESS, TO_SINGLE_BYTE(s.MACHINE) as MACHINE, s.PORT, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH, substr(TO_SINGLE_BYTE(s.ACTION), 1, 64) as ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.TOP_LEVEL_CALL#, s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID, s.SQL_TRANSLATION_PROFILE_ID, s.PGA_TUNABLE_MEM, s.SHARD_DDL_STATUS, s.CON_ID, s.EXTERNAL_NAME, s.PLSQL_DEBUGGER_CONNECTED
FROM V$SESSION S, PS
WHERE 1=1
and PS.SID (+) = S.SID AND PS.SERIAL# (+) = S.SERIAL#
order by PROGRAM, OWNERID;

-- Session: CLIENT_DB_MAINT_USER@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-ORIOLE-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP86.WORLD)))
-- Timestamp: 13:25:43.851
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from v$session
where sid = :sid;
:sid(VARCHAR[3],IN)='240'

-- Session: CLIENT_DB_MAINT_USER@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-ORIOLE-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP86.WORLD)))
-- Timestamp: 13:25:43.894
Select *
from v$sql
where 1=1
and sql_id = :sqlid;
:sqlid(VARCHAR[13],IN)='2jnz9d8909cjy'

-- Session: CLIENT_DB_MAINT_USER@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-ORIOLE-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP86.WORLD)))
-- Timestamp: 13:25:44.402
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from v$session
where sid = :sid;
:sid(VARCHAR[3],IN)='240'

-- Session: CLIENT_DB_MAINT_USER@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=VBAL-ORIOLE-MGT.ITSSO.GC.CA)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=DVPCCP86.WORLD)))
-- Timestamp: 13:25:44.443
Select *
from v$sql
where 1=1
and sql_id = :sqlid;
:sqlid(VARCHAR[13],IN)='2jnz9d8909cjy'

4

For what it's worth, you only need all those extra grants if this option is checked. And I can tell you had your Session Browser open when making that Spool SQL. Some of those statements came from there.

image
image764×732 61.2 KB