I believe this is normal behavior. Here is an explainer:
Opening a connection with the preference set to "Open a Worksheet on connect"
1) Opens a worksheet as you describe.
2) Then opening an unshared worksheet from that worksheet's tool bar icon, of course, also works.
3) Next, if you close those worksheets but leave the connection open, double clicking on the connection node just
collapses or expands that connection node in the Connection tree without opening a new worksheet.
If, however, you disconnect the connection (an option in the context menu for that node) or all connections (context menu in the owning connections folder or the top level connections node in the Connection tree), then opening the connection (which is literally opening a new JDBC connection to the database) will open a new worksheet.
Thanks for your input. The behaviour you are describing seems logical - however I am not talking about opening the very same connection (a fact I accidentally did not share in my initial description); I open a different connection to a different database.
Everything works well and continuous if you have only one connection (meaning different databases). If I got more connections to different databases the behaviour isn't continuous at least in my opinion. I tried a sequence of opening / closing worksheets and connections:
Say I have defined connections to 4 different databases; a, b, c and d
1.) I open up a connection to database a, a worksheet opens for that connection (expected)
2.) I open up a connection to database b, a worksheet opens for that connection (expected)
3.) I open a new worksheet for database a, a worksheet opens for that connection (expected)
4.) I open a new connection to database c, no worksheet opens (why?)
5.) I close the connection to database a: 2 worksheets for connection a are closed (expected)
6.) I open a new connection to database d: a worksheet opens for that connection (expected)
7.) I open a new connection to database a, a worksheet opens for that connection (expected)
8.) I close all connections to all databases, all worksheets are closed(expected)
9.) I open a connection to database a, a worksheet is opened (expected)
10.) I open another worksheet for database a, a worksheet is opened (expected)
11.) I open a connection to database b, no worksheet (why?)
12.) I open a connection to database c, a worksheet is opened (and why does that happen now?)
13.) I open a connection to database d, a worksheet is opened (same here).
To me it seems, that the first new connection opened after I create a new worksheet doesn't get it's own worksheet by default. The next new connections do get new worksheets. It doesn't matter how many worksheets you additionally open for your connection, if you open one extra the next connection doesn't get one.
If that is indeed expected behaviour or is caused by expected behaviour it is at least very strange expected behaviour
thanks & regards
1 person found this helpful
In that case, I do not see the same puzzling behavior. Here is an image of 4 open connections and 5 worksheets, with the unshared worksheet opened from the second connection:
This image was taken yesterday, and I should have included it in my prior post. Your idea about something having gone wrong with your migrated user settings (migrated through multiple SQL Developer versions) could well be correct.
Anyway, for full disclosure, in the image above all connections are to schemas in an 11g XE database, JDBC thin, JDK 1.8u201 on WIndows 10.
I retried with a clean installation SQLDev 18.4 on Server 2016 - the behaviour is the same.
However - I found something: when I check
"New Worksheet to use unshared connection" (as you seem to have) in the Worksheet settings and the Behaviour is as expected - every new connection creates a new Worksheet.
As for now I am not sure if I want a new Connection for each and every Worksheet though...
Having all connections as unshared might cause your DBA concerns over resource usage, yes, plus you have to remember to commit more often if you want DML done in one connection to be visible in the other(s) for the same schema.
My test was also on 18.4, with no migration of settings, but connections imported from an 18.3 export. I remember due to my comment in another discussion on nested folders (Re: SQLDev 18.3 - Folder lost when exporting connections (Bug) ).
And, no, I do not have "New Worksheet to use unshared connection" checked, so the behavior you see is even more mysterious:
Glen Conway wrote:
Having all connections as unshared might cause your DBA concerns over resource usage
No worries here - I am the DBA (we are talking about dev / test databases; I do not intend to add connections to any production system to my local SQL Developer installation).
plus you have to remember to commit more often if you want DML done in one connection to be visible in the other(s) for the same schema.
Yep, that is something that bothered / bothers me. Normally I use SQL Developer for writing Queries, but in some rare cases I spread DML over multiple worksheets and I do want to keep the atomicity of that transaction. That won't be possible with seprate connections for each worksheet.
I applied all the worksheet settings you have according to your settings screenshot - still the behaviour is the same.
I did run SQL Dev on Windows 7 previously - now on Windows 10 (also on Server 2016).
I asked a Colleague of mine to test the behaviour as well - the described behaviour is the same. Windows 10 running SQL Dev 18.2. Are you using the Version where the JDK is bundled with SQL Developer, or a standalone JDK?
1 person found this helpful
I almost always use a standalone JDK. For those rare times when it is necessary to go back to a very old release of SQL Developer, then I will download the zip with a bundled JDK. As noted above, I currently use JDK 1.8u201 (standalone), and do not see your issue on either 18.4 or 18.2 (just tested that).
thanks for your input. I'll retry with a standalone JDK and see if that makes any difference.
I retried using the blank current SQL Developer (18.4) with a separate JDK (1.8u201). Still the same behaviour. I created a screen video showing the behaviour (.zip file attached - should be a .webm video).
sqldev_worksheet.zip 1.4 MB
1 person found this helpful
Thank you for the video. I tend to avoid the Alt-F10 approach (or either of the 2 possibilities from the main toolbar SQL icon)
to get a shared worksheet, but instead rely on unshared worksheets (Ctrl-Shft-N or the Unshared Worksheet icon on the Worksheet's toolbar)
So my automatic custom blinded me from understanding what you were saying. You are absolutely correct, the behavior SQL Developer exhibits seems to fall a bit short here.
On the other hand, It seems a minor problem to me. If you do not want an unshared worksheet, an alternative to opening a new worksheet on an already open shared connection is to split the existing worksheet. Have you tried that? Just right-click on the worksheet's tab and select Split Horizontally or Split Vertically.
in most cases when I use another (shared) worksheet for the same connection this is when the current worksheet is cluttered with SQL Statements I somehow will need again but need to do some other things. Mostly for debugging etc.
Mostly in that cases I do some dml, then call a stored procedure, then dml, then do some queries, then...after a while I start a new worksheet if need be - and as I do not want to commit my modifications but want to see my modifications in the other worksheet I cannot use an unshared worksheet as I wouldn't see my uncommited data. Then I might want to check with a different test database and create a new connection - and that's where the new Worksheet is missing.
You are absolutely correct - this is a minor nuisance but something that bothered me a little for some time but I never found it that annoying to post about it.
Splitting the Worksheet doesn't exactly help that workflow as the 2 shared worksheets contain different SQL Statements which - at least in my installation - is not the case when I split the Worksheet. It is however something practical I'll keep in mind - thanks for the Tip.
In any case - thanks for your continuing effort in trying to help me .
1 person found this helpful
Obviously you know best how to deal with your specific workflow. The split worksheet approach does indeed duplicate all statements in both worksheets, so that could be confusing. It would be nice if the statements "belonging" to each instance of a split worksheet could have a different highlighting color or style (like Bold or Italic).
With regard to your comment "I somehow will need again", do not forget about another useful feature: View -> SQL History
Thanks for the Tip. With "I somehow will need again" I mean in the current session (like select col1, col2, col3 from tab where <complex where>) for debugging purposes but nothing more. Statements which I need repeatedly mostly end up in the Snippets
I'll leave the Thread open for a while - maybe someone else stumbles upon that behaviour as well and finds it annoying .