Database Software Review: dbForge Studio for Oracle

Nikos Minaidis

4 years ago

dbForge Studio for Oracle is a multifunctional tool for Oracle database administration and development. Often considered one of the best, most effective database IDEs given its features, it has a lot of capabilities that enhance PL/SQL development and debugging.

Throughout my career, I have used many database tools. I have had enough experience with most of them to do a proper comparison with dbForge Studio, which I now can highly recommend to professionals as an Oracle IDE tool that will most certainly meet their needs. However, some of the features I value in such tools were missing. I think if the Devart team adds them to the Studio, they will make it the top application out there.

In this analysis, you'll find an in-depth look at both the advantages and disadvantages of dbForge Studio.

What features of database tools do I find the most important?

Since my position (Senior Technical Analyst) includes all aspects of Oracle (both administration and development), I need an all-around and fast tool to administer my databases and their health in the best way possible and as quickly as I can. I also want to write PL/SQL code (packages, procedures, etc.) and debug everything with ease and convenience.

The key features that I use daily are:

Import/Export of Oracle data pumps (DMPs that contain full Oracle schemas)
Connectivity to different versions of multiple databases
Import/Export of datasets from Excel files to Oracle tables (for migration and ETL mechanisms and also for data validation)
Fast query writing (with table and column suggestions)
Debugging of PL/SQL packages and recompilation (availability of reviewing the Error Stack where the uncompiled package has an error; availability of reviewing uncompiled objects)
Schema statistics
Export of DDL definitions of database objects
Export of table data to the SQL Insert file format
Opportunity to review the connectivity of sequences and constraints with corresponding tables (for fast debugging)
Tablespace management for Oracle administration (since the tablespace issue is very common in my work)
Explain plans – execution time of SQL queries for SQL tuning
Database search – references to specific object names

That's it. Of course there are still a lot of other useful features, but these are the most important to me. I will review each of them in the following paragraphs, as well as elaborate on some features I did not mention before.

Do I find dbForge Studio convenient?

Overall, I am very satisfied with it. I consider it quite convenient, mainly because of the features provided, but also because of the usability of the GUI, which I will analyze fully in the following paragraphs.

Now let's get started with the pros and cons, the features I liked and the features that I felt the lack of. Let's see what makes dbForge Studio for Oracle better or worse than other tools I've used.

Advantages & Useful Features

The UI is very user-friendly and easy to use. Also, the black background is a very nice addition since I spend many hours on the computer and especially late at night. The black background makes it less stressful for my eyes, makes them less tired of bright reflections.

The super-fast and full suggestion of SQL writing. Although all database tools have this feature, dbForge Studio for Oracle provides the fastest and the most thorough one. Just as you type SELECT, it suggests tables and statements, while other tools would require an acronym first. Also, after entering TABLE in the SELECT statement, it automatically suggests the columns of that table along with their data type, which is something that exceeds what other tools are offering and is very useful for productive SQL query writing.

Another nice feature is the file extension association with the tool. It opens SQL files with dbForge automatically. It's something that not many tools provide.

One more useful feature is the option to decide whether or not to make the data from the selection of a query editable. This is also available in other database tools.

The feature of AUTOCOMMIT is very useful. It correctly appears in the front of the screen so that the user knows whether the statement commits or not (similarly available in other database tools).

The information about query execution time is very useful for query tuning (similarly available in other database tools).

A nice addition is the availability of information regarding the schema name, the TNS name and the database version (something that the UI in other tools does not provide; instead, the DBA needs to run SELECT * from V$VERSION).

Again the information about every column data type after running the SQL query is very useful. In other tools, the developer has to open the definition of the table to check the data type, which is rather time-consuming.

The paginal mode feature is useful in some cases for data handling and investigation. This feature is not available in other tools.

Card view can also come in handy in some data investigations (also not available in other tools).

A very powerful feature is flexible data export from a selected table. I mainly export data to SQL and Excel files. This makes dbForge Studio more flexible than other tools that don't allow me to choose the exact SQL statement type I need (e.g. INSERT).

The feature button of auto-commenting or uncommenting lines is quite helpful, and it is not available in other tools I've used.

Query Builder is very powerful and useful on many occasions. It is also not available in all other tools.

A feature that I use daily is FORMAT SELECTION. In other tools it might be known as PL/SQL Beautifier. It makes the code cleaner and easier to read.

Another very useful feature is creating new database objects instead of copy-pasting already written code to create new objects. It is not available in other tools.

