DB Tools for Oracle by SoftTree Technologies

DB Tools is a suite of over 20 different lightweight applications, all launched from a common launchpad and sharing common features and interface design. The modules are very full-featured, and there are plenty of extra goodies clearly designed with the working DBA in mind. DB Tools 5.0.4 is new as of October 2005, and while there are still some rough edges out-of-the box, I found SoftTree support to be knowledgeable and committed to making the install work. DB Tools is a useful, well-thought-out suite of tools with a lot of functionality, and with a bit of QA on SoftTree's part it could easily be great.

Usefulness to DBA
Usefulness to developers
Functionality, how much does it do
User interface, intuitive/friendly
Software quality, integrity, robustness
Documentation quality and scope
Technical support availability
Value for money
Ease of integration with other tools and systems
Overall rating

What is great about this product?

DB Tools feels like a product designed for working DBAs by an experienced DBA. It's jam-packed with components that each address a specific DBA automation need or task, each with an intuitive GUI. And unlike many competing products, the components are separated into functional modules that run as separate lightweight applications. This makes for quick start-up of each individual module and means that DB Tools never loads more code into memory than you need. You can also avoid cluttering the database with objects by only loading the server-side objects needed for the tools you actually use. And you have the option of buying only the components you'll need.

History

DB Tools 5.0 was released February 1, 2005. It includes the following new components: LOB Viewer and Loader, File Loader, DB Space Expert, DB Copy Expert, DB Consumer Expert, and Test Data Generator. In addition, the existing components have been enhanced with a new Database Error Monitoring and Alerting package and a new Background DB Performance Statistics collection procedure. There are also many enhancements to the SQL Editor, DB Difference tool, and a number of other components. This version also provides full support for 9i and 10g clients and allows SYSDBA/SYSOPER connections, bringing it into line with competitors like TOAD and SQL Developer.

Technical Requirements and Supported Databases

DB Tools 5.0.4.11 only requires 36 MB of disk space on your local PC (server-side objects are optional). Windows 95, 98, Me, NT, 2000, XP and 2003 are supported for your local machine, and the database can be Oracle 7.3, Oracle 8/8i, Oracle 9i or Oracle 10g, on any platform. You must have some sort of Oracle client libraries installed; you may connect by OCI/SQL*Net/Net8/Net9 or ODBC.

Installation

DB Tools 5.0.4.11 was very easy to install. The suite of applications comes as one file with an install wizard; installation was fast and painless. The website boasts that it takes less than one minute to install, and I found that to be true.

I had a little more trouble with the server-side objects. I had difficulty using the Database Setup tool to install the ORA_MONITOR user, which owns the server-side objects used by DB Tools' modules, in my 10gR2 database. The supplied SQL command to create the user was not 10gR2 compliant (it tried to create a quota on the temporary tablespace). However, this was quickly resolved with the help of DBTools' email support staff, who sent me the correct SQL. In addition, several grants to ORA_MONITOR seemed to be missing from the server-side install, and one of the ORA_MONITOR packages contained 9i-specific SQL that failed on my 10gR2 database. These problems were fairly easy to resolve but left the impression of a few rough edges on the server-side install for 10gR2 databases.

What it does

The full DB Tools suite does a lot of things. I don't know of any product with the same broad range of functionality. You can use it to monitor, tune, manage, configure, audit, compare, and benchmark your databases; to tune your SQL and your applications; to manipulate and archive data, compare and copy databases, load and export multiple file formats, including ZIP, LOBs and Excel; and even to develop in PL/SQL or Java.

Who should use it

SoftTree pitches DB Tools 5 to both Oracle DBAs and Oracle Developers. However, as someone who's actively engaged in database administration and development, I found this a much better DBA tool than a developer tool. Many of the features in the development tools are only available when connected as a user with SELECT_CATALOG_ROLE, for example, which is not always feasible even in a development environment. The Java editor, while adequate, is bare-bones, and I couldn't get the PL/SQL debugger to work. In contrast, the DBA tools are well thought out with DBA needs in mind.

