10 Things You Probably Didn’t Know About Oracle SQL Developer

thatjeffsmith SQL Developer 42 Comments

Tell Others About This Story:

You think you know Oracle SQL Developer. And you probably know it better than the average user, especially if you’re a subscriber to this blog πŸ˜‰ But I’m betting there’s a few key things you’ve overlooked. We’re going to fix that today!

Skip Ahead to the Slideshare Version

10. We’re not the new kids on the block anymore.

From 2005 to today, man how time flies!

From 2005 to today, man how time flies!

Actually, we can’t sing either, but I digress. What started out as Project Raptor in 2005 has become one of the most downloaded resources on OTN, enjoying somewhere around 2,500,000 regular users. The number of folks downloading it grows by double-digit percentage points, year-over-year.

We don’t like to brag (lying, of course we like to brag, we’re Oracle), but after 8 years, we have saved our customers many millions of dollars in software licenses and maintenance. And that will continue for many years to come.

9. We’re based on the JDeveloper framework.

And Netbeans now too.

Heh, no one’s perfect πŸ˜‰ Yes, we like to kid our JDev cousins, but they do give us some nice things. In version 4, we’re running on their latest 12c framework – that’s how you get your fancy floating windows, an updated look-and-feel, improved search and replace, etc.

We DO return the favor though. If you’re in JDeveloper doing database stuff, that’s largely from us. So we build SQL Developer off of JDeveloper and then provide extensions back to JDev. It’s a very fun and interesting relationship.

I’ll be working with one of the JDev Product Managers soon to do more outreach to our Java developers to help them get more out of the database widgets inside of JDev. Should be fun stuff.

8. We’re not JUST for developers.

Yeah, we do DBA stuff too.

Yeah, we do DBA stuff too.

There’s an entire DBA panel in the tool that almost doubles the functionality of the product. With interfaces to RMAN, Data Pump, security, storage, and parameter management, the SQL Developer user can quickly perform their administrative tasks without having to leave the tool.

And we’re looking to add more toys here, so stay tuned!

7. We’re installed WITH the database.

That’s right, go double-check your $ORACLE_HOME directory. Now, the software version of SQL Developer shipped with the database is only updated as new major versions of Oracle Database are released. With Database 12c, you’ll find version 3.2.20.09 of SQL Developer. This version already has a good bit of 12c specific feature exploits available, including:

  • Multitenant stuff
  • Redaction
  • SQL Translation Framework
  • Identity Columns

6. We can connect to SQL Server, Sybase, DB2, and other ‘weird’ platforms.

You can query and browse these cough databases. But the reason we provide this feature isn’t because we’re trying to be a multi-RDBMS query tool. No, we’re trying to help you migrate from these platforms TO Oracle Database.

Everything you need to migrate your application and database (OK, mostly everything) is to be found in SQL Developer.

5. A complete data modeling solution is built right-in.

Oh, pretty pictures! I can do that!

Oh, pretty pictures! I can do that!

Oracle SQL Developer Data Modeler is a separate data modeling tool. But we also include it INSIDE of SQL Developer. So you can build a relational model, engineer that to a 12c physical model, and then generate your DDL scripts to push out to the database. Then tab over to your worksheet and start playing with your new objects in the database – all without leaving SQL Developer.

4. Most users only use 10% of the tool.

Ok, I’m cheating with this one. Actually most users only avail themselves of about 10% of ANY software product. Now the tricky part is that while there’s some overlap, everyone’s ‘10%’ sweet spot is different.

Why do you need to realize this? Because for every percentage point you deviate from the 10% (and I mean going up, not down), makes you that much more productive and valuable to your organization. I like money and I want you to make more. Read this blog, see the ‘cool stuff’, use it, and then teach your organization what you’ve learned and show them how to do more. Good things will happen. I could name names, but I’ll let them identify themselves if they so please πŸ™‚

3. Just because we’re ‘free’ doesn’t mean you don’t get support.

Remember how we’re included with the database? Yeah, your database license gives you your license to use SQL Developer. Now, if you also happen to have a valid support contract with My Oracle Support (MOS) for your database, you also get access to opening Service Requests (SRs) on SQL Developer.

We have a dedicated SQL Developer support team for both SQL Developer and SQL Developer Data Modeler. Don’t be shy, let them know how they can help you.

