In this post we’re going to talk about Oracle SQL developer “SQL Error 17002 Sqlstate 08006” error on Windows. We’ll talk about the error code and how to fix it fast.
Why Does SQL Error 17002 Sqlstate 08006 Shows Up?
Most of the times it shows up because there is incorrect Misconfiguration or listener isn’t running. Before you’re able to connect with Oracle database, SQL developer must be configured correctly and listener has to be running. The listener handles databases connection, by default the SQL settings does not need listener for connecting to database and so it has to be configured.
SQL Error 17002 Sqlstate 08006
Another possible reason for this SQL error could be database sessions have been terminated by the network firewall or interrupted by another reason. In this case you have to make sure firewall isn’t stopping idle connections.
Connectivity problems between oracle integration and connectivity agent can be the cause of this error also, or when the connectivity agent is disabled. You have to make sure connectivity agent is running. You can check the status under your Dashboards and Agents in oracle integration.
How To Fix SQL Error 17002 Sqlstate 08006
The fixes to this SQL error code is mostly about fixing or configuring the listener. We’ll also share other fixes that will help.
Fix No.1: Ensure listener is running
This can be checked from Windows Service Manager or using Command Prompt. Follow these steps to check the status;
Checking listener status using Windows Service Manager:
- Press Windows + R to open Run Dialogue.
- Type msc in the dialogue box.
- In the list of services, TNS listener is listed, make sure it’s running, the status is shown beside each service. If it’s not running you just need to right-click and click Start.
Checking with command prompt:
Open command prompt, you can do that by typing cmd in windows search, right-click on it and select ‘Run As Administrator’.
- When the command prompt opens, you type this command: C:\>lsnrctl status
- If it’s not running, error will be mentioned multiple times, and you need to start it.
- To start the listener service, you need to type this command: C:\>lsnrctl start
lsnrctl status
Fix No.2: Making sure the listener has the right configurations
- Open Oracle SQL Developer.
- Right click on database name and select properties.
- A window will open and you need to take note of the Hostname and port values under the ‘details’ tab.
- The default port is 1521, but sometimes it can be 1522, it has to be changed.
- Now back to checking hostname, you should type C:\>hostname.
- The hostname will immediately appear below this command and if you’re receiving the SQL error code 17002, in most cases it will be different from the hostname that appeared when you right-click on the database name in the previous step. Remember that I told you to take note of the Hostname, this difference occurs when the computer name is changed. In this situation, the hostname has to be corrected in the connection Window, and the port value also should be changed to 1521 if that’s not the value.
- The next step now is to open tnsnames.ora file and check to confirm that the hostname and port is correct. The full path is; %ORACLE_HOME%/network/admin/tnsnames.ora. if not you need to change the values appropriately.
- Open the Windows Service Manager and Restart OracleserviceXE by right clicking and then click restart.
- Now you should test the connection to see if things are ok. When you’re sure all values are correct, that’s the Port, Hostname, username and password, you should click the test button. If connection show successful it means the problem has been rectified.
- After that you can now click connect and can now browse and query your database.
This fix can be lengthy or seem complex to most users but that’s the way to go about it.
Fix No.3: Restart Oracle TNS listener
As we’ve mentioned in the first fix, you should check whether listener is running or not, so in this case even if it’s running you should restart it. You should follow the same procedure in the first fix. Restarting the listener has work for some, you should give it a try.
Conclusion
So far these are some of the best fixes to SQL Error 17002 Sqlstate 08006, the problem most of the time has to do with listener and you need to ensure that it’s pointing to the correct port and server. Once you can figure this out, you’ll definitely get rid of the problem. Do let us know in the comments if these fixes have solved the SQL error for you, if you also have more ideas to share please drop your comments below for our readers.
