Die! Or How to Cancel Queries in Oracle SQL Developer

thatjeffsmith SQL Developer 61 Comments

Tell Others About This Story:

I’m not sure why, but it appears many of you wish you could take back your SQL queries. It’s like that one time you posted that picture on Facebook and immediately (3 days later) you realized it wasn’t a great career move.

Maybe that data you asked for wasn’t going to be that helpful if it took 3 hours for it to come back. This reminds me of the weather forecasting models can that could be perfect but would take years to run…anyway I’m digressing way too early already.

You run query.

You want to cancel the query.

In fact, you want it to DIE, DIE, DIE!

Let me help you kill yoursel…your query.

The Mechanics

You open connection. You submit query. We send it to the database for you. A few moments later, you change your mind and hit the ‘Cancel’ button. We let the database know you changed your mind. Maybe the database hears you and maybe it cancels the query.

It’s the ‘maybe’ part that frustrates people.

Or maybe the client sees the query as cancelled but you still see the server process (PID) running on the server. This is also alarming to folks. PMON is responsible for doing the clean-up there. That’s kind of out-of-scope for this post, but I’m going to say it’s out of the client’s (SQL Developer’s) hands. So let’s get back to the actual query being cancelled.

3 Ways To 'Make' This Query Stop

3 Ways To ‘Make’ This Query Stop

1. Use the Results Panel Cancel Button

Hitting this button will send the cancel request to the database. The button will activate once something’s running in the worksheet.

Cancel this query,  please

Cancel this query, please

2. Using the Task Progress Panel

You might have stuff running in a few different worksheets and editors. You can monitor the progress of all of them using this panel. Open it from the View menu.

See your stuff, cancel your stuff

See your stuff, cancel your stuff

3. Open Monitor Sessions and Kill the Session

Find your SID, select the row, right-click, and ‘Kill Session.’ I’ve heard about folks that will just telnet into the server and issue an OS ‘kill XXXX’ to make their session go away, but I don’t recommend this as anything but a last-resort option. Let the database handle the session if at all possible, otherwise be ‘OK’ with the results.

But Why Won’t My Cancel Work?

The database is coded to listen for cancel requests in various places. Apparently for JDBC connections, it’s not listening in as many places as you may like. But, there’s a ‘fix!’

OCI offers a few advantages over straight JDBC connections.

OCI offers a few advantages over straight JDBC connections.

Enable this, start a new connection, and if you have a compatible Oracle Client on your machine, we’ll use the Oracle Call Interface (OCI) via the ocijdbc driver. Now, one of the few advantages here is that there are MORE ‘listen’ points in the database code for query cancellation requests. I’ve suggested this route for many of our users, and it seems to have done the trick for all of them.

