My alternate title for this post was How to get the DBAs’ attention with lots and lots of connections in her database. But I decided to go with substance over style instead.
Anyways, let’s talk about connections in SQL Developer for a bit – you know you need the refresher!
- By default, connecting to a database in SQL Developer opens 2 connections
- Main connection
- Insight connection for figuring out what text is a table and what columns to show on a pick-list, etc.
- Do something and commit in one worksheet, it commits across the session, OR
- Create a table in the tree, it will commit your worksheet(s)
- An un-shared worksheet gives you a dedicated connection
Got it?
So in general, for every connection open in SQL Developer, you’ll have about 2 sessions going in your database. Unless you start using this new preference..
In version 4 you have the preference to make all worksheets BY DEFAULT run on a dedicated connection. So open 5 worksheets, you’ll have 5 new connections. COMMIT in one won’t COMMIT in another. DDL in the tree won’t affect your worksheet connections.
Here’s how to make that happen:
It’s even documented nicely in the help, just press F1 on that preferences page if you want to know what it does:
New Worksheet to use unshared connection: If this option is checked, a separate unshared connection to the database is used for each new SQL Worksheet window that connects to a given database. If this option is not checked, the existing connection to the database is used for subsequent SQL Worksheet windows that connect to the database.
And I can see for myself that it’s working.
If I connect and open a worksheet and open another worksheet, I have more than 2 connections now.
A Gentle Reminder
If you use this preference, also use your good sense. If you open 15 worksheets, don’t blame me or the tool when your DBA decides that she doesn’t want you in her database anymore.
A Demo, in GIF Format
22 Comments
Hi Jeff,
Following my question on your demo, by the way thanks for it.
How can I run a report in unshared connection? , sometimes I execute 1 or 2 reports at the same time and it happens the first one was slow and it blocks the second one.
Is there a way I can enable always use unshared connections for SQL Worksheet or reports?
Kinda – for worksheets, but not for reports.
Hi, this is very interesting. I consider myself a “newbie” to Oracle SQL Developer. If anyone happens to see this question I would appreciate the help. I am working in a company where multiple employees are using the same login to the same database schema. And the critical database is being called inside the queries by being hard coded in with the table@database name.
Even though I am only doing select queries from a “worksheet/database” tab, when I close the program I am ask if I want to rollback, Abort, commit changes.
***** What should I choose in order to not affect anyone else’s work?
I have not made any database changes myself but I have been concerned that using a shared user name could mean that I am rolling back someone else’s work.
***** I have also been wondering if I should be using the tab with the database (cylinder) symbol or the worksheet symbol (little guy)?
***** And depending on which on if it should be shared or not shared?
Thank you!
Truly,
Renae
if your managers saw my answer, i’d suggest to them that every user get their own login, but I’ll answer your question instead 🙂
If you do an INSERT, UPDATE, DELETE – you start a transaction. Your transaction isn’t complete until you do a COMMIT or ROLLBACK. So when you leave your session, we’re reminding you to go finish what you started.
Now.
If you happen to query over a database link – the database assumes there’s a transaction there. So when we go to check for transactions on your sessions, the database will tell us ‘Yes’ if you queried over a db_link – even if you didn’t do a INSERT, UPDATE, or DELETE.
If I were you, I’d say ‘OK’ to commit. You’re just committing things you’ve done in YOUR session – not your co-workers. But, anything you COMMIT will then be observable by your co-workers.
Hope that helps!
A followup question on this.
Why is it a bad thing for the DBA if a user is creating many connections? I couldn’t find anything immediate online.
Each connection represents one or more processes on the server. If the system becomes taxed to the point that no more processes are available, a DBA might see that you have 15 tasked while you’re co-workers have 2.
I would reply that maybe you are 7x more productive than your co-workers.
I would also be sure to close them as soon as I’m done with them so as to not ever get on the DBA’s radar.
Hi,
Thanks for this post. I’ve been a massive fan of the “unshared” connection for some time. I work in data warehousing so often my work requires me to run one query/process and while it’s running open an unshared connection and start developing on something else, swapping between the two while waiting for long running operations. today I learned that I can make this a little easier!
When I open a saved .sql file, is there a way to change it to use an unshared connection? At the moment I have multiple names for the same connection (in the connections window) so I can achieve the same result, but it’s not ideal.
If it makes any difference, I’m using (workplace doesn’t allow me to install/update anything myself).
Version 4.0.1.14
Build MAIN-14.48
I would also appreciate an answer to the above question – how to open a saved SQL file in a new unshared window?
Thank you for the great information.
You can’t.
You have two options.
Open Unshared Worksheet and then:
I’m really glad these old posts are still around. I just learned the trick of setting the preference of using an unshared connection by default. And in another old post I learned I could tear off windows and float them on my other monitor (I wish I could tear off the whole Document Group though!)
The entire doc group…interesting. That’s a feature we get via Netbeans if you’re interested.
It’s crazy how ‘fast’ time moves, it seems like just yesterday that version 4 was bleeding edge. I wrote that post in late 2013!
Our Systems have been set up RADIUS Authentificaiton for Security on Oracle 11.1 / 11.2 / 10.2 Version.
On Server Terminal, Client SQL*Plus, and Others Tools using SQL*Net OCI Connection work well through RADIUS Authencation.
But Only SQL Developer occurred ORA-1017 Error, So We do the test as following.
Our Environment :
DB Server : 11.1.0.7.12
Client Version : 11.2.0.3
SQL Developer Version : 4.0.1.14 (Java Version 1.7.0_51)
1) Environement Setting => Database => Advanced => Check ‘use Oracle Client’ and ‘OCI/Thick Driver use” and restart and reconnect => Failed
2) Environement Setting => Database => Worksheet => Check ‘Unshared SQL Worksheet’ and restart and reconnect => Failed
3) Connection Windows => User Defined JDBC URL as following using thin
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.254.177.111)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = DCOR)))
3) Connection Windows => User Defined JDBC URL as following using oci
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.254.177.111)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = DCOR)))
Result of the Test :
In Connection Windows Test Button click display success but connect display ORA-1017.
As you mentioned,
“â– By default, connecting to a database in SQL Developer opens 2 connections
1.Main connection
2.Insight connection for figuring out what text is a table and what columns to show on a pick-list, etc.”
Could you provide us the workaround for RADIUS using OTP conneciton ?
Best Regards.
In connections tab, you can right click and “reconnect” to database but it does not reconnect and unshared worksheets. Is there some way to reconnect unshared worksheets short cut-n-paste whole worksheet into another worksheet?
Not yet, but I’m working on getting that into version 4.NEXT, so stay tuned.
Hi,
Has this been addressed? I couldn’t find a way to reconnect the unshared workspaces in the latest version (4.1.3) considering the above reply is 2 years old.
Thanks,
Mor
Nope. The beauty of unshared worksheets comes with the warts of reconnect on the main connection not applying to the 1 or more unshared ones that might be around.
Please consider adding this – I am working with a laptop on many DBs with many unshared connections in parallel.
It seems a fundamental request to be able to connect my laptop to the docking station the next day and reconnect all existing worksheets to continue as if nothing happened.
Thanks.
Hi Jeff,
I second the notion that this functionality is *really needed* for a professional grade software.
Can you possibly comment what thecurrent outlook is in this regard?
Thank you!
Robert
Does this something a new in sqldeveloper 4.0? I am able to see this in 3.2.20.09 itself. please confirm.
Unshared worksheets aren’t new. The ability to make a worksheet unshared by DEFAULT, however, is new.
I use this feature since it was hidden inside “ctrl+shift+n” shortcut. It rocks.
Can you explain why “Unshared SQL Worksheet” button is available _only_ in the very first worksheet (it does not work in unshared ones).
PS. “SQL Workshet (Alt+F10)” is broken since it asks connection name each time you call it. It even does not remember the last few choices.
I have that logged – to be able to spawn an unshared from an unshared.
If you want a new worksheet w/o finding the ‘original’, just right-click on the connection in the tree -> Worksheet