Menu Home

SQL Developer: New worksheet, new session?

SQL Developer loading. Yesterday.

Default Behaviour

The out of the box behaviour of SQL Developer is that SQL worksheets share the same connection.

What does that mean?

If you perform a insert, update or delete in one worksheet without issuing a commit, the result(s) can be viewed in another shared worksheet. This can be illustrated by the following example.

After launching SQL Developer and connecting to a database, I start two new SQL worksheets

The next two screen shots show that for both SQL worksheets that the table t is empty

Next using using the first worksheet, an insert into table t operation is performed. No commit has been issued.

Navigating to the second worksheet and querying the table again and the newly inserted row can be seen.

Unshared Connections

It is possible to open a SQL Worksheet with a dedicated database connection. In SQL Developer this is called an unshared connection.

From an existing worksheet the keyboard shortcut on Windows is CTL + SHIFT + N. This will only work from a shared worksheet. It is not possible to open a new unshared worksheet from an existing unshared worksheet.

The following screenshots demonstrates how an unshared connection differs from a shared connection.

Two shared SQL Worksheets: hr and hr~1 are started along with and one unshared: hr (Unshared). In the first worksheet I perform an insert operation which has not been committed.

Switching to the second shared worksheet, the uncommitted record is shown after querying the table.

Moving to the unshared connection and querying the table no row is returned.

The unshared worksheet is working as expected as a new session will not see uncommitted records.

Acknowledgements

Jeff Smith, the Oracle Product manager for SQL Developer has a superb blog where he shares many SQL Developer hints and tips. One of the posts
Opening Unshared Worksheets was the inspiration and source for this post.

Categories: Oracle Oracle Database SQL Developer

oraclefrontovik

Developer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.