Thank you very much for your update on this issue. SQL navigator should not cause any problem with Oracle Networking connections.
- Generally If the service/listener is in Shared server mode then there will be a parameter in Tnsnames.ora file called (SERVER=SHARED) this should match the parameter in the listener. ora file on the database server.
For Eg :
Assume that the below entry is your database entry in your tnsnames.ora file.
(ADDRESS = (PROTOCOL = TCP)(HOST = meldevitaw01.melquest.dev.mel.au.qsft)(PORT = 1522))
(SERVER = SHARED) This should be there.
(SERVICE_NAME = I102UT16)
If the above mentioned parameter is (SERVER=DEDICATED) then change it to SHARED on your client machine .This is the Oracle requirement. Other wise when you try to make a connection from your client machine, it requests for a dedicated connection and the listener refuses the connection, because the listener is running in shared server mode.
If the listener is running in shared server mode then make sure from your DBA that there is no limit for the number of concurrent connections. If there is a limit then the listener may not accept any more than the specific number of connections, until the established connections are served by database server.
Here I will explain in brief how shared server configuration differs from dedicated server process >>>>
A database server that is configured to allow many user processes to share few server processes. User processes connect to the dispatcher who directs them to a queue from which an idle shared server process picks a request. This implies that a few server processes can server many clients. Contrast this with dedicated server. The request and response queues and other parts that are found in the pga (Program Global Area) as dedicated server are found in the sga (System Global Area) when the server is a shared server. In a shared server environment, a session is not bound to a single server process, rather, multiple processes can serve the session, although there is never more than one process for a server.
Dispatchers: A process that enables many clients to connect to the same server without the need for a dedicated server process for each client. A dispatcher handles and directs multiple incoming network session request to shared server processes.
There are a number of potential issues with the shared server architecture that DBAs should be aware of before even considering implementing this feature.
Long Running SQL
First of all, the shared server architecture is particularly sensitive to inefficient SQL. Poorly tuned SQL statements can easily take many minutes or even hours to complete. This is a problem even with dedicated sessions, of course, but the issue is much more severe in a shared server environment. This is because by definition there are a finite number of shared servers available—as specified by the parameter max_shared_servers. Therefore, if frequently-executed SQL statements take a very long time to complete, then it is very easy to get into a situation where all of the shared servers are busy running those statements—and PMON is not able to spawn any more servers because max_shared_servers has been reached. This situation essentially results in a system-wide “hang” in which many user sessions are not able to do anything.
Shared Server Aborts
Another type of issue that can occur with shared servers is that in some cases a shared server process can completely abort. When this happens, an ORA-00600  error will be listed in the alert log and a trace file will be generated. PMON will then restart the aborted shared server process automatically.
This issue can occur when a shared server completes executing a SQL statement and tries to write the results of that statement to a virtual circuit—but that circuit has been updated (or “dirtied”) by a dispatcher. In other words the sequence of events is this: A shared server picks up a SQL statement from a virtual circuit and begins executing it. Then a dispatcher places a new message onto that same virtual circuit. When the shared server completes its SQL statement and tries to update the virtual circuit with the results of that statement, the problem occurs. More detailed information on this issue can be found in Metalink Doc ID 106607.1.
In any case, the bottom line on this issue is that the shared server architecture is designed to be used with “serial” applications; i.e., applications that force the user to wait until their current statement has completed before executing a subsequent statement. Therefore, applications that do not enforce such serial access certainly have the potential to encounter the shared server abort issue.
Service Handler Blocked Status
The final type of shared server issue in which all of the service handlers in an environment get into a “blocked” state. In a shared server environment, dispatchers “register” with a listener by establishing service handlers to that listener. Evidently, when the service handlers are under a relatively heavy load, those service handlers can sometimes get into a “blocked” state—such that those service handlers will not accept any new connections to the database. When that happens, users receive errors (usually ORA-12537) when they try to establish new connections to the database. Existing connections are apparently not affected, however. The issue generally seems to last for a few minutes at a time, before appearing to resolve itself.
The following information on this issue is from Oracle Support:
What these symptoms indicate, is the Service Handlers registered with the TNS Listener for the Oracle instance are going into a ‘blocked’ status based on the number service request handled since the last SERVICE_UPDATE from the instance’s PMON process.
This certainly appears to be a type of “threshold” issue; i.e. the service handlers stop accepting requests when the load on them reaches a certain level. As such, it may be possible to reconfigure the listener (or instance) to allow the service handlers to operate under a heavier load in order to increase this threshold.
Please confirm with your DBA that there are no issues with Shared serer configuration.
When ever you get a TNS Listener error, please type in tnsping dbname (service name) in command line and verify whether you get a response or not.IF you did not get a response then definelty some thing wrong with your lietener process.Either it need a reload or restart.