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.

1 Like

I've no control over what developers do on their environments. I've come to conclude, that in some environments, each developer has its in their own schema.
This synonym seems like a good idea yeah.

Thanks.

Raul

It's been fixed like the others.

Thanks, knowing its fixed, is good enough for me.
Thanks.

Raul