Top 10 Preferences to Tweak when using SQL Developer

thatjeffsmith SQL Developer 192 Comments

Tell Others About This Story:

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.

You can find the Preferences Dialog under the Tools Menu

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.

Keep a running list of all of your query output

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.

Turn this off. You can then open objects with a double click instead.

Turn this off. You can then open objects with a double click instead.

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.

Tell SQL Developer what database objects you want to see when clicking around the tree.

Tell SQL Developer what database objects you want to see when clicking around the tree.

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)

The contents of this will be executed as each database connection is established.

The contents of this will be executed as each database connection is established.

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.

Related Posts

Tell Others About This Story:

Comments 192

    1. thatjeffsmith Post
      Author

      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.

    2. 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.

    3. 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

  1. 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

    1. thatjeffsmith Post
      Author

      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.

    2. Yes sir..how can it be possible to import xml data into data like importing the data from excel or csv?

    3. Yes sir..how can it be possible to import XML data into tables individually like importing the data from excel or csv?

    4. thatjeffsmith Post
      Author
  2. 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

    1. thatjeffsmith Post
      Author
  3. 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

  4. 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 🙂

    1. thatjeffsmith Post
      Author
    2. Sadly, that takes me back to the previous line at the end of COL2,

      I am running SQL Developer Version 4.1.3.20

    3. thatjeffsmith Post
      Author

      >>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?

    4. 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!

    5. thatjeffsmith Post
      Author
    6. thatjeffsmith Post
      Author

      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

    7. 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!

    8. 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.

  5. 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

    1. thatjeffsmith Post
      Author

      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?

    2. 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.

    3. thatjeffsmith Post
      Author

      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

  6. 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?

    1. thatjeffsmith Post
      Author
    2. 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.

  7. 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

    1. thatjeffsmith Post
      Author
    2. 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

    3. thatjeffsmith Post
      Author
    4. 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

  8. 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

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  9. 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?

    1. thatjeffsmith Post
      Author
    2. 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?

    3. thatjeffsmith Post
      Author
  10. 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.

    1. thatjeffsmith Post
      Author
  11. 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

  12. 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…

    1. thatjeffsmith Post
      Author
  13. 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

    1. thatjeffsmith Post
      Author
  14. > “Ask Tom (and Tom himself) isn’t anything BUT annoying”

    a freudian slip, or just being a little harsh today? 🙂

    1. thatjeffsmith Post
      Author
  15. How can I remove that pesky “Ask Tom” search box on the top right right corner of the screen ? It’s really annoying…

    1. thatjeffsmith Post
      Author

      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.

  16. 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

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  17. 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.

    1. thatjeffsmith Post
      Author

      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…

    2. Well, there are always weirdos like me who could (and would) use this feature if it were made available 🙂 Thanks for the response.

    3. thatjeffsmith Post
      Author

      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 🙂

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *