Run 2 queries at the same time on Oracle SQL Devel

Question

I need to retrieve quite a bit of data from our oracle DB and to do so I need to run 20+ queries. Is there any way to run more than one query at a time on the same connection?

I tried using / to separate the queries, but that simply opens multiple tabs and queries still run sequentially, although I don't have to start them one by one.

Answer 1

Assuming you like to live dangerously, you can run multiple "threads" from one script using the pragma AUTONOMOUS_TRANSACTION. For example:

DECLARE
   PROCEDURE foo(i IN PLS_INTEGER) AS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO qux
         SELECT * FROM bar
         WHERE baz = i;
      COMMIT;
   EXCEPTION WHEN OTHERS THEN ROLLBACK;
   END;
BEGIN
   foo(1);
   foo(2);
   foo(3);
END;

Answer 2

No, you will need a separate session per query.

Answer 3

So the simplest solution to this was to use SQL Plus that came with the rest of Oracle software. It's a clunky tool, but does what I needed, while I'm free to use SQL Developer for other queries.

Answer 4

Pressing ctrl+shift+N will open a new unshared worksheet that can run queries in parallel. In that case you have to paste a query in each tab and run them manually though, but it is handy when just testing a few queries.

Answer 5

@Tony is correct, each query must run in it's own session to run in parallel. What tool are you using? In PL/SQL Developer, I can open a DB connection, then open multiple sessions within that connection and run several queries in "parallel" - I do have to execute each one manually, but if they each take a long time, perhaps that will get you what you need, or something similar in whatever tool it is you're using.

Answer 6

In SqlDeveloper preferences: Tools > Preferences > Database > Worksheet check the option for New Worksheet to use unshared connction. This will allow you to execute multiple queries at the same time, each in each tab. See a screenshot too.