DBAs with a few years of experience will benefit from tools like the DB Monitor more than novice DBAs, because so much of the power of the DB Monitor lies in its customizability. There's plenty in the DB Tools suite for novice DBAs, though, such as graphical user/role managers, GUI interfaces to the job queue, and the Table Painter.

Competitive products

This product reminded me most closely of the Oracle Enterprise Manager management and performance packs. Picture an Oracle Enterprise Manager with more features, a better user interface, a real SQL Editor, and no Web or Java interface to slow things down. Now imagine integrating it with TOAD's object browsing and SQL editing capabilities, and you'll have a feel for the DB Tools suite.

Other than OEM, DB Tools' competitors include tools like TOAD and PL/SQL Developer. DB Tools has a much stronger database monitoring component than either of these competitors, though. The individual components of DB Tools collectively approximate the monitoring capabilities of tools like Spotlight or DBA Cockpit, but DB Tools doesn't come preconfigured with the ability to show you a full picture of current database availability on the screen at one time. Its DB Monitor module is so highly configurable, though, that you could set it up to act more or less like Spotlight with a bit of work: any query you can run against the DB can show up as an auto-refreshing graph, with custom refresh interval, on your screen.

Detailed review

Let's look at each module in turn.

DB Tools Launch Pad:

Your DB Tools experience starts here. DB Tools is a suite of over 20 different lightweight applications. Launch Pad is a small application with an icon in your Windows Toolbar; when launched, it provides a single window with icons you can click on to launch any of the DB Tools applications. Each application pops in a separate window. A second tab in the Launch Pad window pulls up a useful list of your most recently opened SQL and Java files; clicking on a file opens it in the appropriate DB Tools editor. You can also right-click on the systray-resident Launch Pad icon to get a quick list of DB Tools applications and launch from there.

We'll treat the rest of the modules in the DB Tools suite in the order that they appear in the Launch Pad.

DB Monitor Expert:

DB Monitor's main powerhouse is its customizable performance graphs and reports, which we'll cover in a moment. But it also includes a Swiss army knife's worth of useful DBA tools:

Top Sessions:

Find in Database: This is a great GUI interface that lets you search the database for a string. The feature goes way beyond looking for object names; it includes the option to search in view definition text, comments, and trigger bodies. DBAs know how annoying it is to search the LONG columns in the data dictionary, making this a very useful feature.

Database Object Navigator: A very full-featured object navigator, easy to navigate, with tabs showing all the information you could want on an object. Associated components include the Data Dictionary Navigator and a quick schema browser.

Security Navigator: An intuitive GUI interface to the DBA_*_PRIVS tables. One very useful feature of the Security Navigator is the ability to include/exclude rights available to a user as a result of grants to PUBLIC.

Table Painter provides a GUI view of tables and their keys, and a GUI interface to add/modify tables. I wasn't able to create a table (kept getting an ORA-904), but otherwise it's full-featured and easy to use. I did find Table Painter to be a bit slow to render on my system.

DBA Notepad: This is DB Tools' SQL editor; see below, under the "SQL Editor and Debugger" module, for a review of this component.

Connection Manager: Shows connections, waiting processes, lock SQL (uses v$access instead of v$locked_object), and a locking tree. WARNING: Clicking on the "Locks" tab in Connection Manager hit some sort of Oracle bug that repeatably crashed my unpatched 10gR2 instance with an ORA-600. Since it's an Oracle internal error, this is in no way DBTools' fault, of course, although it does add to the impression of a product that may not have been thoroughly tested against 10gR2. I encourage readers who are on 10gR2 to try this particular component on a test instance first.

Dependencies Analyzer: A useful little tool

Status Analyzer: This is a little tool that pops up a list of all invalid and disabled objects in the database, grouped by object type. It's not much more useful than simply running an equivalent query in a SQL window. It would be a lot more useful if clicking on the name of an invalid object brought up some object information -- eg. table owner/name for a synonym, constraint cols & base table for a constraint, etc.

