Oddis

Oddis (Oracle Data Dictionary Information System) is a Tcl/Tk-based graphical user interface for navigating visually throughout the entire Oracle data dictionary (ODD). Oddis follows in the footsteps of Tom Poindexter’s Wosql and IUD Maker programs (covered in Chapter 3), but it is a much more comprehensive Tcl/Tk Oracle application, and it provides an excellent example for aspiring Oracle open source developers. The program is based on original work by Rainer Gruetzner and is currently maintained by a team at the University of Hanover in Germany, including Michael Bethke, Axel Schlüter, and Regine Kasten, who have moved the program forward mostly via their computer science projects.

Intended primarily for DBAs, both novice and expert, Oddis aims to make the data dictionary as simple as possible to traverse, while providing the maximum amount of useful information. In addition to providing tools for Oracle DBAs, Oddis also possesses excellent facilities for storing and running SQL files as well as superb EXPLAIN PLAN functionality (including diagrammatic trees), making it potentially useful for developers too.

Oddis is far more than the data dictionary viewer it modestly claims to be. It is also a fully interactive OLTP tool, as well as a general reporting engine. It offers complete facilities for transactions, commits, rollbacks, auto-commits, and so on, in the main interface, all available via its various menus. The program provides an excellent demonstration of what’s possible using TclX and Oratcl, and it offers substantial room for expansion in the future. We can’t wait to see Oddis 3.0 and any subsequent developments. (Oddis 3.0 is rumored to be nearly ready for release and may even provide some kind of linkup with Java. Stay tuned.)

The large number of Tcl/Tk web plug-ins and other developments from Sun, as well as Oracle Corporation’s own efforts to push Oratcl (which we described in Chapter 3) make the future for Tcl/Tk Oracle applications such as Oddis very bright indeed.

The main web site for Oddis is:

ftp://www.informatik.uni-hannover.de/software/oddis/oddis-2.11.html

That central information page will direct you to the latest download, for example, oddis-2.11.tar.gz

Installing Oddis

Before you can install Oddis, you will need to install Tcl/Tk, TclX, and Oratcl on your system. These installations are described in Chapter 3. You can then install Oddis by following these steps:

Download the latest version of Oddis from the site listed at the end of the previous section.

Unpack the Oddis tarball:

$ gzip -d oddis-2.11.tar.gz
$ tar xvf oddis-2.11.tar
$ cd oddis-2.11

Make sure that the Oratcl side of the equation is happy by ensuring that the correct Oracle libraries are accessible. For example:

$ ORACLE_HOME=/u01/app/oracle/product/8.1.5
$ export ORACLE_HOME
$ LD_LIBRARY_PATH=/usr/local/lib:$ORACLE_HOME/lib
$ export LD_LIBRARY_PATH

In case of display difficulty, you may also want to point the appropriate environment variable at your current screen:
```
$ DISPLAY=:0.0
$ export DISPLAY
```
Edit the actual Oddis script to make sure you’re pointing at the correct version of the wishx TclX program on your system:
```
$ vi oddis2.11

#!/usr/local/bin/wishx
# Program: oddis_v2.0
# Tcl version: 7.4 (Tcl/Tk)
# Tk version: 4.0
```
Make sure the Tcl MODULE_PATH variable (described more fully in the README.FIRST file in the download) is set to the directory in which you’re currently running the Oddis program. This is to ensure that the correct helper Tcl scripts and indexes are picked up correctly:
```
# SET THE RIGHT MODULE_PATH HERE !!!!!
#set module_path /home/tpoindex/src/tcl/oddis-2.11
#set module_path /root/tk/oratcl-2.5/samples/oddis #
Local Oratcl
set module_path /root/tk/oratcl-2.5/oddis-2.11 # Local Download
```
You should now be ready to run the Oddis program:
```
$ ./oddis2.11
```

Using Oddis

Once you’ve installed and started up the Oddis program, you’ll see the Oddis login screen in Figure 4-19. No surprises here, but as you’ll soon see, Oddis does possess several features which we haven’t seen in the tools described so far in this book, and it also provides the usual assortment of drill-down goodies (Figure 4-20 also shows the main menus displayed in the Oddis top level menu):

File menu: The usual suspects, plus the ability to open SQL files stored on disk.

Options menu: The most important option is the ability to change the transactional state of the program, but other options are available as well.

SQL-Options menu: Allows several of the more involved SQL types of operations, including LONG data handling and PL/SQL.

User-Objects and All-Objects menus: Allow you to drill down into their respective sets of database objects (e.g., tables, sequences, views, etc.). Roles and other granted privileges can also be accessed from these paired menus.

System menu: Drills down on system-specific details.

Optimize menu: Provides Oddis SQL tuning options.

Help menu: Michael Bethke’s extended active help system.

Figure 4-19. The initial Oddis login screen

File Menu

