I tried to reproduce this one last week but was unsuccessful. Can you reproduce it consistently?
Do you have Team Coding enabled?
Do you have any other windows in Toad open (such as Object Palette or other dockable window)?
Also, please post a screen shot of your options at Options->Oracle->Transactions.
Thanks
I cannot reproduce it anymore... But I also upgraded to 14.0.0.519 in the meantime which might resolve the problem.
I have several windows docked and usually a describe one too, but I checked all that and none of the above problems occur. No Team Coding and these are my transactions settings:
Anyway, the case is closed, sorry to bother you.
It's no bother. I didn't do anything (that I'm aware of) to fix it, so it may return. Let me know if it does.
Just happened again in 14.0.51.637.
Tried to switch (from TEST) to the disconnected connection (DEV), got the "reconnect" dialog. Reconnected to DEV. But the edit stayed within TEST (checked with SELECT SYS_CONTEXT('USERENV','DB_NAME') from dual;).
I'll try to create a repeatable test case.
Thanks. I tried it several times this morning but could not reproduce it. For me, every time I got the first reconnect, I did not get a second one. I think that second error that you are getting is the key, but I don't know why you are getting that.
It might also play a role that DEV has a profile's controlled connection idle time limit while TEST has not (and my corporate firewalls must be breaking that). Another factor could be a previous reconnect. I'll spend some time on it in the coming days.
That's how I was testing - a profile timeout on one user but not the other.
I am able to reproduce the issue every time now. Tried right after a reboot too (short time working in TOAD).
BTW, in the meantime (since the initial bug report back in July), I've upgraded the RDBMS from ver. 12.2 to 19.8 and the client on my PC from ver. 18 to 19.8. I presume that means the RDBMS version does not influence the problem.
The steps:
-
Opened two connections TEST (green) and DEV (lime). DEV has PROFILE's idle time limit of 15 min. TEST has not.
-
Pasted the same script into both editor windows:
SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM dual;
SELECT instance_name, host_name FROM v$INSTANCE;
-
Worked on the TEST for 1 hour, so DEV session got disconnected:
Switched to DEV and tried running one of the statements - got ORA-02396:
-
Without reconnecting, switched back to TEST and tried the "Change session" button to switch to DEV:
-
Got the error - pressed "Reconnect":
-
The session reconnected and TOAD showed another dialog:
-
Pressed OK, and the Editor window switched to DEV - all OK but the text on status bar:
-
Run the code in the editor as a script (press F5) and the problem reveals:
-
Running any statement (press F9) fixes the title bar text and the query returns the expected instance name.
-
Running again as a script changes the the title bar text to TEST and the query returns TEST instance again.
Oh, forgot to mention two factors that might have an effect on connections:
#1: sqlnet.ora on both TEST and DEV servers:
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA256, SHA1)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA256, SHA1)
SQLNET.ENCRYPTION_SERVER = required
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.CRYPTO_SEED = ...
SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256)
SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT = required
ADR_BASE = /u01/app/oracle
#2. I'm using the Instant Client rather than full Oracle Client on my PC.
Hi Przemek,
Thanks for the details. I wasn't able to reproduce this (for me, the reconnect on step 5 always works - I never get the 2nd "connection is not connected" dialog), but I haven't set up encryption. I'll give that a try next.
I noticed that you have both window bar and connection bar hidden. When you change from one Editor to the other, do you use the "Window" menu, or switch windows by some other technique?
I still can't reproduce it even with encryption set up, using an instant client, and logging in with a username that contains a dot.
I had an idea though - if you go to Options -> Editor -> Behavior, and check "Display SQL errors in popup dialog", then the first time you get the "connection is not connected" message - instead of it just going to the messages panel at the bottom, you'll get a popup with a "Reconnect" button.
Let me know if that works. Maybe "connection lost" type errors should always popup anyway, so you can reconnect w/o having to go to the main menu.
Also, please zip up and email me your user files folder, and I will try again with your settings. You can find steps to do that, along with my email address, here.
Thanks - see the e-mail with attached settings.
I'm using Ctrl+Tab to switch between windows.
I turned on "Display SQL errors in popup dialog" - so far the behaviour is much worse, i.e. TOAD freezes when the session gets disconnected or shows endlessly the "Statement processing" dialog:
... when trying to cancel:
.. and eventually kill:
The screenshots show small times, but trust me - I waited several minutes waiting to run/cancel/kill on other tests.
Let me test few times more (maybe straight after a reboot) later today/tomorrow.
Oh, forgot to mention that I can recover from the freeze by disconnecting the network = switch off the VPN. Will try that with the "Statement processing" too.
ok. I'll retest when I get your User Settings files. Hopefully that will allow me to reproduce it.
So, the only thing that will cause a timeout is the profile's IDLE_TIME, is that right? Nothing else on the profile or network to timeout?
The m/a sqlnet.ora on the server and the following from TOAD's SQLNET editor:
NAMES.DIRECTORY_PATH=(TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES=(ALL)
LOG_DIRECTORY_CLIENT=c:\temp
LOG_FILE_CLIENT=c:\temp
SQLNET.EXPIRE_TIME=0
TRACE_DIRECTORY_CLIENT=c:\temp
TRACE_FILE_CLIENT=c:\temp
TRACE_FILELEN_CLIENT=100
TRACE_FILENO_CLIENT=3
TRACE_LEVEL_CLIENT=SUPPORT
TRACE_TIMESTAMP_CLIENT=ON
TRACE_UNIQUE_CLIENT=ON
USE_DEDICATED_SERVER=ON
I have the following profile assigned:
CREATE PROFILE user_profile LIMIT
SESSIONS_PER_USER 5
CPU_PER_SESSION 100000 -- 1000 seconds
CPU_PER_CALL 20000 -- 200 seconds
CONNECT_TIME 480 -- 8 hours
IDLE_TIME 30 -- 30 minutes
INACTIVE_ACCOUNT_TIME 180 -- lock the account after six months of inactivity
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
-- COMPOSITE_LIMIT DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 180 -- 180 days
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME 7 -- 7 days
PASSWORD_VERIFY_FUNCTION DEFAULT
;
This gives "ORA-02396: exceeded maximum idle time, please connect again" on DEV.
The rest are database/client defaults and probably corporate firewall inactivity rule (giving "ORA-03135: connection lost contact" on TEST).
BTW, my DEV session just timed-out so tested with the "Display SQL errors in popup dialog" option turned on. No difference - exact same dialog is shown after the reconnect.
ok, thanks for the info. I'll try my profile that way.
By the way, you said.....
...but I don't have the email yet. Just mentioning because it sounds like you've sent it. If they are getting blocked, try putting them in a google drive or similar and send me a link.
I still can't reproduce it (even with your user files folder) but I think I know what's happening now.
Go to Options -> Oracle -> Transactions
and set "On Test/Reconnect" to "Always disconnect and reconnect" ?
(I hope I haven't suggested this already).
If that fixes it, I know what the problem is.
For the record - no luck with "Always disconnect and reconnect".
I spooled the SQL output as you advised and saw that TOAD is checking for the package that I had been working on in the other editor tab. Maybe the second editor tab is causing the second dialog box? It might be the key to replicate the issue...
Yes! I got it.
The key to reproducing it is having a compiled package in the editor that does not get disconnected.
So:
- Start Toad.
- Make Connection1 (which does not time out).
- Open a package in an editor for connection 1. Compile it.
- Make connection2 (which will time out)
- Run any old sql (with F9) in an editor for connection 2.
- Let connection 2 time out and then run its sql again
- A connection lost message goes to the messages tab at the bottom.
- In Connection 1's editor, use the "change session" dropdown to change to Connection 2.
- A connection lost/reconnect dialog appears. Hit Reconnect.
- another disconnected dialog appears (this one w/o a reconnect button).
- now the editor's status bar shows the wrong connection.