Schema Reverse Engineer: A quick and painless way to extract DDL to recreate the schema of your choice.

Table Size Estimator: What DBA hasn't had to pull out the calculator and engage in some hair-pulling to estimate the future size requirements of a table and its indexes? The Table Size Estimator pulls average row info and does the math for you. Very useful.

Table Analyzer, Index Analyzer and Cluster Analyzer provide GUIs to analyze these objects.

Note: DB Tools is unique in using common Windows-app keyboard shortcuts for DB tasks. For example, the shortcut to refresh the view in a realtime screen, such as the Connections Manager, is Ctrl-N for New; the shortcut to kill a session is Ctrl-D for Delete; the shortcut to commit changes to a table is Ctrl-S for Save. Unlike some of its competitors, the keyboard shortcuts aren't configurable, but DB Tools has gone to a good bit of effort to make them intuitive.

In addition to the above built-in tools, DB Monitor also offers a great deal of flexibility in delivering custom-built reports, real-time performance graphs, monitors and trend graphs. DB Monitor comes pre-packaged with about a dozen of each ("monitors" are just queries that return in a regular results grid). Modifying the supplied reports and graphs is as easy as modifying the SQL that appears side-by-side with the report name, and adding your favorite monitoring queries & reports is as easy as pasting in the SQL and adjusting the display properties.

I found the graph interface to be awkward -- it's primitive and choked on most computed columns I tried it with, although it worked for GROUP BY expressions like count(..) and sum(...). Having said that, the custom performance graphs are still an extremely powerful tool, and I was able to coax my custom DB monitoring scripts to appear as graphs. Despite the rough edges, this is a great tool for a senior DBA who has developed a library of scripts over the years.

Similar comments apply to the reports and trend graphs. The reports interface is particularly minimal; most field properties are set numerically in a grid. Still, it's sufficient for the basic DBA task of reporting on the database. I should note that not all of the supplied reports ran as-is on my 10gR2 instance. Some referenced 9i-specific x$ tables (but weren't labelled as 9i-only); some returned an "Invalid number" when I tried to run the SQL by itself. For an experienced DBA, this isn't a big deal, but again, it adds to the impression of a product that hasn't been thoroughly tested against 10gR2. For a novice DBA, this could be a real drawback, but for an experienced DBA, it's just another small annoyance in an otherwise useful tool.

The trend graphs require the DB Trends Expert server-side objects to be installed and running on the database. They provide valuable information, but I was disappointed to see that DB Monitor didn't take advantage of the built-in trend monitoring objects in 10g (ASH), or even of the STATSPACK objects in 9i, which DBAs are more likely to already have installed in their databases and which have a known database load.

DB Difference Expert:

This tool takes a somewhat different approach to comparing DBs than some of its competitors. Instead of running a quick schema compare and then discarding the results, as tools like TOAD do, the DB Difference Expert builds a repository of objects in the ORA_MONITOR user's schema and then provides reports on the repository. This makes it a very powerful tool, but also makes it very slow. A simple schema comparison between two similar schemas in two medium-sized databases took easily four times as long as the same comparison in TOAD. However, unlike TOAD, the DB Difference Expert's schema comparison is highly configurable -- so, for example, you can avoid seeing all those annoying messages about different constraint names and index names if you just want to know which tables are missing from your dev database. And once a database or schema snapshot has been loaded into the repository, comparisons against that snapshot are quick. You can also compare code definitions, table contents, and choose whether to find matching or non-matching objects.

DB Index Expert:

Analyzes indexes, using the options of your choice, and provides output and recommendations in neatly-formatted reports. The neatest feature of the DB Index Expert is its command-line availability. This lets you submit index analysis requests as a batch job to run unattended.

SQL Editor and Debugger: The SQL Editor and Debugger features a very easy-to-use, flexible interface. It's quite full-featured and includes the following components, many of which are shared with the DB Monitor:

