Includes additional tools for Oracle database comparison, ER diagramming and PL/SQL debugging.
KeepTool Enterprise adds three more tools, ER Diagrammer, PL/SQL Debugger and DB Compare to address some special needs — beyond what is included in KeepTool Professional.
For example, from time to time, you may need a diagnostic tool to help you see where your PL/SQL code is failing (PL/SQL Debugger). Or you may find a graphic representation of the table relationships within a newly developed project to be a useful part of your documentation (ER Diagrammer).
Using our newest addition to our suite, DB Compare, you can always make sure your test and production databases have the same structure. DB Compare not only checks to ensure that you have identical objects in both places, but makes it easy to execute the DDL statements to consolidate differences.
KeepTool Enterprise is also ideal for independent consultants who need to quickly understand and troubleshoot a client’s database.
KeepTool Enterprise new DB Compare prompts you to log in–twice. Then, without further prompting, it compares each of the two user’s objects to the other’s.
The initial display clearly identifies items found in the second schema but not the first. These are labelled “added.” Items in the first but not the second are considered “removed.”
The third category, as you might expect, is “changed”–objects present in both schemas with differences in their DDL. When you highlight one of these, the DDL for each is displayed side by side.
Besides detailing the differences, a generated draft script helps you to consolidate both structures through a series of DROP and CREATE statements. For comparing a database that is not accessible through your company’s network use Reverse/DB to script the remote database and compare as a local copy.
If you need to quickly understand the relationships between tables in a schema, ER Diagrammer presents those to you in a concise, easy-to-understand format after scanning the table definitions and foreign-key constraints in the data dictionary.
Independent tables are shown to the left of the diagram, with the level of dependencies increasing toward the right side. A slider bar immediately lets you zoom in on any portion of a complex schema. A subset function lets you generate and save a new diagram around a selected table and any of its neighbors.
Save the result as a file that you can share with your project team. If your database changes, re-open the file, rerun the scan, and your documentation is immediately brought up-to-date.
Software developers know that there’s only so much that the eye–or “another pair of eyes”–can detect when testing does not yield the expected results. Conditions that seem like they should be true may not be interpreted as we would expect. Variables may not contain the values that we are sure should be there.
A good debugging tool should be able to show logic paths and the variables at any point in time, in an easy to follow-way, without a lot of setup effort on the developer’s part.
KeepTool’s PL/SQL Debugger makes setup a snap by providing a skeleton call block that asks only for calling parameters to be populated. Then you can either step through your code line by line, or skip ahead to breakpoints. If you missed something, you will automatically be placed back at the top with the opportunity to watch a few more variables or to stop at different places in the flow.
Need to examine routines that aren’t normally run? It’s a simple matter to make a condition happen, or test an exception handler by altering the value of a variable. No need to force a routine to run by “temporarily” altering your code. The Debugger handles these situations with ease.
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.
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.
Instead-Of Trigger Wizard
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:
- Specify whether INSERT, UPDATE and DELETE or any subset of DML statements should be built.
- Associate each should-be-updatable view column with a base table column. Hora makes a suggestion that is a good guess in most cases .
- Apply any required customization, such as functions on the view’s columns.
- Execute the DDL to create the triggers.
No need to worry about the details of syntax — just supply the essentials.
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.
HTML Documentation Generator
Particularly, 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. The report embeds syntax-highlighted source code for views and PL/SQL specification. Hyperlinks connect related objects.
View the result in your browser, explore the options, save it, and you’re done!
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.