Updated June 2015, current for version 4.1
There are too many options in our every day lives. We love asking for more options, but I often find myself being overwhelmed by the number of questions I have to answer before I can accomplish very mundane tasks. Do you want your latte ‘skinny’ or your cappuccino ‘wet?’ Waffle House customers are probably familiar with ‘capped, covered, smothered, & covered’ routine. Do you want a receipt with that?
So what’s the sweet spot between the Spartan-like Notepad, where we have no options, and the current lineup of Republican Presidential candidates, where we have lots of options that don’t do anything? — OK, that was a political joke, and I should refrain from doing these as I am not a comedian.
When it comes to software, you want something that pretty much works out-of-the-box. Just starting it up and using it shouldn’t require several hours of digging through options or preferences to get it to do what you want it to do. I think we’re pretty close to this with SQL Developer. I can’t think of any ‘deal breaker’ option that would put a user down the wrong road right out of the gate. Here’s an example of something I would see as ‘bad’:
Preferences – Database – Advanced – Autocommit: if this were set to ‘True’ out of the box, I would most likely freak out and remove the product immediately. But, some crazy folks out there might think this is a good idea and demand they have this level of control. These people are also probably from an evil parallel dimension where this is necessary to prevent an obnoxious level of object locking, but that’s a different story.
So most things are good out of the box. But.
What options SHOULD you be aware of?
Here are ten that I think deserve your attention. If you need help navigating to any of these preferences, simply start typing their names into the search box. DO NOT HIT ENTER, this will close the preferences dialog.
1. Code Editor – Fonts
You’re going to be typing a LOT. Be sure to set a font that’s pleasing to the eye. I’m a fan of fixed width fonts that support Unicode. There are thousands of font out there to choose from. One to check out are the Droid fonts from Google.
2. Database – Licensing
As much as we would like for everyone to be running Enterprise edition with ALL the database pack goodies, many people are running lower licensed editions of the database. You can disable the Tuning and Diagnostic Packs in SQL Developer to avoid accidentally using and licensing these features in the database. I recommend disabling the packs at the database level to protect yourself, but if that’s not possible then at least do it in SQL Developer. The nice part is you can do so at the connection level rather than neutering SQL Developer completely.
3. Database – Worksheet – SQL History Limit
The number of statements you want saved is defaulted to 100. I think this is probably on the low side. Bump it up to 500 if you want a few days more worth of queries in your ‘backup.’
4. Database – Worksheet – Show Query Results in new tabs
Wouldn’t it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.
5. Debugger – Start Debugging Option
Set this to ‘Step Over.’ This allows you to start and run a debug session sans breakpoints. If you leave it at the default, starting a debug session will appear to ‘not work.’ This is because SQL Developer will enter debug mode and run until it finds a breakpoint, breaking condition, or exception. If you have none, it will execute to the end of your program. The ‘Step Over’ option will tell SQL Developer to stop execution at line 1 auto-magically.
6. External Editors
Spend a few minutes and setup your default editors for your JPG, XLS, XML, and PDF files. Please. This will make working with BLOBs much more pleasant. A post all about that here…
7. Shortcut Keys
So here’s a trick: sort by ‘Category’ and scroll down to the ‘Other’ category. I’ve found some of the best keyboard shortcuts are ‘undocumented.’ You’ll find a lot of the fun ones here. My particular favorite is ‘SQL History: Replace with Next.’ Of course if you do not like any of these shortcuts, you can customize them to your liking. Just mind the conflicts!
8. Open Object on Single Click
Good for n00bs, probably annoying to experienced users.
9. Hiding Database Object Types from your Connection Trees
You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy.
10. Connection Script Startup
Don’t care to toggle on DBMS_OUTPUT? Want your script page results in groups of 25? Setup your connection startup script (LOGIN.SQL)
Is that it?
Hardly. I could do an entire hour on just configuring your application preferences. But this is a Top 10 list, so it should give you an idea of what you can control via preferences.
If you need a bonus, check out this post on setting up the automatic code insight behavior. Watch the movie at the bottom of the post for insight on the preferences that affect that feature.
221 Comments
Thanks for the top 10 pref tips. How do you export/save these settings now for use on another server/db?
Thanks.
Copy over your AppData folder for sqldev.
Or this basically…
Windows – located in the C:\Users\$USERNAME$\AppData\Roaming\sqldeveloper…
Version 4.0 is in a 1.0.0.0.0 subdirectory
Version 4.1 is in a 4.1.0 subdirectory
Mac and *Nix – located in the $HOME/.sqldeveloper/…
Version 4.0 is in a 4.0.0 subdirectory
Version 4.1 is in a 4.1.0 subdirectory
Fun stuff – Windows usually hides the AppData folder – tell Explorer you want to see the hidden folders. On Mac and *NIX, dot(.) directories are hidden by default, so use the ls -a command to see it.
Jeff, the only thing I am finding there is the product.conf file. Which only has the path to Java JDK and everything else is commented out. Preferences must be elsewhere.
Confused.
Ah, found it …
Navigate to the following location,
Step 1: C:\Documents and Settings\\Application Data\SQL Developer
Step 2: C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX
Step 3: Copy the product-preferences.xml in the location below,
C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX\o.sqldeveloper.XX.X.X.XX.XX
Step 4: Copy the connections.xml in the location below,
C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX\o.jdeveloper.db.connection.XX.X.X.X.XX.XX.XX
When I do a select in SQL developer (MySQL database), it creates a transaction and the table Is locked. Is there any way to avoid this lock. Am fine to only query the database to view the data.
Thanks
SJ
Is there a way to remove/adjust the list of previous locations/folders in the Open File Dialog box?
How to import XML file into SQL developer as a user not as DBA?
Import XML…as what? Do you mean read XML and load it as individual records to a table, like what we do for CSV or Excel? We don’t support that.
Yes sir..how can it be possible to import xml data into data like importing the data from excel or csv?
Yes sir..how can it be possible to import XML data into tables individually like importing the data from excel or csv?
No GUI solution for that here, you’ll have to write some code. Did you check the XML db docs?
How can i import XML file into SQL developer?
Hello Jeff —
Thank you for the good information. I am currently moving from Oracle SQL Developer to PL/SQL Developer 11.0.6.
Every time I open PL/SQL Developer, it immediately prompts for a database connection. Is there a way to stop this from opening by default?
Thank you,
Joshua
I don’t help people with PL/SQL Developer.
Why are you moving away from SQL Developer?
I am using Sql developer to Oracle 11g & Oracle 12c databases. I have it connecting to IBM MVS Databases , and am utilising the JDBC drivers. However, I have noticed that the EXPLAIN PLAN button is greyed out. I have moved the Oracle JDBC to be the first in the list for Tools\Preferences\Third Party JDBC drivers, but it is still greyed out. What should I do please to have this work with Oracle 12c & 11g databases.
Regards Kevin
Is there a way to get SQL Developer to delete previous whole words using a keyboard shortcut like CTRL+Backspace but NOT delete past the left margin?
Example:
SELECT COL1,
COL2,
COL3 <– my cursor would be here
FROM SYS.DUAL
I want to press ctrl+backspace to delete COL3 then press ctrl+backspace again to delete the whitespace preceding COL3 but NOT delete back to the next line placing my cursor after the comma following COL2.
I have tried many different things and cannot find a setting that allows this. I hate that when I try to just delete the whitespace, my cursor ends up on the line before where I have to press enter and then press backsapce a few times to clear out the whitespace.
You would be my hero if you can explain how to do this 🙂
There is supposed to be 7 spaces before COL2 and COL3.
I think this makes me your hero, I think. If I understood you correctly…
Sadly, that takes me back to the previous line at the end of COL2,
I am running SQL Developer Version 4.1.3.20
>>to delete the whitespace preceding COL3
i press it once, deletes ‘COL3’
i press it again, it takes out the 7 spaces after ‘COL2’.
Where exactly do you want it to delete to?
In many editors that I use and have used for development, ctrl+backspace is kind of a dual purpose. It deletes whole words and leading whitespace.
I would like to be able to use that same functionality in SQL Developer. I would settle for deleting the leading whitespace (excluding the newline character) before COL3 and having the cursor stop on the same line that COL3 was on but stop at the very left margin). I may just have to retrain my brain to go for.
I appreciate you taking the time to look at this!
so you don’t want to have to hit ctrl+backspace 3x to get to the previous word (and past the 7 spaces), you want to get there in 2 passes?
I want it to remove the 7 spaces before COL3 and *not* go back up to the previous line.
ahhhh, NOW i get it
the closest i could get was to use ‘delete current line’, but that will put the cursor on the next line, as the entire line is blammo
I started recording a video to show you what I was wanting…then I realized I couldn’t post it 🙂
I think it is just a matter of retraining my brain to use a different key combo to do this.
Thanks again for looking at this and the help. This was a good article and I was so thankful for the Navigation Filters step you showed that allowed me to clean out stuff that we don’t use!
Try and see if “Shift + delete” works for you.
The difference I see is the next line shifts up, so you will have to press enter after the edited column name.
Hi,
Many times support people are running update/delete sql in production without where clause and causing issues.
Is there any way in oracle SQL developer to restrict or check for statements without where clause and throw an error instead of sending for execution to server.
any plugin for this or any other better way to handle it will be appreciated
thanks
Dinesh
If they did it once, accident. If they did it again, training. If they did it a 3rd time, new job/lose production access.
Not ONLY do they have to write the update/delete with a missing predicate, they also have to totally ignore the ‘106 rows updated’ feedback, AND then they additionally have to COMMIT the transaction.
They don’t have AUTOCOMMIT enabled by any chance do they?
Even if this happens once in a year, impact is huge
so we want to check If there is any way in oracle SQL developer to restrict or check for statements without where clause and throw an error instead of sending for execution to server.
surely…prevent access to production from folks that don’t know what they’re doing, or create an application that does the delete/update for them, or create before update/delete triggers
Is there a way to change the numbers of rows that are looked at by the engine when it does an initial test of the IMPORT insert on sql developer?
you mean BEFORE you get to the wizard? no, i don’t think so. is 100 too much?
I’d like to do more, maybe 1000 or 5000 to test the data, so that i don’t go thru 50 columns and then find out it errors out.
Hi Jeff,
Is there a feature in SQL Developer which prompts a warning message if more than x number of rows are about to be updated or blocks updates or deletes.
Regards,
Som
How would we know?
You can always run a query to see how many rows will be affected first, or you can always do a ROLLBACK.
I was looking for a feature similar to “Safe Mode Update” that is available in advanced query tool.
I do understand that it is redundant and requires firing a count query but since its configurable , its up to the user to figure out if they need it or not.
A Safe Update Mode prevents accidental updating / deleting of more rows than intended. If more than one row is updated or deleted you will be asked whether you wish the update/delete to proceed or not. This option can be switched off or configured.
http://www.querytool.com/help/366.htm
Yeah, I don’t think we’re going to do that.
Hi
I am also looking for similar plugin or feature in oracle SQL developer where max rows update/delete can be configured or check if where clause is used or not, throw an error if where clause is not used
Hi Jeffs,
pl/sql developer 8.0 consuming more of CPU Is there a way to reduce the CPU utlization of pl/sql developer?.
Regards,
Max
Sure thing, just don’t use it. I hear Oracle SQL Developer is pretty good though.
Ha! Funny response, Jeffs. 🙂 I wonder if Max thought so too?
The world may never know. #HowManyLicksToGetToTheCenterOfATootsieRollTootsiePop
I noticed that with 4.1+ when running a procedure a default value of 1 has starting showing up for number fields. Is there any way to turn this off and leave it as null as it was and still is for other types such as varchar2?
side effect from fixing another bug
we have this back to the expected behavior for our next update – thanks for the heads up!
I was excited when I saw the 4.1.3 update had been released, but it still has the same bug. Any idea on when the default of 1 for a number field bug will be fixed?
version 4.2, sometime this year – sorry i can’t be more specific than that. we WILL do an Early Adopter/Beta this Spring though
Hi,
Is there a way to disable the commit command from running with sql developer? For example, a script has a commit command, once the user run this script, it won’t run because commit command is blocked/disabled.
Thanks in advance.
ummm, no
if you don’t want someone doing stuff, strip their privs
thank you for the advice. more power
Great answers, very helpful already. Using Oracle SQL Developer 4.1.1.19, Looking for an option in Oracle SQL Developer to ‘ding’ when a long-running query completes – similar to MS SQL Mgmt Studio option of sound notification on query completion (helpful when multi-tasking with other apps on other monitors). Does such exist in OSD?
Thanks
How can I set a “name” to the Results tabs (not Query Result n)?
How can I set a bookmark of table in a specify schema?
Sometimes my table is in Another users…
I have been used Toad eversince. I would like to see SQL Developer tool displaying the no. of rows returned with the result set like Toad. How?
See today’s post, but the answer is, it does show that, you just need to know where to look.
Hi Jeff,
I am new to SQL Developer. i have a question. When i want to see the SQL of a table. it shows script with out the Drop table statement. Is there a way to add or drop the script options as in TOAD. I want Drop statement included in SQL.
Thanks in advance.
Aqeel
Kinda, see today’s post.
> “Ask Tom (and Tom himself) isn’t anything BUT annoying”
a freudian slip, or just being a little harsh today? 🙂
This is what happens when I try to use double-negatives before coffee! Doh!
How can I remove that pesky “Ask Tom” search box on the top right right corner of the screen ? It’s really annoying…
Upgrade to version 4.0 – we killed that. I agree, you have your Web Browser to do internet searching.
BTW, Ask Tom (and Tom himself) isn’t anything BUT annoying 🙂
Ok, I’m an idiot – I meant to say, Ask Tom and Tom himself are NOT annoying. #BadJeff
I think you can also go into the preferences and find an extension for that search mechanism that you can disable.
But seriously, upgrade. Version 3.2 is almost 2 years old now.
Thanks for your great articles!
A small wish from my side:
Being able to save the preferences without closing the preferences window.
Why didn’t sql developer don’t save records and running long time with out showing any errors. I was trying to add a new record from data view
Save records and running long time w/o showing any errors? I’m not sure what this means. Can you provide an example of what you’re doing/seeing?
when we are closing windows the last stored table values are not seen when we again log in in sql
Are you committing your pending changes?
Jeff, is there a way to change the font in which the query results are displayed (i.e., NOT the font of the code editor)? I’m not seeing that option in Preferences, but then I’m very new to this product.
The font preference controls both the editor and the grids. We’ve considered splitting that out to two preferences, but I’m wondering why someone would choose different ones…
Well, there are always weirdos like me who could (and would) use this feature if it were made available 🙂 Thanks for the response.
Just to be sure – you know you can change the grid font now? It just also changes the code fonts…is there a font that you would like for data that would be drastically different than for the editor?
Part of keeping it to one setting is keeping the number of preferences down, to keep things simple, a la KISS – but I don’t want to restrain folks unnecessarily either.
Also, you’re not a weirdo 🙂
how i customize my local oracle sql developer table columns when i click on column name it shows textbox?
I’m sorry Rufat, I’m not sure what you mean by customizing table columns?