Help - Auto Re-establish the database connection

SQL Navigator
1

Hi Quest,

Can you help here?

I am currently using SQL Nav 6.0.0.1005 and a few months ago my company bought licence for 6.5. but we can use it as it does not have auto Re-establish the database connection when the connect is lost.

in 6.0 version, if the connection is lost, I can simply rerun the query then it would reconnect automatically and return the result. dont know why this feather is missing in higher version of 6.5

May be theres some configurations problem. can you please advise if it’s possible to have the Auto Re-establish the database connection in 6.5 and how? Thanks a lot!

Kind regards,
Joey

2

Hi Joey,

There’s a Reconnect menu item under main menu “Session” group. Could you please try it when your connection is lost? It should work better than running a query to re-establish your database connection.

Thanks,
Vincent

3

Hi vliu,

Thanks for your answer! but I know this option and this is not something we need as this way is too slow.

Is there any settings in 6.5 to do with auto re connect ? please advise… if not then why this function was removed? Please advise!

Kind regards,
Joey

4

Hi Joey,

There’s no settings can help to have this function back. We did not meant to remove this function and it’s possibly affected by some changes we made in the past. We’ll look into this and provide you an update when we find out a way to solve this.

Thanks,
Vincent

5

Hi vliu,

We really wish this issue can be resolved asap as everyone in the company are refusing to use newly purchased 6.5 version caused by this issue.

We are upgrading the system to windows 7 and can’t keep using 5.5 and 6.0… really need to move to the latest version 6.5 or higher. Thanks!

Kind regards,
Joey

6

Hi Joey,

Until we can provide you the fix, in the mean time, could you please try the following work around and let us know if that would work for you.

  • In v6.5 we have the option in the preference window to keep the session alive where you can specify the time interval in minutes in which SQL Navigator will run the select sql to avoid the timeout situation. I have attached the preference window screenshot for your reference.

Hope that by keeping the session alive, you would not need to reestablish the lost connection.

Thanks and regards,
Bruce

nav.jpeg
nav.jpeg787×534 58.3 KB

7

Hi Bruce,

Thank you so much for your help! But unfortunately, this way would only solve the capability issue between All version of SQL Nav and Kaspersky Anti-Hacker function in Windows 7 environment.

Let me explain more deeper about the reason why we need that function back:

While we were using SQL Nav 5.5 there’s no any connection issue to Oracle 10 db. but lately, our company implemented Audit logging and we get SQL Nav upgraded to 6+, this is the time when the problem starts.

With SQL NAV 6+ (from 6.0 -> 6.5) All experiencing below issue when type ‘.’ in the editor:

e.g.

select x from table1 t1
where t1.

Then we highlight the query then run it, will receive the following message:

ORA-02002: error while writing to audit trailORA-00604: error occurred at recursive SQL level 10RA-22922: nonexistent LOB value

only in 6.0 version, we can just highlight the query then rerun it, in other 6 versions we have to click session from menu bar -> reconnect

As we have to run query thousands times a day, so that’s the reason we need the function back in the new 6.5 version.

P.S. the ‘.’ caused connection issue does not exist in 5.5 version. only 6 +

I would image to solve the ‘.’ caused connection issue would not be easy for you guys. but bring the re-connection function back would be earlier for you.

Is it possible to get a quick fix tool to have this function back please… this would make our work and life much much more easier.

It would be great if you know the problem of ‘.’ caused issue. and why it does not happen in 5.5 and only 6+

But in the main time, we wish to have a quick fix for main issue I mentioned in my first post. Thanks a lot for all your help!

Kind regards,
Joey

8

Hi Joey,

We would like to clarify your problem first. From your post we conclude that the problem begins after you type in a dot in a query like shown. Does the dot-lookup show at all? I assume that you go on and complete the query (otherwise it won’t run) and then run it.

The error shown has actually nothing to do with a lost connection. From Oracle manual:

ORA-02002: error while writing to audit trail
Cause: The auditing facility is unable to write to the AUDIT_TRAIL table. If this error occurs, SQL statements that are currently being audited may also fail. This error will occur if the SYSTEM tablespace runs out of disk space.
Action: Add space to the SYSTEM tablespace or delete rows from the AUDIT_TRAIL table. If these operations fail or do not eliminate the problem, shut down and restart Oracle with auditing disabled. This is done by setting the initialization parameter AUDIT_TRAIL to FALSE.

It’s not clear why you think that fixing the auto-reconnect issue will fix that particular error. Rather than relying on the auto-reconnect feature, I would recommend that you talk to your DBA first and find out why audit is causing problems that disrupt your work. 5.5 doesn’t have the problem probably because it issues fewer queries in the background (e.g. code completion). By the way, if you think that code completion or dot lookup trigger the problem, you can try and disable them in Preferences.

Regards,
Roman

9

Hi Joey,

I have checked the auto reconnect behavior in 6.5. We did not find out any difference comparing to 6.0. When connection is lost, we can still re-run the query in code editor to reconnect and have the result back. You may see " ORA-03113: end-of-file on communication channel" error when you first time re-run the query, but you can run it a 2nd time and then the result should return.

Have you tried rerunning the query more than once since your connection was lost? If it’s still not working, what is the error in output or error pane?

Thanks,
Vincent

10

Hi Joey,

Thanks again for your additonal info/explaination on your issues.

We are trying to work it through to identify underlined issue of the lost connection on why the '.' would cause the session disconnected. It is the issue which we would like to address for you.

Vincent and I have reviewed the area reconnect when executing sql, all Nav 6.x (including 6.5) would attempt to reconnect when executing sql statement when current session is lost. We are still not sure yet why the reconnect does work for you.

Much appreciated if you could assist us to provide the following info:

  1. Please let us know if turning off the auto dot look up (as per Roman's note) - Does it help to avoid the ORA error you reported earlier? I have attached the screen shot of the preference window where you can turn off that option.

2.Can you please run the Quest SQL Tracker to capture all sql that SQL Navigator issues The SQL Tracker app can be found in the same Start Programs of SQL Navigator. Please send us the sql log from sql tracker from the time before you start type typing your sql including the dot '.' - Please make sure you have the Auto Dot lookup option ON.

  1. If you can send us the Alert log from your DB (Audit logging) or any info from the Alert log to help us to determine which sql from SQL Navigator might cause the session disconnect.

Thanks and regards,
Bruce

nav.jpeg
nav.jpeg784×531 55.9 KB