2. We use SQL Developer to build SQL Developer, and large parts of other things in the database.

We’re not just some Java heads that are throwing screens up to handle PL/SQL package interfaces. The developers on this team come from our real world tuning/performance teams, our consulting groups, and other places where real database work and experience are used to make SQL Developer the best little database IDE in Texas…and Hong Kong, Sydney, Belfast, Dubai, Mexico City, and just about anywhere else you can imagine.

Other Database product teams like Times Ten, Spatial & Graph, Data Miner, etc. build their own SQL Developer extensions to make their products more accessible to the SQL Developer user. We’re working with another internal team to bring you guys an interface to one of the most powerful SQL tuning diagnostic and analysis tools I’ve ever seen – and I’ve seen a LOT – so expect more good things.

1. We’re more than ‘good enough.’

I’m frequently asked if SQL Developer is ‘good enough’ to use ‘for real.’ I think I know what you mean, is SQL Developer really good enough to use as the ‘go to’ Oracle database tool?

My answer is ‘Yes,’ but you could probably care less about what I have to say. I can tell you that I have worked with many dozens of companies and government organizations to deploy SQL Developer as their corporate standard database tool. This has been for groups of 10, 100, and 1500 users. The biggest surprise to come about from these exercises is that the users are not only as good off as they were before the switch, but that they’re even happier. The organizations are happier too – everyone is using the same tools, updates are free, and the change often surfaces really, really weird processes that had been built around old tools and workflows.

It goes without saying, but I’ll say it anyway. Oracle SQL Developer is used by the good folks at Oracle Corp. If you work here, you can go to our MyDesktop software inventory, and pull up SQL Developer and Modeler, and have it put on your machine. As new employees are brought in via mergers and acquisitions, they get to use SQL Developer too. We eat our own dog food. We drink our own Kool-Aid. When it tastes bad, we know it right away. We fix it with purpose, because we know y’all out there will benefit.

SlideShare Version

Tell Others About This Story:

Comments 42

  1. Thank you Sir. to use crystal viewer in oracle Database do we need to pay any license cost to Oracle. My crystal software is already licensed. Did oracle support Crystal report software.

    1. thatjeffsmith Post
      Author
  2. Hi friends in our office we are using Oracle EBS. I use to query through Oracle SQL developer and design reports in Crystal report and connected to user by Crystal viewer. To day our IT manager blocked the SQL developer access and informed me that SQL developer is Expensive paid version we dont have licence.
    Dear Friends please share me SQL developer is Free version or paid version, and also share me can i connect crystal viewer with oracle DB for report purpose.

    1. thatjeffsmith Post
      Author
  3. Unfortunately it still has the worst connection management of any serious database tool I’ve ever used.

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,

    Excellent post. Now that I know SQL developer can be used for DBA tasks too, I’m a little worried! I have few users who use it this tool. Will they require any extra authetication to execute DBA tasks ? We generally provide them a read only user to run select queries.

    1. thatjeffsmith Post
      Author
  5. I am not able to view procedures of other users in SQL developer but in toad it shows up . After doing some research I found
    SQL developer only shows objects from all_objects .
    I have access to dba_objects and dba_source and not to all_objects and source how to make sqldeveloper discover these objects using dba_objects instead of all_objects. Is there some kind of setting or hack !

    select * from all_objects
    where owner in( ‘XXX’)
    and object_type in ( ‘PROCEDURE’ )
    No rows returned

    select * from dba_objects
    where owner in( ‘XXX’)
    and object_type in ( ‘PROCEDURE’ )
    All procedures returned

    1. thatjeffsmith Post
      Author
  6. Except for the fonts problem…
    I have tried any solution found on the internet and fonts are display inaccurately.
    I like to set Consolas 11. I have it SQL Server, I have it in TOAD….. I set it in SQL Developer and I get a font that looks like 8…. if I set it to say, 14 or 15, I get a bigger font, yes, but blurry.
    Where are you going to fix this? I’m using version 3.2.20.09

    1. thatjeffsmith Post
      Author
  7. Hi,
    I am using SQL developer since many years and I am quite happy with it, except for the inabilities in the data modeler. It is quite tedious to document attributes in the comments; it is a lot of click you need to do, I would prefer a table based view where I can move down like in (yes) Powerdesigner.
    A thing I am missing totally in Oracle however is the inability of documenting relationships, for me the key information to understand any data model.

    1. thatjeffsmith Post
      Author

      There is table view – but it’s for reviewing only, not editing.

      You are able to document relationships – you can have the relationship name shown in the diagram. Each relationship also has notes and comments available for more documentation. What exactly are you looking for?

  8. Hi,
    Nice Article provided ,and am using oracle SQL developer For My Travel WebSite around 9k customers visit my site.here my issue is after Few hours my Database is going Down some One has to Restart it again and again it became daily process for us.i checked with Database Connections We made 30k still it is going so what the sloution from ur side ,thanks in advance.

    1. thatjeffsmith Post
      Author

      Check the alert log – it will tell you WHY the database is going down every night. If you don’t know this, then there’s know way to know how to fix your problem.

  9. Hi Jeff

    APPS is the super schema for Oracle E-Business suite, and the development is at ease when a single schema is used, else you have to register multiple components with the Oracle E-Business suite environments and the mapping could be a real challenging factor for ill trained DBAs (especially over Linux environments)
    I’m totally agreeing with you, instead of changing the tool, it is always better to change the way we approach the development.
    Still, I think I have the maximum liberty to forward my concerns (who knows many others feel the same? πŸ˜‰ )

    1. thatjeffsmith Post
      Author
  10. Oh Jeff! I am so sorry for the broken sentence! My intention was never to pick an argument. Please check my blog, I did a comparison of Toad and SQL developer 4 as soon as it was released in beta!
    Instead I was just saying “I know you know better (about why the object libraries were not referred” within the context of our discussion.

    If my comment was offensive, I am truly sorry for it, as I didn’t mean it!

    regards,

    1. thatjeffsmith Post
      Author

      Sorry, poor choice of words. I meant to say that I had to agree with you that it was odd that we could resovle the object on a Desc but not a Ctrl-Click. That will be fixed, so it’s more robust.

      I’m determined to enhance the desc feature as well. I have a solution in mind, just have to draft developers to do it πŸ™‚

      1. Hello Jeff

        Thank you for visiting my blog. I am working on a new post, discussing the features of your latest release.

        I’ll try to explain you a work situation first and then come to my actual question. I asked one of my team members to alter a table (which is already in use with more than few forms/procedures) with an additional column. My bad, the developer just renamed the table

        The one primary concern while using SQL Developer is the accessibility of objects to a developer as soon as a connection is established and poorly constructed user privileges (personally I have given “scott” the DBA rights many times so that I won’t have to logout and login back to do certain activities (Test environments!) ) could cause serious unexpected scenarios…

        What am trying to say is, Should the connection node only establish the connection and the schema browsing should be an independent action (which is available by right clicking the connection!)? Thus, temporarily hiding the objects until the user really wants to browse through using the schema browser? How about an independent button with the toolbar for the schema browser? πŸ™‚

        You see, am trying hard, still so used to certain features of Quest Toad!

        regards,

        1. thatjeffsmith Post
          Author

          So what you’re saying is, please don’t invite the user to see objects they have access to? I reckon you don’t have the Schema Browser set to auto-open for new connections in your other tool then πŸ™‚

          Schema browsing doesn’t happen until you actually expand that tree and any nodes though, right? You’re driven by the UI to a worksheet.

          poorly constructed user privileges (personally I have given β€œscott” the DBA rights many times so that I won’t have to logout and login back to do certain activities (Test environments!) ) could cause serious unexpected scenarios

          That’s a pretty hard problem to overcome. Any curious user will get themselves into trouble, yes? And in a way, you’re inviting them to. If you want them to do only do what they need to do and require them to know how to do it, I would only give them SQL*Plus.

          1. Hello jeff

            Happy weekends! I saw your comment, but couldn’t respond immediately.

            Now, I do agree, a user must have access to his objects, at the same time, as I told you earlier, being a development lead I have to make sure that none of my juniors are doing certain actions which they are not entrusted upon.

            For example, 100% our developments are done using the default “APPS” user for EBS, which has access to millions of objects. I know and do understand the importance of having a custom top and other requirements to make sure that the standard objects are NOT being manipulated.

            However a majority of the firms don’t really stick to those custom development recommendations and JUST let the developers to deal with “apps” account itself.

            Here, a conscious act to open up the schema browser somehow reduces the chances of manipulation, unless intended from my prospective.

            You said something about restricting the users to SQL*Plus, brings back some nice experience with a developer candidate few years back. I asked this female candidate to create a view and provided her a laptop with local database and after few minutes, she came back to me asking “YOU DON’T HAVE Toad!”

            She said, she can’t create a view without Toad and advised me why I should/must have Toad before walking away.

            To be quite frank I never used the “Windows” component with the “other” software πŸ˜‰ as you referred it!

            Nice day Jeff πŸ™‚

          2. thatjeffsmith Post
            Author

            I’m a little surprised – you give your developers the APPS account? Why not have each developer have their own account? How do you know who is doing what?

            However a majority of the firms don’t really stick to those custom development recommendations and JUST let the developers to deal with β€œapps” account itself.

            A lot of people might use ‘*’ in their application SQL statements too, but I don’t like that either.

            I totally understand and can relate to the problem our tool is presenting to your team, but my solution would be to change the way your team is doing development, not to change the tool.

  11. Hi Jeff

    We are trying to limit the usage of Quest Toad across our development requirements, as we can’t literally afford the licensing costs. Being the IT manager, I took the lead and started using SQL Developer on infrequent intervals to make sure that slowly I can “enforce” it on to my team.
    However, I wish the future versions (we are using latest as on date) will have the describe (SHIFT+F4) bit more extended as is Quest Toad

    The ability to add a column or view data from the describe window makes the lives of developers bit easier, if NOT asking for too much.

    I just love the look and feel approach (Don’t know whether the same was introduced recently) which allows the users to choose between Oracle (I never liked it) and Windows, which almost gives the feeling that We are using a standard desktop software πŸ™‚

    Regards,

    Rajesh

    1. thatjeffsmith Post
      Author

      Instead of doing the shift+F4 describe, do a ctrl-click into the object itself – then you have the entire editor for that object and all of its powerful features. In v4 you can even float the editor and make it work somewhat like a floating describe.

        1. thatjeffsmith Post
          Author
          1. Excellent, noticed something πŸ™‚ You need select in front of the table name, when you can just type in the table name and do a F4 to bring up the entire editor over Toad.

          2. thatjeffsmith Post
            Author
          3. Wow πŸ˜‰ Alt+G looks more promising to me, btw Jeff, why you have the parser depending upon a hint? Object libraries couldn’t be referred instead (I know you better, still asking πŸ™‚ )

            Thank you! A wonderful job

          4. thatjeffsmith Post
            Author
  12. Fantastic post πŸ™‚ Quick question on #5…I haven’t used the modeling tool before. Does it work in reverse? That is, can you generate a model from a given set of tables? That would be super handy, as that’s the direction I work in – developing code to work with the DB objects that are already there, and rarely creating new ones.

    1. thatjeffsmith Post
      Author
      1. I feel this tool should be more simplified. It’s getting complicated in times. I believe oracle should give a thought of simplifying this. Keeping some simplified command windows and all cos frankly speaking the traditional ways we adapted will get tough to follow when we implement more GUI and all.

        1. thatjeffsmith Post
          Author

          I don’t think it can get simpler if you’re in a UI. What in particular to you find complicated?

          You can always run SQL*Plus if you want a more stripped down experience.

  13. Great post Jeff! As a member of the MyDesktop team, I can add to your item #1 that even MyDesktop is built using SQL Developer πŸ™‚

    1. thatjeffsmith Post
      Author
  14. Jeff,
    This is great, I didn’t touch DBA tab until now, but it is very useful. Nn the DBA tab, the instance viewer needs JAVAFX, so after I installed it and pointed JAVAFX_HOME to that directory I only see ‘Waiting for Editor to initialize’ … for more than 10 minutes. Any ideas on fixing that?

    1. thatjeffsmith Post
      Author
  15. Excellent post! I, for one, agree that the more you know – especially about the little known features – you can really make a difference in your organization. Great job continually getting us info that makes our lives more productive!

    1. thatjeffsmith Post
      Author

Leave a Reply

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