Also, don’t forget to use ‘unshared worksheets‘ if you’re about to run a hairy query – it will run on a dedicated connection and let you use the rest of the tool without holding you up whilst you wait for it to finish.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 61

  1. None of these work. Luckily I use VPN. So the only thing that works is to disconnect the VPN and then connect back. It is a hassle and it is frustrating, but someone with this version of my PC, my trusted PL/SQL developer does not work

    SQL Developer also takes up too much memory and does not allow for keyboard shortcuts.

    1. thatjeffsmith Post
      Author

      What doesn’t work?

      How much is too much memory? You can’t compare jvm memory use to a native exe on Windows…is it too slow? Maybe the jvm needs tuned if sqldev is unresponsive.

      How do you mean no KB shortcuts? We have tons of them.

  2. I’ve tried over and over again to kill queries with SQL Developer and have never been able to get it to work, would love to get it working if possible! I have the settings above enabled, but so far nothing.

    What happens for me is that the query appears to cancel (message shows ‘task cancelled in x seconds’) but it actually keeps running in the background (I can tell because if I try this with a short running query eventually it starts working again).

    If I try to rerun anything (in any worksheet) in the meantime, the connection appears busy. If I try to disconnect, that doesn’t work either (sometimes nothing happens, something I get a connection busy abort/retry message). In fact, once I’ve tried to cancel a query, the only way to run another query is to end task on SQL Developer and reopen it again.

    1. thatjeffsmith Post
      Author
      1. SQL Dev 4.1.0, I don’t have Oracle client installed. This happens with both 11g and 12c databases. These are all select queries, I’ve never tried cancelling a DML operation.

        thanks!

        1. thatjeffsmith Post
          Author
          1. Apologies Jeff, I thought I already had the full Oracle client, but then I remembered I since got a new machine and hadn’t needed to install the client. I installed the instant client (the thick connections option was already enabled) but it didn’t seem to make any difference. Appreciate the help!

  3. Jeff,
    My Advanced Preferences show a ‘Display XML Value in Grid’ instead of ‘Use OCI/Thick Driver’ .
    Help About shows version 4.1.0.19 Build Main-19.07.
    Check for Updates shows no new updates available.

    1. thatjeffsmith Post
      Author
  4. Another thing, that make me mad — working with “Files” tab.
    I keep my working files on SSD drive, but SQL Developer force me to reach a Zen, when it try to read directory structure of project.

    1. thatjeffsmith Post
      Author
  5. Wow, I know you are trying to be sarcastic about how you handled the ‘I don’t know why you want to cancel the SQL’ help ‘Kill yoursel…sql’ things. But being a product manager, very immature. here is an enlightenment, people want kill their SQL for the same reasons multi billion dollar corps and their product managers can’t copy a product properly after several releases and keep pushing bug fixes every two weeks on an improperly implemented/copied functionality developed by another small team like TOAD developers. There are 10 more tools that are all developed by Oracle to monitor the DB but yet they can all work so clueless of each other just like your second paragraph says, left hand doesn’t know what right hand does. what’s the point of buying products from same company if they don’t integrate with each other well.

    1. thatjeffsmith Post
      Author

      Sure, I could do what I do here w/o my personality, sarcasm, and poor sense of humour. But I’d quickly get bored. And I’d slowly stop writing so much. And people would quit reading my mindless drivel.

      Also, I never claimed to be mature.

      I’ve only ever wanted to help people. And if my sarcasm prevented that, I’m truly sorry.

      Also, we’ve never wanted to copy a product. I’d have never taken this job if that was the goal. I’ve been on both teams, 4 years here, 10 years on TOAD. Copying is a pretty serious charge.

      I know of only one monitoring solution here at Oracle, that’s Enterprise Manager.

      We push releases as required. And free of charge.

      If you’re happier somewhere else, don’t let me stop you from being there.

      Now all that being said – were you able to get your queries to cancel in SQL Developer?

  6. HI Jeff,
    Your articles on SQL Dev are really good. We have a situation where our support team connects to Oracle 11gR2 RAC databases using SQL dev 3.0.4 (Windows terminals). We (App DBA’s) suggested to use Oracle 11gR2 client (OCI Thick drivers) instead of using JDBC Thin drivers (JDBC URL’s). From you articles we know OCI Thick drivers should be preferred way to connect to databases. We have been challenged by Tech Arch’s that JDBC thin drivers should be used only and not Oracle client. Could you please provide advantages or point to one of Oracle note, of using OCI Thick drivers (Oracle client) over OJDBC drivers (JDBC url’s).

    regards
    Raj

    1. thatjeffsmith Post
      Author

      I’d rather help you get up to v4.1.2 and Java 8 first!

      But

      In terms of OCI, there are 2 advantages over JDBC,

      1. better query cancellation support in the database
      2. ability for a user to reset their password while not connected

      The only other reasons you’d want to use a Thick connection is when your connection protocol requires it, i.e. isn’t supported in the Thin JDBC driver.

      1. Ok Thanks.
        Given the choice, should we use Oracle OCI Thick (tnsnames using Oracle 11gR2 client) connections or OJDBC thin (JDBC URL’s) connections?

        regards
        Raj

        1. thatjeffsmith Post
          Author

          You don’t need any client at all to connect with tnsnames, jdbc thin supports using a tnsnames file.

          What you need to do is tell me your requirements, then I can make a suggestion. If the #1 and #2 things i listed previously aren’t an issue, then it’s MUCH easier to just use straight JDBC.

          1. HI Jeff,
            We have installed SQL dev 4.1.2 with Java 8. I am trying to set Oracle client in Advanced config and upon test, I am getting error:
            Testing testing native OCI library load … Failed:
            Error loading the native OCI library
            The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
            the expected native library directory C:\app\product\11.2.0\client_2\bin is present and precedes any other client installations.
            java.library.path = C:\Program Files\Java\jdk1.8.0_65\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\app\product\11.2.0\client_2\bin;C:\app\product\11.2.0\client_1\bin;C:\app\lstevens_admin\product\11.2.0\client_1;C:\ProgramData\Oracle\Java\javapath;C:\Perl\site\bin;C:\Perl\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\TortoiseSVN\bin;.

            My PATH setting at OS level is:
            C:\app\product\11.2.0\client_2\BIN>path
            PATH=C:\app\product\11.2.0\client_2\bin;C:\app\product\11.2.0\client_1\bin;C:\ap
            p\lstevens_admin\product\11.2.0\client_1;C:\ProgramData\Oracle\Java\javapath;C:\
            Perl\site\bin;C:\Perl\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbe
            m;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\TortoiseSVN\bin

            I did see your post on your SQL Dev 4 blog, but it doesnt apply to my case. Oracle Client is set and preceeds any other client installation in path settings. How do I fix this error now?

            regards
            Raj

  7. Need to configure ODBC connection to Progress Database using SQL Developer. How should I do this configuration in SQL Developer?

    1. thatjeffsmith Post
      Author
  8. Jeff,

    I’ve been successfully using the OCI client for some time now without any issues. Over the past weekend while browsing with Chrome I decided to install the Java browser client which successfully installed. Now all of my attempts to connect using SQL Developer fail with the following type of error:

    SQL Developer ocijdbc12.dll The specified procedure could not be found Vendor code 0

    (I removed the full path to the DLL). I’m running 4.0.2.15 in 64 bit mode on Windows 7 Enterprise. I also checked my path statement and the proper BIN directory that contains this DLL is there.

    1. thatjeffsmith Post
      Author

      The first thing that comes to mind is that when you updated the java browser client, it put its path in the ENV variable first, and SQLDev is looking there for the DLL instead of in your client folder. Make sure the PATH points to your client install path FIRST.

      1. Jeff,

        I just looked at the SQL Developer About screen’s Properties and noticed that java.library.path had the following entries which looks like the PATH but the first 3 entries are not from my PATH settings.

        C:\app\sqldeveloper_4.0.2.15.21\sqldeveloper\bin;
        C:\Windows\Sun\Java\bin;
        C:\Windows\system32;
        C:\Windows;
        C:\app\Client\Jim.McCusker\product\11.2.0\client_64;
        C:\app\Client\Jim.McCusker\product\11.2.0\client_64\bin;
        C:\app\Client\Jim.McCusker\product\11.2.0\client_32;
        C:\app\Client\Jim.McCusker\product\11.2.0\client_32\bin;

        1. I figured it out. I had both the 11.2.0 and 12.1.0 client installed and for whatever reason my path was pointing to the 11.2.0 client. I’m now pointed to the 12.1.0. client and all is working again. Weird.

  9. Sorry for so many replies 🙁
    After some testing to see when it happens, this only seems to occur if I try to cancel the task while it is still executing, and not just fetching rows. The only way I was able to get in this case was with a large query that will always take multiple minutes to run.
    Run statement. Then go to another (quick) statement and run that. Both tasks then show in task progress. Cancel the large one, but note that on the server it will keep running and that the next task will still be waiting.
    Canceling a row fetch seems to work with and without OCI for me.

    Any ideas of what I might be doing wrong?

    1. thatjeffsmith Post
      Author

      I don’t think you’re doing anything wrong. It might just come back to ‘all we can do is ask the database to cancel its task’ – we can’t force a query cancellation. Normally the OCI Thick stuff remedies this situation, and it sounds like you’ve successfully set this up.

      You never say what version of SQLDev you’re running, 3.2 or 4.0?

      1. Version 4.0.0.13.80 now, but when I wrote the first comment I was using the preview. The upgrade from preview to release didn’t handle the OCI settings properly until OCI was disabled and re-enabled, but there was no difference in behavior for the complex query.

      2. Sorry to pester, just wondering if you have any ideas about anything else I can try for this.

        Using SQL*Plus I am able to cancel queries, and it seems to work properly, but I am still unable to successfully cancel any from SQLDeveloper. This is most obvious when trying to cancel a long running query, and then trying to quit the application. It tells you the connection is busy and hangs.
        I have upgraded to 4.0.1.14.48 for Mac with Instant Client Version 11.2.0.3.0 (64-bit) from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
        I haven’t tried using the 32-bit client, but I am using 64-bit SQLDeveloper (confirmed in Activity Monitor) so I didn’t think it was worth trying.

        1. thatjeffsmith Post
          Author

          Have you confirmed that you have the Instant Client correctly configured with SQL Developer 4? With SQL Developer running, go to the Help > About and do a search in the properties panel for ‘oci’ – it should say that sqldeveloper.oci.available is ‘true’

          Make sure you’ve enabled the thick connection preference.

          If you’ve done all of this and you’re still unable to cancel queries, I would open an SR with MOS.

  10. On my machine, SQLDeveloper still fails to properly cancel a task while set to use the OCI. OCI/Thick is selected in my preferences, but there doesn’t seem to be a way to verify that it is using OCI instead of JDBC. Any tips?

    I downloaded the basic instant client for Mac (64 bit matching SQLDeveloper and version 11.2.0.3.0 matching SELECT * FROM PRODUCT_COMPONENT_VERSION). I added the path for the client to my DYLD path, and SQLDeveloper finds that and allows me to specify the path to the client (without complaining that it isn’t in the DYLD path) but the test button in the configuration does nothing with or without anything in the path.

    1. Just saw your other post on OCI, and it does show as true in about->properties and I can reset passwords. The term “available” makes me wonder if it actually is using OCI or just knows it can.

  11. Can you please implement .cancel for Autotrace Action?
    I bet it is just not implemented as I was not able to cancel autotrace action.

    I did try OCI driver and it does NOT help (the sqlplus from that ora_home is SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 10 10:17:39 2014)

    Cancelling the same queries works fine from sqlplus and PLSQL Developer.

    1. thatjeffsmith Post
      Author

      Cancel is there for Autotrace.

      Just ran select * from all_source a, all_source b; through AutoTrace, the AutoTrace task bar pops up – I hit cancel…the query cancels, and then the AFTER info is gathered and the autotrace output appears.

      1. No problem, full step by step:
        1) select max(rownum) from all_source a, all_source b, all_source c, all_source d;
        2) hit autotrace
        3) hit cancel
        expected result 3.1) UI shows the query is cancelled
        expected result 3.2) the query is really cancelled at the DB side (no query in active sessions)

        actual result 3.1) UI does look like the query is cancelled. For instance, the progress bar is gone and the UI allows to launch new query
        actual result 3.2) the query is NOT cancelled at the database. Any attempt to launch new query _waits_ until previous autotrace finishes.
        4) immediately after canceling autotrace try to execute “select * from dual”
        expected result 4) the query _immediately_ returns dummy=x
        actual result 4) “select * from dual” hangs forever

        It looks like the UI just does not propagate cancel when using autotrace.
        The same test works perfectly fine when using plain execute statement command, so NO excuse for “lack of listen points” here.

        I do not mind if “cancel” will cancel autotrace completely and SQLDeveloper will not show statistics of partial executions if that reduces time-to-marked of the particular fix.
        The ability to cancel long query is of top priority.

        It would be great if SQLDeveloper could show statistics of partial execution (i.e. the query is cancelled immediately, and the statistics still calculated).

        1. thatjeffsmith Post
          Author
        2. thatjeffsmith Post
          Author
          1. Technically speaking it might wait till autotrace query completes.
            However, the main point here is even 10 minutes is close to forever when you want it to die.

            Typically I have to kill sqldeveloper from the task manager if I unexpectedly launch a long query via autotrace.
            I do heavily use the unshared connection feature, but I often use ‘base connection’ by accident.

  12. Jeff,

    I’m running SQL Developer on a Windows Server 2008 Terminal Server which has the 64 bit 11.2 client installed. When I try to configure for the OCI/Thick driver (which works great on my laptop, btw) I get the following error:

    oracle.jdbc.driver.T2CConnection.getLibraryVersionNumber()I

    Vendor code 0

    Any idea what’s going on?

    Thanks,

    –Jim

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          I remember now, SQL Dev 4 is shipping with an 11.2.0.3 JDBC driver, are you running a 11.2.0.3 client?

          We’ll ship production with a 12 driver, so you’ll need to eventually upgrade the client to a 12 client as well.

          1. Yes, it’s 11.2.0.3 client. Should up upgrade to the 12 drivers now or wait? Still don’t understand why I’m getting this error. Running same client on my laptop and works great.

    1. thatjeffsmith Post
      Author

      Are you using Network Alias or Connect Identifier in your TNS defined connections? I believe the Connect Identifier will pull in OCI even if you don’t have ‘OCI Thick’ enabled in the preferences…it’s a sqlnet thing and the thin jdbc driver doesn’t support it.

  13. I just tried using the OCI driver on a 64-bit AMD MS Terminal server and got this error: “c:\app\….\ocijdbc11.dll: Can’t load IA 32-bit .dll on a AMD 64-bit platform”.

    Is there any way to get around this? I was able to use this option on my local desktop but would like to also have it available on our VM sessions.

    Thanks,

    –Jim

    1. thatjeffsmith Post
      Author

      You’ll need a 64 bit Oracle client. If you already have one, make sure it’s listed first in your PATH, so SQL Developer will find and load it first over your 32 bit Home. Or, try running SQL Developer in 32 bit mode…

        1. thatjeffsmith Post
          Author

          I’d argue it works today. If you want the OCI thick connectivity, you need to make sure the right client is accessible. That’s not just us btw. I’ve been advising clients for YEARS on how to setup Winders to make it such that their applications find the right ‘Oracle Home.’

          Could we add a property for you to specify the client/home? Yeah, and we’re talking about it a bit internally…

          1. I argue it does not work. And I am not using OCI. I have both 32 and 64 clients installed, 32 first in path, and sqldev 64bits, and windows 64bits.

            > how to setup Winders
            wait, my applications are finding the right home, which is 32bits for most of them (default). And the 64bits applications (like Toad 12.1) as well: they look in registry not in path.

            For SQLDEV4, I found the workaround :

            AddVMOption -Djava.library.path=c:\oracle\product\11.2.0\client_64\bin

          2. thatjeffsmith Post
            Author
  14. I just spent about hour with the setup, I wasn’t able to find any complete instructions.
    You need to download Oracle Instant Client Basic Lite, extract the content to some folder and set the location to environment variable PATH (as the first value in PATH).
    Also you need to replace ojdbc6.jar in [SQLDeveloper_HOME]\jdbc\lib\ with ojdbc6.jar from the Instant Client.

    1. thatjeffsmith Post
      Author

      The Oracle Client, Instant or otherwise, does come documented with installation instructions. You should not have to replace the ojdbc6.jar file.

      I simply installed 64 bit Windows client for WIN7, and toggled the preference.

      With Instant Client, there’s no installer, so extract and add to PATH so it can be found.

      1. I don’t want to install Oracle Client 11g because I’m using Oracle Client 10g and I dont want to face any issue created by installing other version of Oracle Client.

        If I don’t replace the ojdbc6.jar I’m getting error when trying to establish connection in SQL Developer:

        An error was encountered performing the requested operation:

        oracle.jdbc.driver.T2CConnection.t2cCreateState([BI[BI[BI[BI[BI[BI[BISI[S[B[BZ[J)I

        Vendor code 0

        1. thatjeffsmith Post
          Author

          For what it’s worth – and for other’s who will be reading this discussion – you can install multiple Oracle Clients on a single machine. So you could have 11 and 10 and 9, and set it up so that your applications use the correct ones. It basically runs off of the PATH, but there are other tricks you can employ.

          If you’re using 11g database, you should be using a 11g client – that’s a best practice, but I understand if that doesn’t work for you.

      2. Jeff,
        We had to do the same thing, copy the ojdbc6.jar from instantclient_11_2 into the jdbc folder of sqldeveloper to get rid of the error as well. I’m not seeing another way around this.

        1. thatjeffsmith Post
          Author

Leave a Reply

Your email address will not be published. Required fields are marked *