Execution history, also not available in most tools, is very handy as it can help DBAs and developers to check and rerun statements.

A nice addition is being able to do some text formatting (e.g. Uppercase).

However, I would prefer to access it via right-clicking the text instead of going to the toolbar window to find it.

dbForge also supports the session manager. It is a must-have tool for handling idle and non-responding sessions along with deadlocks and long-running queries. This functionality is available in other tools.

Another key feature crucial to my work is import of data from Excel sheets. dbForge provides it and has related tabs to allocate correct column mapping. Although this feature is also available in other tools, dbForge has the advantage of providing more options.

The table definition view is fairly informative. It has table definition information in both the grid and the DDL statement, and it provides related constraints, tablespace owner info, and the ability to view its existing data. A probable addition would be to also have the table's related SEQUENCE and DEPENDENCIES (also available in other tools).

The database schema export feature is rather powerful. It provides more options of exporting database objects than other tools. Later on, I will also elaborate on some of its drawbacks.

The database diagram tool is very powerful in comparison to many other tools. So, exporting diagrams for my database is easier for me. Actually, the only tools that have satisfied me so far in this respect are dbForge Studio and SQL Developer.

The editing of permissions also comes in handy. Most of the time it's about password handling (also available in other tools).

Another useful feature is editing sequences instead of having to search for their DDL to fix them (also provided in some other tools).

The handling of materialized views is very powerful when it comes to editing a particular database object. It is not available to a such great extent in other tools.
The object browser is a daily feature which I rely on to find references to objects in a database. It is available in other tools.

The Invalid Objects manager is a must-have feature that helps you to compile invalid objects of a database (also included in other tools).

Query Profiler is the key feature for analyzing my execution plans (also available in some tools).

Exporting/Importing DMP via the Studio is very helpful (not many tools have this feature). I would suggest an improvement: the ability to export all objects of a USER schema instead of just TABLE objects.

The SQLPLUS feature is useful in some DBA cases (also available in some other tools).

It needs some improvements (I will mention them later on) in order to be fully satisfying and replace the need for SQLPLUS, at least in common cases.

Disadvantages & Missing Features

I encountered a small confusion with the connection manager. The Oracle Home was located at the bottom, while it should have been at the front, since it updates accordingly the TNS server that is going to be used. Other than that, it contains all the needed information.

It would be useful if in some part of the SQL window it would show the overall number of rows in a large table. Right now, it only shows the rows that I have scrolled through, and I must scroll to the end or run SELECT COUNT(1) from TABLE to find the actual number of all rows in the table. The same problem exists in other tools, where I also have to go through all the records to find the total number. In some cases, this number is available inside a tab in the Table definition.

In some DBA cases, access to ORACLE RECYCLE BIN is needed. It would be useful if in Database Explorer it could be accessed directly instead of being queried.

Example: PL/SQL Developer

A missing feature is being able to open a package from a SQL window. Presently, the only way to do it is open the package via Database Explorer. For example, in dbForge Studio we have the following:

And this is how it works in PL/SQL Developer:

Another missing feature (or one I have not found yet) is the multiple mention of a variable or object inside a package. What I mean is that by selecting a variable or object in a package code, it should highlight the rest of the mentions of that object inside the package. This speeds up coding and debugging a lot.

Example: PL/SQL Developer, selecting the variable l_table_name_typed

Furthermore, if I have a single line code statement or any line for that matter, when I make a TRIPLE LEFT CLICK, it should select the whole line in the editor. In dbForge Studio it works the same as a double-click instead.

So the only way to select a whole line is either by selecting it via dragging the mouse or by using keyboard shortcuts.

Example: This is how it works in PL/SQL Developer by triple-clicking a line:

A feature that needs improvement is export of database object definitions in SQL, but in separate files. dbForge Studio allows exporting definitions of several objects, but does that only in the same file (see the screenshot below).

As opposed to that, SQL Developer allows exporting to separate files:

Another missing feature is GATHER STATISTICS for the database, which is available in SQL Developer:

Instead, in dbForge Studio (and some other tools), the DBA has to run the following query:

begin
dbms_stats.gather_schema_stats(ownname => user,estimate_percent => 25,cascade => true,method_opt => 'for all columns size auto');
end;

begin

dbms_stats.gather_schema_stats(ownname => user,estimate_percent => 25,cascade => true,method_opt => 'for all columns size auto');

end;

Another missing feature is Real Time SQL Monitor. It is a useful tool for DBAs that have bought the Oracle licence for Tuning Pack.