Run 2 queries at the same time on Oracle SQL Developer?
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.
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.
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.
No, you will need a separate session per query.
@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.
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;
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.