First and foremost, the Oddis program can act as a SQL command and reporting tool, as demonstrated by a familiar selection statement’s execution in Figure 4-20. Oddis also lets you read any SQL scripts you might have in your various collections, as in Figure 4-21, and save any new ones you’ve written.

Figure 4-20. SQL reporting usage via Oddis

Figure 4-21. Running saved SQL files with Oddis

To enable these features, the following options are available under the File menu:

New: Edit any SQL within the main screen and prepare it for file saving.

Open: Pick up a previously saved SQL file and populate the main screen with it.

Save: Save the current SQL into the currently open SQL file.

Save As: Create a new SQL saving file.

Quit: Exit the Oddis program.

Tip

The “Execute” button at the right-hand side of the main menu is used to execute any SQL currently within the main screen.

Options Menu

The Options menu provides several options, mostly covering the transactional state of the program:

AutoCommit: This toggles between on and off, determining your transaction commitment status on the SQL statements executed in the main window. This feature is similar to the one available through the Orac program’s Orac Shell feature described earlier.

Font: Offers a range of font sizes for program displays.

Windows: In contrast to Orac, which is centered around one main screen holding most of the information, Oddis consists of families of windows. You can alter this default behavior with a series of menu options.

Display: Offers a display choice between color and monochrome.

SQL-Options Menu

The SQL-Options menu lets you perform a variety of SQL tasks, such as writing a LONG column from a file and executing PL/SQL procedures. As shown in Figure 4-22, you can select from the following:

Commit Now: Available for use when AutoCommit is set to off.

Rollback Now: Available for use when AutoCommit is set to off.

Write Long Column: Allows you to insert files into Oracle LONG columns (this process is demonstrated in Figure 4-22).

Read Long Column: The reverse of Write Long Column.

Procedure Execute: Enables the binding of variables and the execution of PL/SQL procedures.

User-Objects Menu and All-Objects Menu

The User-Objects menu and All-Objects menu are identical except that the User-Objects menu lets a logged-on user access only his own objects, while the All-Objects menu allows access to all the objects that user can see, either in his own schema or in other schemas. Both menus deliver a full range of options to drill down into the various database objects in a manner that’s similar to Orac’s Object menu structure.

Figure 4-22. Writing LONG columns into the database via Oddis

The general standard format of each drill-down object remains basically the same across all the different types of database objects. You can see this standard format in Figure 4-23, which covers the second option for Columns. This option gives you quick access to all of the columns within the database and shows how they’re made up. If you’re tired of typing “DESC MY_TABLE” within SQL*Plus to get hold of that table column whose dimensions you keep forgetting, you’ll be very happy with this Oddis feature. The following are the available related options on these two similar menus:

Table

Columns

Indexes

Views

Clusters

Tablespaces

Extents

Sequences

DB-Links

Synonyms

Constraints

Triggers

Procedures

Roles

Privileges

Grants to User

Grants by User

Figure 4-23. Column drill-down information

The final remaining option available within this menu structure is Find, which provides a useful search utility for tracking down those elusive objects hiding in the data dictionary. You can see this option in use in Figure 4-24. A minor difference between the User-Objects and All-Objects versions is that you can input the object owner in the latter. You also don’t necessarily have to input the correct name for each object you’re after. The standard Oracle search wildcards, such as “%”, are available too.

Figure 4-24. Database object searching facility

System Menu

The System menu allows you to examine system-wide information. It provides useful drill-downs in a way that’s similar to that of the Columns option within the User-Objects menu. It consists of the following:

User List

Tablespaces

Roles

Rollback Segments

Free Space

Data Files

Devices (provides raw operating system information such as disk partitions)

We’ve displayed a typical usage of the Rollback Segments screen in Figure 4-25.

Figure 4-25. Rollback segments under Oddis

Optimize Menu

As far as we’re concerned, the Optimize menu is the killer menu with Oddis. It has two main submenus, Analyze and Explain:

Analyze: Allows you to drill down on either tables or indexes and analyze their structure. This option is particularly useful for indexes. You can see this menu option at work in Figure 4-26.

Explain: Provides detailed information on Oracle’s EXPLAIN PLAN facility, with numerous drill-downs, as displayed in Figure 4-27.

And if that isn’t enough to whet your appetite, if you select the Show Tree option, Oddis will even draw the full execution plan in diagrammatic form, as shown in Figure 4-28.

Help Menu

The Help menu provides a variety of options, most of them making great use of Tcl/Tk’s graphical canvas abilities. We’ve demonstrated a handful of these in Figure 4-29. The “About ODDIS” window is particularly useful. This offers help drill-downs into most of the other menus and areas within the Oddis program. This help system is unlike similar-looking systems you may have come across. Michael Bethke has creatively extended Regine Kasten’s original envelope in such a way that every time you navigate through the data dictionary menus, the help function changes its state to show your next options. (We think this is a classic example of good software.)