Product Review: EMS SQL Manager

SQL Manager for Oracle is a
“high performance tool for Oracle Database Server administration and
development.” Developed by EMS, a company primarily based in Chelyabinsk, Russia with
other offices located in Europe and the United
States, SQL Manager for Oracle is an
affordable compromise between Quest’s TOAD and Oracle’s SQL Developer. If you
like the Microsoft Office or XP look and feel appearance in your Windows
applications, you’ll be sure to appreciate what SQL Manager for Oracle has to

Although the focus of this
review is on SQL Manager for Oracle, it may be of interest to you that EMS also
produces similar tools for quite a few other database systems. SQL Server, DB2,
and MySQL are among the major systems. Many of the other RDBMS products include
applications for data migration and data generation. Several vendors offer data
generation tools in addition to a combined administration and development tool.
Aside from providing sample schemas, Oracle does virtually nothing in terms of
offering a data generation tool.

Where to get SQL Manager for Oracle

The quickest and easiest
method is to download the product from the SQL Web
site. Another option is to purchase (at a nominal price) a CD from EMS. If you
work with more than one system, purchasing the CD is a good option as EMS ships all
of its products on the CD. The CD also provides a one-stop source for recovery
of program files.

Which Version?

That depends. The two
options for purchased/licensed installations are non-commercial and business. You
can request a non-commercial license, but you’ll need a commercial email
address. With both options, the price includes at least one year of maintenance
(up to three years is available). The maintenance cost enables you to receive
upgrades, technical support, and a hefty discount on new releases. As of the
publication date of this article, EMS is also offering a 20% discount (for new customers)
through the end of January, 2007. A 3-year business license then costs just
over $300, or on average, just over $100 per year. You can easily spend that
much money on the time needed to stop and start some products, free or
otherwise, when their sessions hang.

Of course, you want to try
before you buy, so EMS offers a 30 day evaluation period. You can also
download a “Lite” version which is almost as functional as a purchased version.

Major Features

EMS lists over 60 features and the list is quite
impressive. You can compare what
a complete or professional version has versus what is enabled in the Lite
version, and my impression is that what the Lite version lacks isn’t that

One of the more “gee whiz”
features is an HTML report wizard. Select a database, a few schemas (if not all
of them) and some objects (again, if not all of them), and the wizard generates
a very nice drill down type of report. Using the five sample schemas (HR, IX,
OE, PM, and SH), as an example, the report generation took a couple of minutes
and the output is impressive.

From the main or index page
(which you can have appear once the report generation is finished), select
Tables. From there, select a table and note how neatly information about the
table is displayed.

The value of that wizard
alone more than justifies the price of the product. Do you work in an
environment where schemas are not documented? Or not directly available for
developers? You can create reports on all pertinent schemas and publish the
HTML pages on a file system or Web server (I’d use Apache and require logon

The next best feature is the
Visual Database Designer (Tools > Visual Database Designer). Select a group
of tables (example below uses the seven tables in the HR schema) in the Object
pane, drag them into the drawing area, adjust the layout if you prefer, and
just like that, you have an ERD for a schema.

As with other ERD or CASE
tools, the screen real estate is limited, so what helps you navigate around a
drawing is a Navigator window which bounds in red what it is you are currently
viewing on the screen. The Navigator maintains a correct perspective, that is,
if you shrink the drawing area, the Navigator border reflects the action. In
the screen shot below, I shrunk the drawing area and Navigator immediately
shows what took place.

Another sharp tool is the
Export Data Wizard. You want to dump the contents of a table into some other
product/format? Select a table, right-click and fire up the wizard. How many
postings on the Internet have you seen where someone is asking how to dump
table data into some other format?

Another option is to create
a script, and the example below shows what Scott’s EMP table looks like on SQL

Just as easily, you can
export into other databases, and the example below shows the EMP table after
being imported into MS Access.

SQL Manager for Oracle is
similar to SQL Server in that you can right-click on an object and choose to
display a “new” or “edit” window. Creating a new function results in
boilerplate text ready for your use and edit.

Editing a function results
in the code appearing in an editor window.

Another neat feature is the
dependency tree. Who or what uses those ten or so rollback segments?

Select a table in a schema
and the dependency tree can be expanded and collapsed as need be, plus an
object can be set as the root. You can spend hours exploring a database via
this tool. The dependency tree tool is extremely powerful given that it can
rapidly generate a visual display of how objects are related to one another.

What’s Missing

If your database uses
partitioning, and you’re looking for a tool to help manage and administer
partitions, SQL Manager for Oracle is not your best bet. The interface into
table partitions is non-existent (SQL Developer has the same limitation). This
is one area hands down that TOAD does better. Monitoring sessions and their
I/O, current statement, long operations, etc. a la TOAD is also not available,
but that limitation can be worked around via the use of canned scripts which
can executed from within SQL Manager for Oracle.

In Closing

EMS SQL Manager for Oracle
is an impressive tool in many regards. Overall, its appearance is sharp, its
performance is good and quick, and the amount of functionality it packs into a
small installation footprint (around 15MB, compared to over 110MB for SQL
Developer) is enormous. For as little as it costs, it is an excellent option to
supplement TOAD in an application/development environment, and in some cases
(small to medium business or small development shop), it is probably all you
need. EMS SQL Manager for Oracle definitely warrants two thumbs up.


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles