f you look at the Oracle productivity and Oracle database tools currently available on the market, you’ll see that some are primarily aimed at developers and others at DBAs.
In fact, you won’t find the word “developer” or “DBA“ in any of our product names. With KeepTool Professional, no add-ons are needed to perform key DBA functions. Our core tool, Hora, provides both the developer and the administrator with a wealth of ways to view and manipulate the database – without the need for a tutorial. Once you’ve experienced a few of Hora’s pages, you’ll find that the rest are surprisingly similar, as they all share the same three-pronged approach to the task at hand: Overview, Drill Down, and Build.
Packaged with Hora are complementary tools for automating the generation of documentation and DDL for all or selected objects belonging to a user – and a highly customizable SQL editor that can be used offline and associated with any file types you specify. Check the market and you will be hard pressed to find another software that offers so much functionality for the price.
Hora is our flagship tool for Oracle databases. The navigator on the left provides access to the various Hora tool pages. It is divided into the fixed groups ‘Standard’, ‘Additional’, ‘DBA’ and another customizable group ‘Favorites’. There are tool pages such as a SQL developer’s scratchpad, a data content browser, or management tools for some type of database object such as tables and views or PL/SQL objects. Most Hora page layouts include an overview data grid that displays the main object (i.e. tables) including its metadata. Subsequent detail tabs display subordinate objects such as table columns, constraints, indexes, triggers, and so on. Each data grid has a context menu that provides operations for maintaining the displayed object and includes operations such as create, alter, drop, and show DDL.
The DBA group provides tools for database administration such as Oracle user and privilege management, tablespace management, Oracle database session monitoring, and more. Users without DBA privileges can still open the tools in read-only mode if they are granted a role that bundles select privileges on some Oracle Data Dictionary views.
The SQL Scratchpad allows you to organize your SQL statements in a hierarchical repository. You can include multiple SQL root directories in the tree view and share each with members of your project team.
Each tree node is associated with an SQL file, which is displayed in the right pane of the editor. SQL statements can be executed either in batch mode or iteratively, with the SQL result set displayed in a dockable output window. Another output window captures the dbms_output text.
The DB object browser and code snippets on the right side of the editor window allow you to drag and drop items into the code window.
From the toolbar, you can open an Explain Plan window to view the SQL execution plan, run Oracle’s PL/SQL profiler, or open the Query Builder, which can produce either traditional or ANSI join syntax.
The result set can be exported to MS Excel spreadsheet and various other data formats.
There is a button that allows you to execute your own PL/SQL code under the supervision of Oracle’s PL/SQL Profiler. KeepTool displays the execution metrics next to the corresponding line of source code. This allows you to analyze how often lines of code were executed and how much time was spent on them.
The Data Content Browser makes it easy to browse, filter, and update table and view data. Double-click a table or view on the left to open a tab in the Data Content pane and display table data in a grid view. You can switch to a record view, tree view, pivot view, or chart view to choose a different type of data display. For foreign key column values, tooltips show you the referenced master record. For editing, you can use the ellipsis button in the data cell to open a popup that allows you to grab a reference from the master table.
The filter box above the grid allows you to enter SQL-where conditions and filter the data content. Alternatively, you can use QBE (query by example) technologies and enter filter values in the filter cells below each grid column header. To explore master/detail relationships, you can either use the context menu to open a filtered master or detail view on another tab, or open a detail grid directly below the master data grid.
The data content can be exported to an MS Excel spreadsheet and several other data formats.
This page allows you to view and manage the structure and contents of Oracle tables and views. The Overview tab lists tables or views, including table comments and other metadata. Use the subsequent tabs to drill down into various types of detailed information such as table columns, constraints, indexes, partitions, etc. The context menu on each data grid provides contextual operations to add or modify table details and more.
The Master/Detail tab displays a subset of a virtual ER diagram with only one level of master and detail tables. Double-click a related table to navigate to that table in the overview grid.
The Data Contents tab provides a data view for the selected table similar to the Data Contents Browser, including the option to display data as a master/detail view.
For performance optimization, you can find column statistical information on its own tab.
This page displays information relevant to the database schema. The first page provides a summary of the health of the schema, including statistics on invalid or erroneous objects, disabled or unusable objects, tablespace usage, and grant recipients.
The next tab shows a pivot view of the schema’s objects, grantors (i.e., users and roles), and granted privileges. The background color indicates whether synonyms exist.
Subsequent tabs show a diagram of dependencies to other schemas, a calendar view of recent changes to schema objects, policy functions, and more detailed views for invalid objects, PL/SQL errors, unvalidated constraints, and unusable indexes. There are also tabs for managing objects in the trash and another tab for managing spatial columns.
To code your CREATE OR REPLACE statement, you can start from a built-in template (which can be further customized to meet your company’s standards) or by copying an existing definition. Syntax errors are highlighted and identified by line number, making them easy to fix and retry.
When you’re ready to test, Hora provides a skeletal call block for each procedure or function, with default values for the required parameters.
When you’re done developing and testing, you’re just a click away from viewing your objects or those of another user. The data displayed for each PL/SQL object includes synonyms and privileges, which you can easily create or modify from the context menu.
If you’ve created a view on several joined tables and now want to make it updatable so that the view’s column names can be referenced for inserts, updates and deletes on the underlying table, you can create an INSTEAD OF trigger that converts DML on the view to DML on the base tables.
ora provides a wizard that guides you through building the required PL/SQL in a 4-step process:
No need to worry about column names, key columns and their relationship – just provide the essentials.
The overview grid displays used and free space, space management parameters and statistics. Below this is a subset of database parameters related to Oracle tablespace management. Subsequent tabs display detailed information for the currently selected tablespace, such as data files including segments and fragmentation, tablespace usage by owner, quota and temp/sort segment usage.
For Oracle tablespaces and data files, you can see the current usage as a pie chart.
The context menus of the data grids as well as the toolbar provide operations such as Show DDL, CREATE TABLESPACE, ALTER TABLESPACE, ADD DATAFILE, RESIZE DATAFILE, MOVE TABLESPACE for segments and much more.
Often this is due to changes in their underlying privileges as a database user, and as a result, second-line support is called in to look at the database for an answer.
If a user can update a table that another user can’t, the reason may not be readily apparent by querying table privileges, because in most cases privileges are granted through roles.
This image is taken from Hora’s database page. The tilde (~) indicates object privileges granted through roles. By selecting another user, it is easy to see if that user has access to a particular object, either directly or through a role. A similar view on the Users page shows all roles granted to or by a particular user.
This page allows you to manage Oracle pluggable database and database containers (CDBs). The overview grid displays your PDBs. If you are logged in to the root database, you can see and manage all PDBs, including the root CDB. The context menu that opens when you right-click on the CDB grid allows you to open a PDB, close a PDB, save the PDB state (to automatically restart the PDB with the root), create a PDB, or clone a PDB.
Both the Users and Tablespaces tabs display a pivot view that shows the schema owners or tablespaces for each Oracle container database. You can multi-select one or more rectangles of selected pivot cells, and the data grid below displays detailed information for all selected users or tablespaces and Oracle container databases.
The last tab lists the properties of your database containers.
On the Overview tab, you’ll find basic information such as SID, user, application, and client machine for each current session. A separate panel lists the current SQL statement for the highlighted row. A context menu also allows authorized users to kill a session, enable SQL Trace, or move a session to a different consumer group.
Additional tabs drill down into other details of the selected session, such as locks, latches, accessed objects, I/O statistics, open cursors…and more, giving the DBA quick access to the information they need without having to find and run queries against the Data Dictionary views.
As with all of Hora’s grid views, several powerful filtering options are available. For example, if you want to see only sessions with open transactions, it’s easy.
As a DBA, you’ve probably never had to answer a question by consulting a data dictionary view whose exact name you may have forgotten. For other users, KeepTool’s Data Dictionary view can help.
When you first open the Data Dictionary, it asks you to expand a tree view by prefix: USER_, DBA_ or V$, for example. Highlight the one you’re interested in, then click the Columns or Data Browser tab.
In a particularly large table such as DBA_TABLES, you can then filter on any of the columns. Say you’re not sure of the owner or the exact table name-in that case, just filter on a LIKE expression.
Although the Data Dictionary Viewer appears on Hora’s DBA page, it is not just for DBAs. Granting the HORA_USER role gives SELECT privileges on all DBA_, GV$ and V$ views, so your developers can see the dictionary.
Reverse DB Engineer creates the complete DDL for all objects of a database schema or any subset. You can run Reverse DB in batch mode to periodically back up your database structure or to check the script into a source code version control system.
The DDL statements for database objects are ordered by dependency. This avoids errors that might occur if a referenced object has not yet been created.
Optionally, you can exclude tablespace clauses and storage parameters, for example, if you want to compare the DDL generated from two different databases, such as a development database and a production system. For sequences, you can choose to start with 1 or the original start value. Use the latter choice if you plan to perform a data pump import into the existing structure.
If necessary, the tool also allows you to script a subset of your data. Large inserts are split into multiple inserts after reaching a customizable file size.
Our SQL Editor is a powerful text editor for SQL and Oracle PL/SQL with syntax highlighting and the ability to execute single SQL commands or batch SQL scripts.
The tool can work as your default SQL file editor if you tell Windows to replace Notepad. In disconnected mode you can edit your SQL scripts, drag and drop customizable code snippets into the text area and quickly switch between all files in the same directory. A button lets you open a tool to visually compare SQL files.
When connected to the Oracle database, you can drag and drop table names, column names, package or procedure names, parameters, etc. from the DB Object Browser into the code area. You can interactively execute SQL queries and optionally request a live result set that allows you to edit data in the grid. Use the Batch Execute button to run the entire script or a selected subset at once. Oracle error messages and dbms_output are displayed in the output window. Use the Explain Plan dialog to analyze Oracle SQL performance.
If your Oracle project teams use any type of HTML platform, such as a corporate intranet or wiki, to maintain ongoing documentation for applications that change over time, you will find that KeepTool Professional’s Documentation Generator can save you a lot of manual work.
The generator creates an HTML page that lists all metadata for a schema’s objects, including tables, views and PL/SQL program units. Tables and views list all columns and their attributes, constraints, indexes and triggers, along with any comments that have been defined. DDL is included for views, as well as source code for PL/SQL objects. Hyperlinks connect related objects.
View the result in your browser, explore the options, save it, and you’re done!