DBA Notepad: The main interface, also available in the DB Monitor. More on this component below.
Find in Database, Table Painter, Schema Reverse Engineer and Table Size Estimator: The same tools as in the DB Monitor Expert.
Database Object Navigator and Security Navigator: These are also the same tools as in the DB Monitor Expert. The one drawback to these components is that they require access to the DBA_* tables. This makes them unavailable should you wish to connect as a non-privileged user. This is not a problem for DBAs, but would be a major barrier for developers. Competing tools that are aimed primarily at developers, such as PL/SQL Developer, tend to use the DBA_ views if available and the ALL_ views if not.
Compare Files: This tool lets you compare the text of any two text files on your machine, whether they be database-related or not. You may find yourself firing up the DBA Notepad just to use this great little utility!

In addition to all the tools above, the DBA Notepad itself is very full-featured. It has all the standard features like syntax highlighting, code formatting (comment/uncomment, indent/unindent, etc), multi statements in a window, etc. But it's got plenty of extra goodies, too. I especially appreciated the following features:

Result sets pop in separate windows, so it's easy to compare the result sets of two successive queries.
In a long-running DDL/DML operation, the SQL Editor displays a progress window with a handy "Kill Session" button. If the user you're connected as has Kill Session privileges, you can simply kill the session.
The DBA Notepad supports sql*plus-style substitution variables, including support for the DEFINE command.
In addition to DEFINE, the DBA Notepad supports useful sql*plus commands like DESC, SHOW ERRORS, PAUSE, EXECUTE, and more. Most competing products support these commands only in a separate "command" window, if at all.
You can minimize the SQL Editor during processing, and its icon in the taskbar will flash at you when your statement has been processed.
You can put multiple SQL statements in one Editor window, and SQL Editor provides easy-to-remember keyboard shortcuts to let you run the current statement your cursor is on, all statements, all statements below the cursor position, or selected statements only.
In addition to the full-featured Database Object Navigator, there's also a quick schema browser associated with each DBA Notepad window. It appears to the right of the code window and is very useful when composing SQL. You can drag-and-drop object and column names into the Notepad. Unfortunately, this tool also requires access to the DBA_ views, again making it less useful for most development environments.
The Explain Plan window has two great features I have not seen in any other DBA tool. First, the plans for five common optimizer hints are shown in tabs next to the plan for the original statement. Second, a "Compare" tab runs the SQL statement each of the six preceding ways -- original, and with each of the five common hints -- and produces graphs of relative execution time, etc. I would love to give a copy of this tool to every developer I know.

The SQL Editor does have one shortcoming with respect to the editors in some competing products: you cannot process more than one SQL statement at a time. The SQL Editor session will not let you do anything else while a statement is being processed. Some competitors, such as TOAD and PL/SQL Developer, offer the option to open multiple DB sessions for each application window, which lets you process multiple statements at once. However, this is a minor shortcoming, as you can start a separate SQL Editor from the DB Tools Launch Pad; it will run in a separate window and memory space, and you can process concurrent queries there.

DB Tools' SQL Editor is so full-featured that I was surprised not to find one of my favorite editor features -- context-sensitive code assistance. Some competing tools pop up a list of table names, column names, function arguments, etc. where appropriate. It's easy to find this information in the quick schema browser, or via a DESC statement, but DBAs who have come to rely on context-sensitive code assistance will miss this feature in DB Tools.

I had some trouble with the SQL Debugger. I couldn't get it to work with my 10gR2 database, either as a SYSDBA or normal user. It would either start a debugging session and immediately shut down, or hang. So, unfortunately, I wasn't able to review it.

In general, the usefulness of DB Tools to developers is hampered by the requirement that you log in as a user with SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and SELECT ANY TABLE privileges in order to enjoy full functionality. This is expected for tools like DB Monitor and DB Space Expert, which are focused on DBA tasks, but a real hindrance for tools that are aimed at developers. It's simply not always a good idea, from a security

