A common question that user’s of Oracle’s SQL Developer ask is “How can I cancel a query in SQL Developer?” Many times, the reason for this is, out of the box, SQL Developer uses a jdbc “thin” driver. This thin driver doesn’t have all of the robust functionality of the “thick” driver built into it.
The solution is to install the OCI “thick” driver for SQL Developer, although this is not a panacea either. Some queries, such as those that do remote calls over database links, are still difficult to cancel at times. Below is an easy, visual guide how to install the OCI “thick” driver for SQL Developer.
Download and unzip the “thick” driver from Oracle
Go here: http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Download the appropriate version of the “Instant Client Package – Basic”
Next, type “Windows Key + R” to get a “Run” dialog box. Type in “C:\” (without the quotes) and hit “OK” to pull up windows explorer.
Inside the C:\ drive, you’ll want to make a new folder.
Name it “oracle_client” (without the quotes).
Go to the instant client you downloaded:
You’ll want to extract the contents of the zip file to the directory you just created.
Add to your your “PATH” environment variable
Next open up “My Computer.” How you do this will vary depending on which version of Windows you have.
Right-click on an empty space somewhere in the “My Computer” window, and a context menu will pop up. Click on “Properties.”
In the window that comes up, click “Advanced System settings”
Under the “Advanced” tab, choose “Environment Variables”
Under “System variables” choose “Path” and click the “Edit…” button.
Don’t modify any of the existing values…we just want to add a new value.
Click on the “New” button and add the path to the directory you “unzipped.” So for me, I downloaded the 11_2 driver, so what I added looks like this:
C:\oracle_client\instantclient_11_2
You’ll want to replace the instantclient_11_2 piece with whatever version of the client you’re using.
Click the “OK” button.
Now click “OK” in your “Environment Variables” window.
Now click “OK” in your “System Properties” window.
Setting up the “thick” driver in SQL Developer
Next, you’ll want to open up SQL Developer.
From there, navigate to Tools -> Preferences.
You’ll want to go to “Database” -> “Advanced” in the left-hand menu bar.
From there, you’ll see a “Use Oracle Client” section. It’s important that this checkbox is checked. Once you’ve ensured this, click on the “Configure” button.
You’ll get a dialog box. You’ll want to make sure “Instant Client” is selected from the drop-down menu and find the directory that the client was unzipped to. Again, for me, that directory was C:\oracle_client\instantclient_11_2, but for you it will likely be different, depending on which version of the client you downloaded.
Once you’ve done that, click on “Test…”
You should hopefully get success messages all around:
If you didn’t get success messages, you’ll need to do some troubleshooting (so make sure everything is pointing to the right directory).
Once you’ve gotten success messages for everything, click the “OK” button in your dialog.
Next, you’ll want to make sure that the “Use OCI/Thick driver” check box is checked.
Click “OK.”
It’ll ask you to reboot SQL Developer.
Once SQL Developer reboots, go to “Help” and click on “About.”
Click the “Properties” tab and search for sqldeveloper.oci.available.
You should see a value of “true” for this property.
If it doesn’t say “true” then again, you’ll need to do some additional troubleshooting here.
But if it does say “true” you’re in good shape!
Cancel Queries!
You should now have the ability to cancel a query in SQL Developer. Again, many database link queries will still refuse to cancel, but you should notice most local queries will cancel if you ask them to.
Thanks for reading!
Free Oracle SQL Tuning Tactical Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
