Updated Wednesday, July 6, 2016

Setting up a new install of SQL Developer this morning – exciting stuff as we’ll be teasing some v4.1 features at Open World this week – I found myself immediately tweaking the following preferences.

  1. Font – we’ve talked about this a few times, but I make it bigger to make it easier to see in presentation mode
  2. I also change the font itself, Dialog is horrible
  3. Open Object on Single Click – Disable
  4. Grid in checker board or Zebra pattern
  5. NLS DATE Format
  6. Connection Tree Object Type Filtering
  7. PL/SQL Syntax Coloring Scheme
  8. SQL History
  9. Explain Plan – Add Notes Field
  10. PL/SQL Compiler – All, Enable
Make this bigger for other folks in the room - or maybe you're taking screenshots for tech papers, blogs, etc.
Make this bigger for other folks in the room – or maybe you’re taking screenshots for tech papers, blogs, etc.

Bonus tip: if you’re on Windows, make sure you have MSFT’s free ZoomIt program. Makes presentations look ‘whiz bang’ as you can freeze, zoom, draw, and type in your live product demos.

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.
Grid option for alternating backgrounds
Grid option for alternating backgrounds

Enable ‘Grid in checker board or Zebra pattern.’ which gives you query results that look like this –

Grids in SQL Developer with the 'zebra option'
Grids in SQL Developer with the ‘zebra option’
type the search string, we auto-filter the preferences
type the search string, we auto-filter the preferences
SYSDATE is RIGHT NOT, not 'Today', according to the database and will always have the time - even if you don't see it.
SYSDATE is RIGHT NOT, not ‘Today’, according to the database and will always have the time – even if you don’t see it.

New Stuff I’ve Forgotten, and Just Remembered

So as I actually have a physical new computer, my brain was forced to walk this path. For reals. And so I thought I’d update this post to include the extras.

Tree Filters

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.

Notes for Your Execution Plans

Explain Plan Preferences, toggle on OTHER_XML
Explain Plan Preferences, toggle on OTHER_XML

If you want to know if adaptive plans are in play for example, turn this field on.

Editor Color Scheme

The 80's refuse to die.
The 80’s refuse to die.

I’m running a variant of the Twilight scheme.

Bump Up the SQL History

Don't go crazy - each item is saved in an XML file. If you have a really, really big number here, you're asking your machine to carry a big load and you'll notice a longer startup time.
Don’t go crazy – each item is saved in an XML file. If you have a really, really big number here, you’re asking your machine to carry a big load and you’ll notice a longer startup time.

I have mine set to 1500.

PL/SQL Compiler Stuff
If the compiler has ideas for me to make my code better, I want to know about it. This will enable warning messages when you compile your PL/SQL code.

Preferences > Database > PL/SQL Compiler

Find the top item, ‘All.’ Set to ‘ENABLE.’

Then when you compile your code, you’ll see stuff like this.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

15 Comments

  1. Hi Jeff,

    Sometime back I had found a very useful tip on your website which I am unable to locate again.
    when I am in an editor in SQL developer 18.4 and moving through the table name like T_PCP_MODEL_LOCATOR_NEW, the cursor goes through each and every underscore. I remember I had this fixed in one of the earlier versions of SQL developer through some preferences settings, but not able to locate that again. Please help!

  2. Hi, Jeff.

    Can I ask: Where is the location of the preferences.xml file? – (prefferably on Windows machines)?

    Thanks.

  3. Vic Cordiner Reply

    Hi Jeff,

    I am trying to standardise our SQL Dev environment for all our developers, and was thinking, is there a way to set these in the sqldelevoper.conf or other config file?

    Thanks
    Vic

    • they’re stored in a preferences.xml file – you can standardize that and push out on a corp ‘install’

  4. Thanks a lot for your response, Jeff!
    Yes, I have downloaded the latest beta version of 4.1 and I can confirm that everything is working just fine!

    Thanks again!
    Oleg.

  5. Hi,

    first of all I’m finding really useful all of your tips. Thanks for sharing!

    On thing I noticed is that if I use the Zebra pattern for the grid the colour set for the background on null values is not used any more. So I was using red for null values and after I enabled the Zebra grid all the columns are displayed following the Zebra pattern. No red any more.

    Not sure if that is the expected behaviour but I thought I would mention it.

    Thanks and regards

    • you can do one or the other, but not both – that’s a bug though, not your fault, it’s ours

    • Hi there Jeff,

      Thanks for sharing your configs, I was doing pretty much the same thing for any new installation of SQL developer 😉

      Just on the note of Zebra Pattern/NULL highlights, is there any plan to fix this bug (as you’ve called it yourself) in the future? As it’s been 5 months since your original reply, I though it would be worth to ask you about it, cheers!

      Oleg.

  6. NLS-Date format: a 2-digit year is usually a bad option. You will loose the century when you export the data. I think you are old enough to remember Y2K 🙂

    One more thing is to disable the use of ORA_ROWSCN. We get a lot of complaints from users that they get error messages, even when they simply view table data (Open a table from the navigator – data tab – error), mostly on heavily used tables.

    And as you say changing these settings is the first thing you do on first startup. It would be a good feature if we could export non default settings like we can export connections or reports. Then I could send new users a simple profile file and save a lot of time for support.

    Regards
    Marcus

    • One more thing is to disable the use of ORA_ROWSCN. We get a lot of complaints from users that they get error messages, even when they simply view table data (Open a table from the navigator – data tab – error), mostly on heavily used tables.

      What kinds of errors?

    • “What kinds of errors?”
      ORA-00904: “ORA_ROWSCN”: invalid identifier

      I just reproduced it with 3.2.20.09 against 11.2.0.2.

Reply To Mauricio Cancel Reply