SQL Profiler:

Provides a GUI for DBMS_PROFILER. DBMS_PROFILER lets you benchmark and analyze the performance of your PL/SQL code. Since code tuning is an iterative, repetitive process, DB Tools' SQL Profiler allows you to group related DBMS_PROFILER runs together as projects; these are stored in the DB along with related information.

Java Editor:

This is a simple Java editor with syntax highlighting. An integrated file browser is conveniently located on the left-hand side of the edit window, and an integrated quick schema browser pops on the right-hand side. Editing commands are the same as in the DBA Notepad. The Java Editor module doesn't have most of the extra goodies, like the Object Navigator, and Find in Database tools, that the SQL Editor module has, nor does it have a debugger. Casual coders or Java developers accustomed to coding in emacs, vim, BBEdit, UltraEdit or other syntax-highlighting text editors will enjoy the added functionality of the integrated file browser and quick schema browser; developers looking for a full Java IDE should look elsewhere.

DB Log Expert:

The DB Log Expert provides rapid access to redo log information. In addition to a nice visual showing the current redo log status in realtime, the Log Expert provides several useful reports. It offers both realtime and retrospective reports. On the realtime side, one graph tracks SCNs; another shows the % space used in the active log file; a text report shows redo latch statistics and provides advice on contention, and another shows a listing of all log-related database initialization parameters. The retrospective reports analyze database load based on the rate of changes recorded in the log files; you can choose from several report options to get a quick feel for the database load at any given time in the history still captured in the database's control file.

I especially appreciated that one button-click on the toolbar is all that's needed to bring up a listing of the database redo log parameters in a separate window.-- in a log crisis scenario, this is much better fumbling through, for example, "select * from v$parameter where parameter_name like 'log_archive_dest_n%" or even "show parameters log_archive_dest". It's a small feature, but it's this kind of attention to detail that makes DB Tools feel like it was written with the working DBA in mind. I look forward to future versions including the db_recovery_file_dest parameters, for DBAs using OMF.

DB Log Expert did crash a couple of times on my Windows XP Pro machine, although I wasn't able to work up a test case. I was always able to restart with no problem and no consequences for the database.

DB Benchmark Expert:

This is a great little tool with a clean interface. It lets you do some basic TPS benchmarking on your database -- very useful if you're adjusting parameters and want to test the effects. You can specify the duration of the test, the number of connections -- anywhere from 1 to 100's -- and the DML and DDL that each session will run. (The tool comes pre-supplied with basic insert, update, select and delete statements on a pre-configured test table.) The results are displayed in a neat graph. The tool will benchmark and graph simultaneous connections vs. TPS/connection, too.

DB Audit Expert:

The DB Audit Expert provides an interface to Oracle's classic auditing, and several extensions that will be especially handy to pre-9i users.

System Audit Options tool: Lets you set system audit options, ie. which objects, statements and/or privileges to audit. I found this a tiny bit less fine-grained than simply using SQL -- for instance, I couldn't find anywhere in the GUI to AUDIT CREATE TABLE BY . The interface is very clear, though.
Advanced System Audit Options: This component supplies three "missing pieces" to Oracle's auditing functionality. First, it lets you automatically archive system audit table (AUD$) contents to a file or table, or move the Audit Trail out of the SYS schema altogether. Second, it lets pre-9i users achieve some semblance of auditing SYS operations by loading the OS audit trail into the database. Third, it provides a handy interface for setting system event triggers to audit database error events, functionality that's also available through the DB Alert Expert interface.
The toolbar provides handy one-mouse-click shortcuts to clear out AUD$, archive its contents to a different database table, or archive its contents to an external file.
DB Audit Expert also extends Oracle's basic auditing capabilities by providing auditing on data changes to database tables. Note: This tool doesn't use Oracle's Fine-Grained Auditing (FGA), which, as of 10g, captures who, what, and when for every DDL. Instead, it uses a home-grown, trigger-based solution that can be implemented on any supported Oracle version, unlike FGA. Like any trigger-based data auditing solution, it's not particularly lightweight. A shadow table is created for every audited table, and an OLD and a NEW row inserted for every change -- unlike FGA, the SQL itself is not recorded, but the results. The data change reports were quite no-frills, but usable.

