ThatJeffSmith

Unshared Worksheets in Oracle SQL Developer 4

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
    1. Main connection
    2. 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:

Turn this on for more connections :)

Turn this on for more connections :)

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.

More worksheets = more connections

More worksheets = more connections

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.