Toad World® Forums

Toad plan table issue

Toad 13.2.0.258

I have logged in with user SYSTEM.

I am trying to explain plan on table, that is another schema.
I have opened editor window, and set schema as AETADMIN.

I am trying to explain plan on that very schema.

I place my cursor on top of this statement and press ctrl+e:

select * from aetadmin.view_osaliste_nimed where dokument_id=281;

And I get nothing.

If I spool this to screen, and repeat this process, I get this:

----------------------------------
-- Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=XXXXXXXXXXXXX)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=YYYYYY)))
-- Timestamp: 14:20:05.820
ALTER SESSION SET current_schema = AETADMIN;

----------------------------------
-- Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=XXXXXXXXXXXXX)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=YYYYYY)))
-- Timestamp: 14:20:05.820
explain plan set statement_id='raulk2:013020142005' into TOAD_PLAN_TABLE For
select * from aetadmin.view_osaliste_nimed where dokument_id=281;

----------------------------------
-- Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=XXXXXXXXXXXXX)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=YYYYYY)))
-- Timestamp: 14:20:05.851
ALTER SESSION SET current_schema = SYSTEM;

----------------------------------
-- Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=XXXXXXXXXXXXX)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=YYYYYY)))
-- Timestamp: 14:20:05.851
Select *
From TOAD_PLAN_TABLE
Where statement_id = :STATEMENT_ID
order by id;
:STATEMENT_ID(VARCHAR[19],IN)='raulk2:013020142005'

You can see that, it will insert output into AETADMIN.TOAD_PLAN_TABLE
And then change schema, and query this plan from SYSTEM.TOAD_PLAN_TABLE

Raul

I agree, that the "ALTER SESSION SET current_schema = SYSTEM" should come last...but....

Do you have a TOAD_PLAN_TABLE in each schema?
Why not create one TOAD_PLAN_TABLE with a public synonym?
That would make this problem go away.

I've logged this bug and it will be fixed soon.