Product Review: EMS SQL Manager
January 10, 2007
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 Quests TOAD and Oracles SQL Developer. If you like the Microsoft Office or XP look and feel appearance in your Windows applications, youll be sure to appreciate what SQL Manager for Oracle has to offer.
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 Manager.net 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.
That depends. The two options for purchased/licensed installations are non-commercial and business. You can request a non-commercial license, but youll 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.
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 isnt that significant.
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 (Id use Apache and require logon authentication).
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 Scotts EMP table looks like on SQL Server.
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.
If your database uses partitioning, and youre 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.
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.