Tuesday, December 21, 2010

SQL Developer

In the past as a DBA and developer, I used Quest TOAD on a regular basis. Now with the freely available SQL Developer tool from Oracle, there is the perfect solution for a robust and easy to use development and DBA tool.

Oracle SQL Developer now has the following utilities:

1. Data Modeler- instead of ErWIN you can do it from SQL Developer
2. SQL and PL/SQL Development
3. Third Party Database Migration tool- migrate from MySQL and DB2 or Microsoft SQL
Server

4. DBA tools for monitoring user sessions, killing processes, etc.
5. Search feature for online Oracle Documentation

In my book on database migrations, Migrating to Oracle Expert Secrets for Migrating from SQL Server and MySQL from Rampant Tech Press, I discuss how to use the migration tools available in SQL Developer. Sue Harper of Oracle also has excellent discussions from the Oracle SQL Developer product management team. Let's take a quick peak into some of the new and useful goodies available in the latest version of SQL Developer - the swiss army knife of Oracle development tools!

The main window and menu has list of tools available:



I like the database copy utility when I need to clone a database fast:

Tools->Database Copy


Next up is the cool feature for data modeling. I will write more about this in a future blog post as the new data modeling tool merits a blog post of its own. You can generate new data models for physical or logical database designs or re-engineer a current database environment as well as gather DDL for the entire database or schemas.

The Unload Wizard is interesting tool which I plan to explore and test in the future. This allows you to dump data in various formats such as XML, PDF and flat file formats.



I also like the Diff Wizard tool to check for synchronization issues between database environments. I find this particularly useful after I perform a database refresh to a DEV environment from Production to ensure that the development database is in sync with production. SQL Developer provides a simple option as shown below to perform this task.



We can choose options for taking the diff in the databases



Once the report completes it shows the diff between the two databases:



Finally, with the DBA mindset, I like the monitoring capabilities to view sessions and trace expensive SQL statements. No longer must I use SQL scripts to view SQL statements and explain plans. Its so much easier as shown below.



And last but not least is the way cool search functionality for checking online Oracle documentation and even the Tom Kyte AskTom site:



This makes the latest version of the Oracle SQL Developer tool a one stop center for many development and DBA tasks and best of all, it is FREE!

Cheers,
Ben

4 comments:

Gus said...

I'm intensely interested and will be sure to waste innumerable hours on this over the holiday season, but which version of SQL Developer are you reviewing?

Ben Prusinski said...

I tested the beta release of SQL Developer listed below:

Oracle SQL Developer 2.1 Patch 1 (2.1.1.64.45)
Released 01 March 2010

Cheers,
Ben

PS: Use in test sandbox as it is beta!

Kharisse Grace said...

I'm interested with this software, it has features that will surely reduce the work for a DBA. You mentioned that migration from "MySQL and DB2 or Microsoft SQL Server" is possible. I am using PostgreSQL for my database, is it also possible to migrate from this platform?

Ben Prusinski said...

@Kharise,

No, I don't think that SQL Developer supports migrations from Postgres to Oracle. Check the release notes online at http://download.oracle.com/docs/cd/E18464_01/index.htm and My Oracle Support (http://support.oracle.com) as things may have changed since the last release.