DB Tuning Expert:

DB Tuning Expert is a wizard that can analyze instance parameters, schema objects, the library cache, performance trends, and space usage trends, and provide a report with tuning recommendations. The performance trends and space usage trends use data collected by DB Tools packages installed for other tools -- the DB Monitor and DB Trends packages -- so no new server-side objects need to be installed to take advantage of this tool.

Some of the recommendations seemed a bit out of date; for instance, Oracle now says the TIMED_STATISTICS parameter causes negligible overhead, but the DB Tuning Expert still flags it as inappropriate for production. And the wizard didn't seem equipped to recognize the automatic memory management parameters -- it reported that my SHARED_POOL size was too small at 0 bytes. Also, given that it didn't recognize that the memory was configured with SGA_TARGET and PGA_AGGREGATE_TARGET, I was surprised to see that the wizard didn't notice my LARGE_POOL was set to 0 bytes with MTS enabled.

The tool is still quite useful for experienced DBAs who basically know what they're doing, and want a report of potential problem areas that they will then evaluate themselves.

Performance Library:

This tool just launches DB Monitor Expert with an extra window that lists all the reports, graphs and monitors in one place, including any custom ones you've created.

DB Job Expert:

The central piece of DB Job Expert is the Job Scheduler Console, a very nicely implemented GUI front-end for dba_jobs and the dbms_job package. The tool doesn't yet include an interface for the new Oracle Scheduler (dbms_scheduler and the dba_scheduler_* tables); perhaps future versions of this module will.

In addition to the Job Scheduler, you have the option of installing a Job Monitor, which collects performance statistics for every job as it runs. The Job Expert then uses these statistics to generate 10 different reports on job scheduling, overlap and performance.

DB Application Expert:

This tool uses a back-end monitoring package to gather performance data on n-tier applications that connect to the Oracle database. I didn't have any such applications handy, so wasn't able to review this tool. By default, a monitoring job is kicked off every 10 seconds in your instance; DB Tools recommends that you use this tool with caution, and that you not leave it enabled indefinitely, as it can hinder performance.

DB Zip/Unzip Expert:

This is a clean little tool that lets you extract full-table data and DDL, save it in a ZIP archive, and re-import it to a database. The tool works well and the interface is well-thought-out and easy to use. The tool lets you extract the data as a TSV as well as in binary, which could be useful for cross-RDBMS moves; other than the TSV feature, I'm not sure what this tool gets you that you don't already have in Oracle export/import or expdp/impdp. The interface is certainly better, though.

DB Alert Expert:

The DB Alert Options window crashed repeatedly with memory access errors, but despite the crashes, seemed to set up the alert options as desired. The DB Alert Expert comes pre-configured with two monitoring packages, one to monitor the alert log for whatever strings you like, and one to monitor free space thresholds. You can also monitor for any and all database errors. On encountering an alert condition, the DB Alert Expert logs it and optionally sends an email to the preconfigured address. There are three alert levels and you can configure different target addresses based on the alert level.

The best feature of DB Alert Expert is that it provides an easily usable framework for creating your own custom-defined alerts. You can call the alert procedure from any package in the database, and it will deal with the alert as you've specified in your DB Alert Expert set up, logging it and/or sending email based on the alert level.

DB Alert Expert it worked well for me. It emailed me appropriately both for custom alerts and for error messages in the log file. This is a great tool for any DBA. The one thing to watch is that the DB Alert Expert does have to be running in order for the alerts to get sent -- it is not a job resident in the DB, so it will continue alerting if one of your DBs goes down, but will not alert you if you just open it on a PC that you shut down for the night. DB Tools provides a command line option so that you can start up DB Alert Expert every time its host machine starts up.

