Oracle SQL Developer – A New GUI For Database Development

raptor_image.jpgAt long last Oracle has come up with a modern product to replace SQL*Plus.

Oracle SQL Developer (formerly Project Raptor) was released last month with little fanfare; however its release represents a quantum leap in functionality and ease of use over previous Oracle provided SQL development tools.

SQL Developer is Oracle’s new, free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer,you can:

  • Browse database objects
  • Run SQL statements and SQL scripts
  • Edit and debug PL/SQL statements
  • Run provided reports
  • Create and save custom reports

Now all these features exist in Quest Software’s product Toad for Oracle so why switch? Well, here’s the hook… SQL Developer is free. Yup, free. It also has another strategic advantage over Toad… SQL Developer is cross platform! I am currently running it side-by-side on Mac OS X and Windows XP, and there is also a Linux version available. The Mac and Windows versions are both extraordinarily easy to install, configure, and use. I can only assume the Linux version follows this trend.

So what’s the down side? Well, I’m personally disappointed that they changed the name from “Raptor” to “SQL Developer”, but that aside, you can’t use it on databases earlier than

A small problem for Toad users will be adjusting to the date format in SQL Developer. Toad defaults to showing the time when displaying date data types while SQL Developer will only show the date. The solution to that is to add a TO_CHAR around the date column in the query (for more information on the TO_CHAR function, check out the Displaying Dates section of my article Oracle, SQL, Dates and Timestamps). Toad users may also miss being able to click on the header of a column to change the sort order of the output, but this can also be overcome by adding an ORDER BY clause to the query.

Setup was quick, taking less than a minute to download, unzip and connect (yes, one minute, you’ve probably spent more time reading this than it takes to install). As you’re setting up SQL Developer you’ll notice that it does not require a TNS Names or other descriptor file to find databases. When you set up a connection you are prompted for the hostname and SID of the database. This assures maximum flexibility but does run the risk of confusing end users, since they can nickname the connections anything they want to.

Below are some screenshots of SQL Developer, but if you’re interested, just go out and get it. It’s free after all, what do you have to loose.

SQL WindowPackage Editing

Table BrowsingReports

Click on the thumbnail for a larger image.

oracle, sql, sql developer, database development, database administration, dba

ER Diagramming Tools

While investigating tools for generating ER Diagrams for the data warehouse I’m working on I came across this good article on which compares three of the top CASE (computer aided software engineering) tools for Oracle.

In the article Steve Callan quickly highlights the differences between Microsoft Visio 2003, Oracle Designer and AllFusion ERwin Data Modeler.

What can we take home from this article? Well, Microsoft Visio is (relatively) cheap at $499 and has good reverse engineering abilities, but won’t write database creation code for you.

Oracle Designer is clumsy, but since it’s bundled with Internet Developer Suite you might already own it; otherwise you’re probably not likely to justify the $5,000 cost of entry.

ERwin really seems like the Cadillac solution for database modeling. The $3,995 price tag will put off most, but like they say in the auto industry, if you’re worried about gas mileage you’re not ready for a Cadillac.

Check out the full article for details. Also check out the Oracle section at for some nice series on Oracle related topics.

oracle, database, rdbms, dbms, oracle database, database management, database design, dba, database administration, er diagramming, case, software engineering

Oracle Shared Pool Advisory

Over at the ITToolbox blogs there’s a great entry on the Oracle shared pool advisory in 9iR2. This type of article is great as it presents a tool, describes it, and walks you right through applying the knowledge.

Here’s their description of the advisory:

The shared pool advisory is an Oracle9i feature that keeps track of the library cache’s use of shared pool memory. While doing this it keeps statistics to determine the behavior of differently sized shared pools. Typically, the advisory will keep a bucket of statistics for shared pool sizes that range from 50% below your current setting to 200% of your current setting. It is then up to us as database administrators to use these statistics to determine what the size of the shared pool should be through the use of the view V$SHARED_POOL_ADVICE.

Check out the article for more information on how to enable and use the advisory.

oracle, oracle database, dba, dbms, oracle dbms, database tuning, database administration

Getting Started with the Sun Fire T2000

