When you look at the Oracle productivity and Oracle database tools currently on the market, you’ll see some primarily oriented toward developers, and others toward DBAs.
This is exactly what makes KeepTool different. 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, offers both the developer and the administrator a wealth of ways to view and manipulate the database — without the up-front need for a tutorial. Once they’ve experienced a few of Hora’s pages, they’ll find the rest to be surprisingly similar, since each features the same three-fold approach to the task at hand: Overview, Drill-down, and Build.
Packaged with Hora are complementary tools to automate the generation of documentation and DDL for all or selected objects belonging to a user — and an extremely customizable SQL Editor that can be be used even offline and associated with any file types that you specify. Check out the market, and you will be hard-pressed to find any other 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 variety of Hora’s tool pages. It is organized into the fixed groups ‘Standard’, Additional’, ‘DBA’ and another customizable ‘Favorites’ group. There are tool pages such as a SQL developer’s scratchpad, a data content browser, or management tools for some kind of database object like 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 show subordinate objects such as table columns, constraints, indexes, triggers, and so on. Each data grid has a context menu that offers operations for maintaining the displayed object and includes operations like ‘create’, ‘alter’, ‘drop’ and ‘show DDL’.
The DBA group offers tools for database administration such as Oracle user and privilege management, tablespace management, Oracle database session monitoring, and much more. Users lacking DBA privileges can still open the tools in read-only mode if being 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 the members of your project team.
Each tree node is assigned to a SQL file that is displayed in the editor window on the right. SQL statements can be executed either in batch mode or iteractively, whereas the SQL resultset is displayed in a dockable output window. Another output window captures dbms_output text.
The DB object browser and the code snippets in the right of the editor window allow you to drag-drop items into the code window.
Via the tool bar you can open an explain plan window to show the SQL execution plan, run Oracle’s PL/SQL profiler or open the Query Builder that can produce either traditional or ANSI join syntax.
The result set can be exported to a MS Excel spreadsheet and various other data formats.
There is a button that allows you to run your own PL/SQL code under supervision of Oracle’s PL/SQL profiler. KeepTool displays the execution metrics next to the appropriate source code line. That allows you to analyze how often source code lines have been executed and how much time has been spent.
The Data Content Browser allows you to easily 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 area and show table data in a grid view. You can change to a record view, tree view, pivot view or chart view to choose another kind of data representation. 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 pickup a reference from the master table.
The filter box above the grid allows you to enter SQL where conditions and filter the data content. As an alternative, you can use QBE (query by example) technologies and enter filter values in the filter cells below each grid column header. For exploring master/detail-dependencies, you can either open a filtered master oder detail data view on another tab by using the context menu, or open a detail grid right below the master data grid.
The data content can be exported to a MS Excel spreadsheet and various other data formats.
This page allows you to view and manage the structure and content of Oracle tables and views. The overview tab lists tables respectively views including table comments and other metadata. Use the subsequent tabs to dive into different kind of detail information such as table columns, constraints, indexes, partitions, etc. The context menu on each data grid offers context operations to add or alter table details and much more.
The master/detail tab displays a subset of a virtual ER diagram including just one level of master and detail tables. Double-click a related table to navigate the overview grid to that table.
The data content tab provides for the selected table a similar data view as the data content browser including the option to show data as a master/detail view.
For performance optimization, you can find column statistics information on its own tab.
This page shows information relevant for the database schema. The first page gives you a summary of schema health including statistics regarding invalid or erroneous objects, disabled or unusable objects, tablespace utilization and recipients of grants.
The next tab shows a pivot view of the objects of the schema, grantees (i.e., users and roles) and granted privileges. The background color gives you a clue whether synonyms exist.
Subsequent tabs show a diagram of dependencies to other schemas, a calendar view of recent changes to objects of the schema, policy functions, and more detailed views for invalid objects, PL/SQL errors, not validated constraints, unusable indexes. Furthermore, there are tabs for managing objects in the recycle bin and another tab for managing spatial columns.
To code your CREATE OR REPLACE statement, you can start from a built-in template (further customizable to 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 furnishes a skeleton calling block for each procedure or function, with default values for the required parameters.
After developing and testing, you’re just a click away from viewing your objects or another user’s. 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 various 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 turns DML on the view into DML on the base tables.
Hora provides a wizard to walk you through building the required PL/SQL in a 4-step process:
No need about column names, key columns and their relation – just supply the essentials.
This page allows you to manage Oracle tablespaces and data files. The overview grid displays used and free space, extent management parameters and statistics. Below you see a subset of database parameters that is related to Oracle tablespace management. Subsequent tabs show detail information for the currently selected tablespace such as data files including segments and fragmentation, tablespace utilization by owner, quota, and temp / sort segment usage.
For Oracle tablespaces and data files you can see the current utilization as a pie diagram.
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.
Corporate Help Desks are often confronted with calls from users who can’t view or update data that their co-workers can. Often, this stems from changes to their underlying privileges as a database user, and as a result, second-line support is called upon to look at the database for an answer.
If a user can update a table that another user can’t, the reason may not readily be apparent by querying table privileges, because in most cases privileges are granted through roles.
This picture is taken from Hora’s Database page. The tilde (~) indicates object privileges that have been granted through roles. By selecting a different user, it becomes easy to see if that user has any access to a particular object, either directly or through a role. A similar view on the Users page shows all roles that have been granted to or by a particular user.
This page allows you to manage Oracle pluggable database and database containers (CDB). The overview grid shows your PDBs. If you are logged in the root database, you can see and manage all PDBs including the root CDB. The context menu that opens when you right-click the CDB grid allows you to open a PDB, close a PDB, save the PDB state (to restart PDB automatically with the root), create a PDB or clone a PDB.
Both users and tablespace tabs show a pivot view that displays schema owners respectively tablespaces for each Oracle container database. You can multi-select one ore more rectangles of checked pivot cells, and the data grid below will display detail information for all selected users or tablespaces and Oracle container databases.
The last tab lists properties of your database containers.
On the Overview tab, you’ll find essential information such as SID, user, application, 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, turn SQL Trace on, or move a session to a different consumer group.
Additional tabs drill down on other details of the selected session, such as locks, latches, accessed objects, I/O statistics, open cursors…and more, allowing the DBA quick access to needed information without the need to find and run queries against the data dictionary views.
As is the case with all of Hora’s grid views, several powerful filtering options are available. If, for example, you’d like see only the sessions with open transactions, it’s quite simple.
As a DBA, you’ve never had a moment where you’ve needed 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 you out.
When first opened, the Data Dictionary asks you to expand a tree view by prefix: USER_, DBA_ or V$, for example. Highlight the one that you’re interested in; then click the Columns or Data Browser tab.
In a particularly large table like 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 only for DBA’s. Granting the HORA_USER role gives SELECT privileges on all the DBA_, GV$ and V$ views, allowing your developers a view of the dictionary.
The Reverse DB Engineer creates the entire DDL for all objects of a database schema or any arbitrary subset. You can call Reverse DB in batch mode for periodic backup of your database structure or to check-in the script into a source code version management system.
The DDL statements for database objects are ordered by dependency. That avoids errors that could occur if a referenced object not yet has been created.
Optionally, you can exclude tablespace clause and storage parameters, e.g., if you want to compare the DDL created from two different databases such as a development database and a production system. For sequences, you can decide 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.
When needed, the tool allows you to script a subset of your data as well. Large inserts are split into multiple after reaching a customizable file size.
Our SQL-Editor is a powerful text editor for SQL and Oracle PL/SQL and comes with syntax highlighting as well as the capability to execute single SQL commands or batch-execute 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&drop customizable code snippets into the text area, and quickly change between all files in the same directory. A button allows you to open a tool to visually campare SQL files.
If you establish a connection to the Oracle database, you can drag&drop table names, column names, package o procedure names, parameters, etc. from the DB object browser into the code area. You can run SQL queries interactively and, optionally, request a live result set that allows you to edit data in the grid. Use the batch execution button to run the entire script or any selected subset at once. Oracle error messages and dbms_output will be 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 as they change over time, you will find that KeepTool Professional’s Documentation Generator can save you a lot of manual work.
The Generator builds an HTML page listing 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 defined. DDL is included for views as is source code for PL/SQL objects is included. Hyperlinks connect related objects.
View the result in your browser, explore the options, save it, and you’re done!