Toad World® Forums

Lockout with Oracle saved workspaces

Toad for Oracle
#1

Frequently, I like to save Oracle workspaces in TOAD. Often I have many windows saved so I can recreate an environment. There is a fatal flaw with this process and I have yet to find a reasonable workaround. If one of the connections is down, or unavailable, TOAD stops loading connections at that point - no more windows. I am currently getting an ORA-12505 TNS error for one my connections and I am locked out of my environment for the remaining windows.

The only way I know to workaround this is to edit the TNSNAMES.ORA file and spoof the connection, but that is not an easy task for me as my work computer is locked down tight. I cannot freely edit the TNSNAMES.ORA file.

Any other solutions to this problem?

Gary

#2

This is fixed in the current beta. If one of the connections is unavailable, workspace loading will continue. Plus, it now makes all of the connections first so you don't have to wait for all windows of one connection to load before it gets to the second connection. If you have version 14.0, you can download the beta here: Toad for Oracle 14.1 Beta

1 Like
#3

Upgrading is not an option for me at the moment. I have redefined every tns entry to be one that is valid. This problem STILL persists. There is some corruption somewhere because I still receive the ORA-12505: TNS:listener does not currently know of SID given in connect descriptor ERROR. I cannot overcome it. Every TNS is changed and its still trying to connect to the wrong connection - one that according to the tnsnames.ora file does not exist. Is there anyway to access the saved workspace script files so that I can at least save my scripts?

#4

That info is stored in a file called workspaces.dat, but it's a binary file and you aren't going to be able to get anything helpful out of it with notepad.

Your trick should work as long as:

  • the logon user exists in the "fake" target DBs
  • Your workspace doesn't have any Direct connections (which would bypass tnsnames.ora)
  • you don't have LDAP.ora defined (which could also bypass tnsnames.ora)

Sorry for the stupid question, but are you sure that you edited the correct tnsnames.ora file?
Do you have more than one Oracle client? If so, do you have a TNS_ADMIN environment variable set in Windows so that it's always pointing to the same tnsnames.ora?

Try turning on Spool SQL (database -> Spool SQL -> Spool to screen).
That will show you the SQL and connection attempts that Toad is running. Maybe it is trying to connect somewhere that you don't expect.

1 Like
#5

I just had another thought -

If you are just looking for a particular SQL, and you've run it, then it's probably in your SQL History. Hit F8 to open that window.

#6

Thanks for all the suggestions. I was using the correct tnsnames file. There is only one. Here is how I eventually solved the problem and I'm 99% sure it was not related to any direct connections (any I had would have been OK). Definitely no LDAP.

  1. Spoofed all the TNS connections I needed, and removed all other connections in the tnsnames file.
  2. Removed a few saved connections that were connecting to where I thought the problem might be. One by one I got further in the group of windows by re-entering the passwords.

I could not get past the ORA-12505 error until I did #2. Something got corrupted. I think one of 2 things caused this issue:

  1. I may have re-saved a workspace after VPN was lost on my computer and restored, and something was not quite right.
  2. DBAs were running rolling patches on our servers and server 1 failed over to server 2 and vice versa. The odd thing was that I could always individually connect to the database even though I was getting ORA-12505 for the workspace during the patching.

I still like using workspaces, but I am going to be more mindful of saving specific scripts to disk so they are not only stored in that binary file.

Thanks again.

Gary

#7

I'm glad you got it working. It might be nice to offer a redirect in workspaces - so when a particular connection doesn't work, offer to open those windows in a different connection instead.