O'Reilly logo

Oracle and Open Source by Sean Hull, Andy Duncan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

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

  2. Unpack the Oddis tarball:

    $ gzip -d oddis-2.11.tar.gz
    $ tar xvf oddis-2.11.tar
    $ cd oddis-2.11
  3. 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
  4. In case of display difficulty, you may also want to point the appropriate environment variable at your current screen:

    $ DISPLAY=:0.0
    $ export DISPLAY
  5. 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
  6. 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
  7. 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.

The initial Oddis login screen

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.

SQL reporting usage via Oddis

Figure 4-20. SQL reporting usage via Oddis

Running saved SQL files with 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.

Writing LONG columns into the database via Oddis

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
Column drill-down information

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.

Database object searching facility

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.

Rollback segments under Oddis

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.)

Analyze option via the Optimize menu

Figure 4-26. Analyze option via the Optimize menu

Extensive EXPLAIN PLAN functionality within Oddis

Figure 4-27. Extensive EXPLAIN PLAN functionality within Oddis

Graphical tuning with Oddis

Figure 4-28. Graphical tuning with Oddis

Help information with Oddis

Figure 4-29. Help information with Oddis

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required