For many years I have worked as a DBA focusing on Oracle Database and also dealing with some other databases, such as SQL Server, PostgreSQL, and MySQL. I used several paid and free tools, but most of them did not deliver what I was looking for.
My goals were facilitating and increasing my productivity at work. I tested several tools with different functionality, and always felt the lack of some specific features until I adopted the dbForge products. Today I can say it is the set of tools I use and recommend to the most diverse professionals who work with databases, striving to perform all tasks with one solution and save resources.
Even using dbForge Studios for Oracle, MySQL, PostgreSQL, and SQL Server separately does not complicate the tasks. The interface of all these products is standardized, thus reducing the adaptation time. Just have a look at the screens of the Studios for different database technologies:
I like its simple and clean interface. You can use seven themes – Visual Studio (blue, light, and dark) and Office (Office 2013, Office 2013 Dark Gray, and Office 2013 Light Grey). These themes are all very light and beautiful. I prefer the blue Visual Studio theme.
The article focuses on dbForge Studio for Oracle – a GUI for Oracle database that I have been using since 2020.
dbForge Studio for Oracle is available in four editions: a free Express Edition with basic functionality and three paid editions – Standard, Professional and Enterprise. Evaluate the list of inbuilt features and choose the edition that meets your requirements best, just clicking here to download dbForge Studio for Oracle and try it free for 30 days.
When connecting to databases, you can fill in the data and segment it by color according to each access type. For instance, it can be red for production, green for development, or yellow for the test environment. Or, we can create a category with the color we want.
It is simple, but it helps to quickly identify each environment, avoiding errors when connecting to the wrong one. When you click the “Test Connection” button, it will validate access, the database version, and display the edition information on the screen.
Here I am creating a connection with the “Production” category in red:
Testing the database access and viewing all schematics:
And getting the initial screen of the tool with the main functionalities:
In the session manager, we can view the summary or all sessions related to the database, queries executed, session origin, enable trace, and end sessions, – everything we usually do via terminal using sqlplus. dbForge Studio for Oracle makes it quick and simple. You won’t need to search for scripts to extract and view information, and facilitate the daily activity of monitoring and ending sessions.
Now, let us review the most helpful tools included in the set of dbForge Studio for Oracle.
Security Manager. Controlling server security entails working with user accounts as well as managing access rights and privileges. This Oracle administration tool lets you create new user accounts and roles, update existing ones, or delete them at the global or object level. Within the account, you can grant and revoke privileges, and manipulate roles and profiles.
The dbForge Security Manager can work with multiple accounts simultaneously, check permissions, tablespace quota, roles, creation date, block, unlock, and add role schemes, and check objects and their permissions in detail.
Schema expdp and impdp – it is one of the simplest activities that every Oracle DBA is used to. Usually, the developer requests updating for the testing or approval environment, and we would connect to the database server via SSH and run the expdp and impdp utilities in the terminal. dbForge Studio for Oracle allows us to do it simply and implement several required features including compression.
Migration or import of data in different formats, such as JSON, text, CSV, Excel, Access, XML, etc.
PL/SQL coding assistant – the best support for query writing. It knows all the schemas, tables, columns, and keys. You don’t need to check or remember all the tables or objects existing in databases.
This functionality is essential for database developers, BI analysts, system analysts, or database administrators – anyone who needs to produce high-quality queries. The below screen demonstrates a simple query with tables and column data types:
Results in the grid. We can generate SQL scripts, apply filters, export data, generate reports, copy data to CSV, XML, HTML, and JSON formats, paginate the results, etc.
The following screen demonstrates the query results and informs about the execution time, production type connection (in red), the IP address, schema, and type of information format projected in data.
Execution plan generation. Not every professional who works with Oracle is a DBA. Many developers or system analysts need to evaluate the execution plan to verify query performance before actually running it. This functionality is available in dbForge Studio for Oracle – it only takes a few mouse clicks.
The screen shows the query execution plan in DBA_OBJECTS – you can see how much information it delivers. You won’t need to connect via SSH using the sqlplus text formatting.
Documentation. We all know how important but tedious it is to document procedures or databases. It is especially complicated when we are not yet familiar with the environment. dbForge Studio for Oracle generates the database documentation simply. It reports all objects that make up the database and exports the results to PDF. You can generate detailed documents in a few clicks.
Here we generate the DBSNMP schema documentation with all objects:
Schema and data comparison. This feature I like most about dbForge Studio for Oracle. I always use it to compare development, approval, and QA environments. Never worked with a tool as practical, simple, and accurate as dbForge Studio for Oracle.
See the screens where I compare the USR_SOURCE and USR_TARGET schemas. The TB_EMPLOYEE table does not exist in one schema. The Studio generates the DDL to synchronize them manually or automatically.
Besides comparing databases, tables, or any object in a granular way, it allows us to generate documentation that differs between environments. Whenever a developer forgets to apply or leaves the DEV and HML environments the same in development, I turn to this option. Otherwise, it would be unfeasible to analyze all objects and their source codes one by one in all databases or schemas.
Report generation for the environment comparison in HTML format. We often lose control over the creation and modification of databases during a system update. If problems occur, we are blind without knowing the previous state of objects. I always suggest using this tool to document all objects before the update or migration.
After the update’s successful completion (or failure), we can detect which objects could be affected at once. The report contains details of objects and actions in the comparison. You can see the complete information about the synchronized objects in the source and destination. Check the added, removed, and modified entities.
Search for objects. This feature makes life easier for all database developers and users. The usual way to query DBA_OBJECTS becomes simpler. With dbForge Studio for Oracle, we can search for any object in a specific schema or the whole database visualizing the content of each object.
The screen shows the search results for the TB_EMPLOYEE table in the USR_SOURCE and USR_TARGE schemas. It provides details about the object name, owner, schema, object type, and DDL.
Checking for invalid objects. This functionality lets us view invalid objects to compile, correct them, or document the environment. There is also an option to export to CSV:
Oracle PL/SQL Debugger. This feature allows you to debug PL/SQL code step by step effectively. You can use various Oracle PL/SQL debugging techniques, such as setting breakpoints, viewing clocks, examining the call stack, and evaluating the variables.
It helps us identify and eliminate runtime code bugs and prevents developers from developing complicated and error-prone applications. This Oracle PL/SQL Debugger lets you easily control the flow of the debugging process.
In conclusion, these features are the most favored for me. I use them all the time to reach the goals I described in the beginning. They help me work productively and save my energy to achieve more.
Besides, there are lots of options ensuring great flexibility of work. You can adjust all processes for your needs and environments and automate routine tasks.
The only downside is the price of the license, mainly for the Brazilian market where the dollar is still very high.
About this article author:
Angelo César Santos De Carvalho is a highly competent Database Administrator with extensive expertise in Oracle, SQL Server, PostgreSQL, and MySQL. His exceptional contributions to the Oracle technology community have earned him the prestigious title of Oracle ACE. With a proven track record of successfully delivering mission-critical projects, Mr. Carvalho excels in managing and tuning complex database systems, ensuring their optimal performance and reliability.
Leave a Reply