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.
That central information page will direct you to the latest download,
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
$ 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
$ 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:
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):
The usual suspects, plus the ability to open SQL files stored on disk.
The most important option is the ability to change the transactional state of the program, but other options are available as well.
Allows several of the more involved SQL types of operations, including LONG data handling and PL/SQL.
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.
Drills down on system-specific details.
Provides Oddis SQL tuning options.
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.
To enable these features, the following options are available under the File menu:
Edit any SQL within the main screen and prepare it for file saving.
Pick up a previously saved SQL file and populate the main screen with it.
Save the current SQL into the currently open SQL file.
Create a new SQL saving file.
Exit the Oddis program.
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:
Available for use when AutoCommit is set to off.
Allows you to insert files into Oracle LONG columns (this process is demonstrated in Figure 4-22).
The reverse of Write Long Column.
Enables the binding of variables and the execution of PL/SQL procedures.
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.
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:
|Grants to User|
|Grants by User|
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.
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:
|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.
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.
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.
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.)