DB Space Expert:

DB Space Expert provides a nice GUI Tablespace Navigator and tablespace block maps, with the capability to create, drop, add, and modify tablespaces, including tablespace rebuilds, reorgs, conversions, and taking TS online/offline. DBAs still using dictionary managed tablespaces will get more use out of this tool, as locally managed TS don't need as much maintenace; still, the tablespace navigator gives it something handy for all DBAs.

LOB Viewer & Loader:

This is a handy little tool to view, insert and update LOB values in the database. I'd like to see more integration between this tool's functionality and SQL Editor's functionality. I found it a little awkward not to be able to insert or update non-LOB values side-by-side with the LOB values -- for example, it would be nice to be able to fill in the filename after loading a file's contents into a table using the LOB loader. Instead, non-LOB values have to be edited separately in a SQL Editor window. Having said that, this tool does make manipulating the LOBs themselves a breeze. It handles multiple LOB formats and supports several viewer windows open simultaneously on the same table. LOBs can be loaded directly into row fields from disk. Note, there are no facilities for bulk loading of multiple LOBs into a table -- SQL*Loader would be a better choice for that task. This tool is designed to facilitate hands-on manipulation of individual LOBs, and does that task well.

File Loader:

The File Loader allows you to load Excel, csv & tsv files into database tables. It's easy to use and works. You select a file, choose a destination table name, choose the format and load the file. This is a very handy utility, and the ability to move data from Excel to Oracle is absent from most competitor's tools. There are some limitations -- for example, you can't choose which sheet of the Excel spreadsheet is loaded. You also can't see the resulting table in File Loader but must query it in a separate SQL Editor window. The File Loader functionality is partly integrated into the SQL Editor and other tools, but I could only get an Excel format file to load by running the FIle Loader utility. Notwithstanding those limitations, this is a great tool to have in a DBA or Developer toolkit and one that's all too rarely included.

DB Copy Expert:

This is a fun tool to use. A simple, intuitive drag-and-drop interface lets you copy schemas and objects from one instance to another. The interface couldn't be cleaner. You have two copy options -- either use a temporary database link between the two instances, or create temporary staging objects on your own system (not recommended for large amounts of data). It would be nice to see future versions of this tool take advantage of the DBMS_DATAPUMP API.

In addition to the main drag-and-drop interface, the DB Copy Expert includes some of the components found elsewhere in DB Tools: Database Object Navigator, Find in Database, Schema Reverse-engineer, Dependencies Analyzer and Status Analyzer, and interface to run and modify reports in DBTools' shared Custom Report library. As with the rest of the DB Tools suite, it's clear that the designers have carefully chosen the components that are most relevant to the task at hand and included only these in the DB Copy Expert module, helping to keep the module lightweight and the interface uncluttered.

The one slight issue I had with the DB Copy Expert is that the object trees for the two instances in the drag-and-drop interface don't get refreshed after an operation fails -- so although the operation is rolled back, the objects still show in the target database pane, which is confusing. And it would be nice to be able to refresh objects in one operation, rather than drop and copy.

DB Consumer Expert:

This tool is made up of several components:

User Manager provides a well implemented GUI interface for user management, including account lock/unlock, roles, consumer groups, and privileges.
TS Quotas is a handy tool that gives a graphical interface to TS quotas by user or by tablespace.
Profile Manager lets you create and modify profiles and assign/unassign users to them.
Security Manager provides a GUI view of grants, roles and objects by user/role.
Resource Quota Manager has two very nice features: 1) It lets you change resource quotas by profile or by user (creates new profile automatically); (2) If the appropriate server-side objects and monitoring jobs are installed, it lets you see actual resource usage reports side-by-side with the quotas you've set. This is a great feature for tuning database resource allocation.

Data Generator:

