DIctionary views missing from internal SQL when connecting via VPN

When opening Schema browser while connected to a database via VPN, I’m bombarded with ORA-00942 error messages.

Using SQLTracker, I’ve identified the offending SQL as

– Session: SCHEMA_NAME@INSTANCE

– Timestamp: 09:56:26.600

Select t.trigger_name, t.trigger_type, t.triggering_event,

    t.when_clause, t.status enabled, null status, t.owner, -1 object_id, t.trigger_body

from sys. t

where t.table_owner = :own

and t.table_name = :obj;

:own(VARCHAR[16],IN/OUT)=‘SCHEMA_NAME’

:obj(VARCHAR[25],IN/OUT)=‘SOME_TABLE_NAME’

When connected to the database without the VPN, the same SQL reads as follows :

– Session: SCHEMA_NAME@INSTANCE

– Timestamp: 10:01:17.618

Select t.trigger_name, t.trigger_type, t.triggering_event,

    t.when_clause, t.status enabled, o.status, t.owner, o.object_id, t.trigger_body

from sys.DBA_OBJECTS o, sys.DBA_TRIGGERS t

where t.table_owner = :own

and t.table_name = :obj

and o.object_type = ‘TRIGGER’

and o.object_name = t.trigger_name

and o.owner = t.owner;

:own(VARCHAR[16],IN/OUT)=‘SCHEMA_NAME’

:obj(VARCHAR[25],IN/OUT)=‘SOME_TABLE_NAME’

This is connecting to the same schema, same database instance. Attempting to browse objects owned by the connected schema.

Any ideas / suggestions on why this behaviour happens ?

Using Toad for Oracle 12.12.0.39 - Although same behaviour experienced in previous versions as well

Thanks in advance

~N

It looks like our code that determines what data dictionary views that you have privileges on didn’t run. Did you have any error during connection?

Hi John,

Thanks for your reply. During connection, I do get a couple of ‘ORA-03113: end-of-file on communication channel’ messages. In the end the connection does go through though and regular operations work fine.

Is there any way to force TOAD to use a specific set of dictionary views ? Or prevent it from performing the queries on startup ?

Thanks!

Try this to help us figure out when it’s happening.

  1. Start Toad but don’t connect
  2. From the main menu: Database -> Spool SQL -> Spool to Screen
  3. Now make your connection (and hopefully it will give the 3113)
  4. post the spool sql output.
    Thanks

Hi John,

Please see attached file. I could only attach a single file, so note the three sections, each separated by

– ***********************************************************************************************************

– ***********************************************************************************************************

– ** File Name Here
– ***********************************************************************************************************
– ***********************************************************************************************************

  1. RegularConnectionSQL.sql - This is what a regular connection would look like. It connects to an Oracle TAF which would then hand over the connection to one of the RAC instances. All TOAD functions work fine

  2. RegularConnectionSQL_STD.sql - This is a regular connection, directly to node one of the RAC. All TOAD functions work fine.

  3. VPNConnectionSQL_STD.sql - This is a connection via the VPN to node one of the RAC. We experience the problems as detailed above.

It was interesting to note the differences in SQL executed between the various connections.

Thanks for your help

Niel
ConnectionSQL.sql (109 KB)

When we first make a connection, we run a big PL/SQL block that checks permissions and other info about your database so we can write appropriate queries for the duration of your Toad session. That PL/SQL block is having a “ORA-03113: end-of-file on communication channel” on your VPN connection, which is leading to the problems later. Unfortunately, I don’t know exactly what aspect of your VPN is causing this…