SunFireT2000After receiving my try-and-buy Sun Fire T2000 over a week ago it’s finally up and running. While we have yet to hit it with much of a load, here are some thoughts on the out-of-box experience:

Before even powering up there are a couple interesting observations about the system. The first is the lack of power button, switch, or key. In fact there is only one button on the system and that is an indicator button which just flashes an LED on both the front and back of the system to make it easier to locate (a nice feature, by the way.) You’ll quickly find the hot-swappable dual power supply and fan compartment. The adventurous will find and the big button on top of the system which allows you to gain access to RAM and other non-hot-swappable components.

All of these compartments and components are accessible without the need for tools. The hard drives are on hot-swappable sleds which pop out the front of the box. The hard drives are small (2.5″ I believe) allowing room for 4 bays in the front and still affording enough space for ventilation. I was somewhat surprised to find laptop-style drives in a Sun server, but I guess I can’t come up with any reason not to.

After giving the hardware a good once-over it was time to get the T2000 up and running. I have to say Sun came up a little short on the out-of-box experience at this point. I’m comfortable with, even partial to the absence of on-board video on a server, and Sun has chosen to include serial via RJ-45 on the T2000. Sun was on the right track including two cables appropriately shielded for use with this port, but neglected to include an adapter to go to the 9 pin serial we all have on the back of our PCs.

After rummaging around for the proper adapter to hook the T2000 to an old laptop we plugged it in. The system is surprisingly (read obnoxiously) loud, but hey, it’s a server not a desktop. After a few minutes of the regular hardware diagnostics stuff the system came up to an sc> prompt.

This is where those who are not familiar with Sun’s newer hardware will come to a complete stop. The only documentation included with the system is the Sun Fire T2000 Server Getting Started Guide and that does little more than tell you what the different lights mean and where you can find more documentation online. If you get online and grab the Sun Fire T2000 Server Installation Guide this will walk you through dealing with the SC serial management port. In my opinion Sun should have included this one in hard copy with the system as well.

After a couple more steps things start to feel familiar and the rest of the setup is much like the Solaris 7, 8, and 9 installs I’m used to. With just a few extra steps here you can (and I’d recommend) configure the SC Network Management port which will allow you the same functionality as the SC Serial Management port without the need for the serial cable and adapter. You will need an extra network drop and IP address to use network management, but then you’ll be able to telnet to it for administrative functions. Of course in a production environment you’d want this behind a firewall or on a private subnet.

Other than that the system seems quick. Our next step is to get Oracle on the system and throw some queries at it. I’ll share more as soon as we get some results.

UPDATE: I have now had the chance to test drive some Oracle jobs on this system. Check out my findings here.

database, database administration, database administrator, dba, dbms, rdbms, solaris, sun, sunfire, sysadmin, system administration, systems administration, t2000, try and buy, unix, oracle

Oracle Timestamp With Time Zone

Last week Warren left a comment on my story Converting Time Zones in Oracle asking how he could output dates with time zone like: “17-Mar-2006 14:30:00 EST”.

Well, after a bit of digging it turns out the answer is not as simple as it sounds. The traditional Oracle DATE and TIMESTAMP datatypes don’t store time zone information. A workaround might be to store time zone information in a separate column in the table, but that seems like it could cause some confusion.

Well, it looks like in version 9i Oracle has added a new datatype to handle exactly this. The TIMESTAMP WITH TIME ZONE datatype allows a time zone to be stored with a date and time either in offset from UTC or by abbreviation.

The TIMESTAMP WITH TIME ZONE datatype can be declared in the table definition anywhere you would have used DATE or TIMESTAMP. To store a date/time/time zone into a row Oracle has also added the function TO_TIMESTAMP_TZ which acts much like the familiar TO_DATE function, however will recognize TZH, TZM, TZR, and TZD for time zone hour, minute, region, and abbreviation respectively.

To retrieve time zone information you can apply the same new abbreviations to the familiar to_char function when selecting a column of type TIMESTAMP WITH TIME ZONE.

Rather than go into any more detail here, check out this article from Oracle Magazine. It covers the topic very well including example code.

sql, oracle, database administration, database, dba, database development, dbms, pl/sql