This tool lets you quickly generate random test data for your tables, one at a time or as a related group. You have the option of either creating the data via a dynamically created pl/sql proc in the DB, or of creating it in the Data Generator and loading it into the DB. The latter is slower for really large datasets, but lets you use a neat feature: the Test Data Generator comes with several files of sample data that you can choose fields from to populate your own table. Thus, I was able to populate SCOTT.EMP with realistic last names and job titles, instead of typical test data names like 'Erj989dfjKo' or 'Tester123'. You can also set the percentage of nulls in each column to reflect real-world ratios. This is one module in DB Tools that I can say would be quite useful for developers.

DB Tools Assistant:

A migration assistant to help users migrate custom DB Tools monitor, graphs, and reports to the current version. This is my first encounter with DB Tools, so I wasn't able to evaluate this module.

On-line Help System:

Instead of a MS Help file, the On-Line Help System provides a convenient shortcut to the DB Tools documentation .pdf.

Database Setup:

This tool was easy to use, and has one of the best "Choose tools and components to installs" screens I've ever seen -- it's exquisitely clear about what's going to be installed and how often jobs are going to be run. Unfortunately, I had some trouble with the built-in install scripts, as mentioned above. I was able to get all the server-side components to install, with a little work.

Shortcomings

I couldn't find a way to start up a down database from within DB Tools. Ie, connect to an idle instance as SYSDBA and issue startup commands. I was unable to connect to an idle instance using the SQL Notepad, for example. This could be a real headache for a working DBA. I'd like to see a future version of DB Tools include a database control panel, OEM-style, with GUI controls to start up/shut down the database.

DB Tools would occasionally "forget" where my OCI library was on restart, and fail to connect. I sometimes had to close and restart all open instances of DB Tools in order to be able to connect. Fortunately, reminding it where to find the OCI library is as easy as re-selecting the appropriate BIN directory from a pull-down in the connect window.

I encountered occasional crashes and hangs with some of the modules. While I was using it on a regular basis, I experienced a crash and/or hang in one module or another about twice a day. However, I found that the individual modules are so lightweight that having to restart a crashed module wasn't a big deal... as long as I'd saved my work.

I was surprised to see typos in many of the modules -- an "Appy" button instead of an "Apply" button, misspelled pop-up text, and so on. This doesn't affect functionality in any way, but does add to the impression of a product with more rough edges than you'd expect at this price point.

Unfortunately, I wasn't able to get the SQL Debugger to work.

Cost and where to buy

DB Tools 5.0 can be downloaded from SoftTree's site, http://www.softtreetech.com/. Its design, as a suite of extremely lightweight modules that run separately, means that DB Tools has a great deal of flexibility available to its pricing and licensing models. A single-user license to the full suite of modules currently costs $689.95 USD. SoftTree also offers various bundled subsets of modules, such as the Developer bundle ($489.95) and the DB Monitor bundle ($120-$375). Or, you can choose your own set of modules, cafeteria-style. Site licenses for all of the above are also available; call for pricing.

Support is available by e-mail or web form. SoftTree Tech also hosts Product Support Forums on their website. I reviewed the threads from the last few months, and found that support analysts were quick to respond to forum posts; they were helpful and knowledgeable; and they stayed with an issue until it was resolved.

Conclusion

DB Tools is a good bet for the experienced DBA who wants a lot of functionality for their dollar, and doesn't mind a few rough edges. If you have the patience for a tool that may not work perfectly right out of the box, and don't mind living with workarounds here and there, then a feature-rich, thoughtfully-designed suite of tools awaits.

About the author

Natalka Roshak is an Oracle and Sybase database administrator, analyst and architect. She is based in Ontario, Canada and consults across North America. In addition to Oracle and Sybase consulting, she is a regular contributor to OraFAQ.com, and has written technical Oracle development and administration articles for a variety of publications. She can be reached via her Web site, http://rdbms-insight.com/.

Site search

User login

Site navigation

You are here

DB Tools for Oracle by SoftTree Technologies