Kailasnath Awati

Subscribe to Kailasnath Awati: eMailAlertsEmail Alerts
Get Kailasnath Awati: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: ColdFusion on Ulitzer

CFDJ: Article

A ColdFusion Based Oracle Database Monitor

A ColdFusion Based Oracle Database Monitor

ColdFusion offers developers an easy way to Web-enable client/server applications. This fact has been noted and written about quite often in this journal (see, for instance, Jerry Bradenbaugh's article in the January 1999 issue of CFDJ [Vol.1, Issue 1]). Putting an application on the Web saves you the pain of client-side software installation and maintenance. Besides, the application then becomes truly portable, as it can be accessed from any browser anywhere in the world (at least in theory).

There are several excellent client-server database administration (DBA) tools available on the market - DesktopDBA by the erstwhile Platinum Technologies and DBArtisan by Embarcadero Technologies are just two examples that come to mind. We would like to show you how to build a simple Oracle database monitor (hereby dubbed OraFusion), armed with only a basic knowledge of ColdFusion and a casual acquaintance with the Oracle data dictionary.

Commercially available DBA tools go well beyond providing system and session-monitoring functionality. Most important, such tools have features that allow you to manipulate all kinds of database objects - tables, procedures, indexes. For simplicity, the scaled-down version of OraFusion that we discuss does not include the ability to view and manipulate all possible database objects. In addition to a system and session monitor, our miniversion will include only the ability to extract table/view information from the database and to write and execute your own SQL. However, you will see that it is possible to extend the application to include whatever functionality you may require. For us it is an ongoing project - we add features to it as and when we find the time!

Oracle-Related Issues
Oracle is a popular database of choice for mission-critical applications. Judging from the ever-increasing number of Oracle-related posts on the ColdFusion Forum (http://forums.allaire.com), it is evident that several Oracle installations are using the power of ColdFusion to Web-enable their applications. Now, unlike ColdFusion, it takes a while to become familiar with using the advanced features of the Oracle database server. Fortunately, the Oracle features we need to use can be conveniently discussed in a few paragraphs. We do this below.

Connectivity with ColdFusion
We won't go into the details of how to set up and test the connectivity between ColdFusion and Oracle, as this has been amply discussed in the documentation (see the Allaire Web site for details). However, a few remarks regarding connectivity are perhaps in order. A ColdFusion server can establish a connection to an Oracle database via ODBC or a native driver. The latter is available only with the enterprise edition of ColdFusion. An obvious requirement is that our database monitor should work with either type of driver. The difference between the two is generally an issue only when you invoke stored procedures or use Oracle-specific features within your templates. We will avoid using such features, so our database monitor should work with either type of driver. We have successfully tested the code provided with both types of drivers. (The code listings for this article can be found on the CFDJ Web site, www.sys-con.com/coldfusion/sourcec.cfm.)

Oracle Data Dictionary and Dynamic Views
Oracle stores information about the data and structures in the database (metadata) in a set of special tables collectively known as the data dictionary. The information in the data dictionary can be accessed through a set of views that organize the information in useful ways (note that these views may vary from one version of Oracle to the next).

These views are broadly classified into three categories: USER_ views, ALL_ views and DBA_ views. An example of each of these views is: USER_TABLES, ALL_TABLES and DBA_TABLES. Any user who is allowed to create an Oracle session (i.e., log on to the database) has access to the USER_ and ALL_ views (see Table 1). As the name suggests, the DBA_ views are accessible only to users with DBA privileges. A user querying the USER_ views gets information about objects owned by him or her. The ALL_ views return information on all objects accessible to the user - that is, the objects owned by the user and those objects to which the user has been granted access. The DBA_ views contain information on all database objects. Additionally, some DBA_ views provide more detailed information than the corresponding USER_ and ALL_ views.

The views described in the previous paragraph contain information about database objects. There is another ex-tremely useful set of database instance level views that allow you to monitor database activity and performance. These views, which usually start with the characters V$ (V$PARAMETER, V$SYSSTAT for example), can only be accessed by users with the system privilege "select any table" or with DBA privileges (which would generally include the "select any table" privilege). These views are appropriately termed dynamic views, since they reflect the state of the database up to and at a given time. We will query some of these views to obtain system and session information in OraFusion. See Table 2 for a list of V$ views that we will use in our application.

Application Security
Not all users should have access to all features of OraFusion. Clearly we would want only users with DBA privileges to view system and session information. In contrast, we would want ordinary users to see all the tables and views that they have access to. Fortunately we don't really need to do anything special to implement security, as Oracle ensures that a user will get to see only the data that he or she is authorized to.

  1. We will use ColdFusion's exception handling mechanism to trap application and database errors. This involves enclosing the relevant code within a matching pair of CFTRY tags, and using CFTHROW to throw custom application errors within the code, as appropriate. Database errors are thrown automatically by the ColdFusion server as and when it receives an error message from the database. This convenient mechanism allows us to treat application and database errors in a consistent way.
  2. Users should be able to return to the main menu or the previous page, or log out of the application from any application page. This is most easily implemented by "cfincluding" a file with the required buttons or a toolbar. Note: All "cfincluded" files are in the final listing of this article (see Listing 17).
  3. For reasons of space the code listings provided do not include any HTML code for formatting and display as in the screenshots shown in the article. You can view more screenshots of OraFusion at www.orafusion.com/cfapps.htm.
Now with these preliminaries discussed, let's move on to a discussion of the individual code templates.

User Authentication
The first thing we need is a login system to authenticate users. This consists of a login form and its action template. The login form requires users to input their database user ID, password and the ColdFusion datasource that they want to connect to (see Figure 1). See Listing 1 for code details. We would like to draw your attention to one point in the code: you could make life easy for your users by using the CFREGISTRY to retrieve the names of all data sources that have been registered with the ColdFusion administrator. These could then be displayed in a drop-down select box. However, for security reasons it is probably better not to do this.

In the action page to the login template (see Listing 2) we check inputs and then attempt to run a simple query that should be executable by any user who can log on to Oracle. Note that any query on any of the USER_ or ALL_ views will do, as most Oracle installations automatically grant SELECT privileges on these views to users with the "create session" privilege - the basic privilege required for a user to be able to log on to the Oracle server. If the query is executed successfully, we set session variables to track user and datasource information. Once the user makes it past the checks, we display the contents of the main menu (see Figure 2). If any of the above steps in the authentication process fail, we catch the relevant application or database error and offer the user an opportunity to log in again.

Once a user is logged in, we have to keep track of session timeouts. This is done via an include at the start of every template (except the login and its action template), which checks for the existence of the session variables set above (see Listing 17). This mechanism also serves to catch users who jump directly to a template within the application without logging in first. Note that Oracle provides ways of centrally administering the amount of time a database session can remain inactive before killing the session. It is up to the developer to pick the most appropriate method for controlling session activity.

Finally, our users should be able to log out of the application from any page. This is best implemented as a button or link on every template. On clicking the element the user is sent to the logout template (see Listing 3). In this template we simply loop through the session structure, deleting session variables as we go along. We then display an informational message along with a button that allows the user to go back to the login page.

System Monitor
The session monitor menu template (see Listing 4) begins with three queries that retrieve current database and summary information for the system global area or SGA (essentially the memory being used by Oracle on the server where it is running). The rest of the template simply displays the retrieved information along with a menu with hyperlinks to the following system detail options (see Figure 3):

  1. Initialization parameters: A complete display of current database initialization parameters (see Listing 5 and Figure 4)
  2. Detailed SGA allocation: A breakdown of the SGA allocation (see Listing 6)
  3. System statistics: A list of database statistics since the time the instance was started (see Listing 7)
  4. System waits: A list of "wait events" with total and average wait times for each; a wait associated with a resource occurs when two or more process are in contention for that resource (see Listing 8)

Each of the queries in Listings 5 through 8 go against V$ views. We do not know whether a user has permissions to query these views, as our login procedure does not check for that. However, this doesn't matter because Oracle will throw us an error if the user doesn't have the appropriate permissions, and we already have a mechanism to pass the bad news back to the user. If we want to, we can even trap specific Oracle errors such as ORA-00942: Table or view does not exist (usually indicating that the user does not have appropriate privileges).

Session Monitor
The session monitor consists of two templates - one showing an overview of all current sessions on the database with a drilldown to individual session details (see Listing 9 and Figure 5) and the other showing session details (see Listing 10). Again, the information displayed in these templates is extracted by querying the appropriate V$ views - the relevant ones begin with the characters V$SES. The drilldown to session detail is implemented as a hyperlink; the session ID is passed as a URL parameter.

Object Information
Our scaled-down version of OraFusion allows users to view table and view data and information. Tables and views are quite similar so we can treat them using the same templates, using a session variable to keep track of which of the two we are dealing with.

The implementation of the table/view management options is shown in Listings 11 through 14.

Listing 11 displays a menu of available options. Here we implement only two options - table/view data and table/view information. The first option allows users to see all the data in the table and the second displays a list of table columns (with data types and other information) and constraints. The latter option can be extended to show even more detailed information - storage and statistics, for example.

On making a selection from the table/view menu, the user is taken to a page that displays a complete list of tables/views that are accessible to the user (see Listing 12). This list is obtained by querying the ALL_TABLES view (remember that the ALL_ views contain information of all objects accessible by the user). A short JavaScript function ensures that the user does select a table or view before proceeding to the next template.

Listing 13 is the template for table/view data display. There is one point here that's perhaps worth mentioning. We don't know the column names until the user selects a table. So the display template has to evaluate column names and data values on the fly. This is conveniently done using the variable queryname.columnlist that ColdFusion creates with every query. A note of caution: our implementation will display all data in the table, which can be a dangerous proposition when a large number of records are returned. There are several ways to deal with this but we won't pursue them here.

Finally, Listing 14 is the template for table/view information display. Column information for tables and views is obtained by querying the view ALL_TAB_COLUMNS. For tables, constraint information is obtained from the view ALL_CONSTRAINTS and ALL_CONS_COLUMNS (see Figure 6). For views, the SQL text is obtained by querying ALL_VIEWS.

Custom SQL
The final piece of our application is a feature that allows users to write and execute their own SQL on the Oracle server. The first template (see Listing 15) is a simple form with a text area in which users can compose their SQL. The action template (Listing 16) simply executes the SQL, and then displays the data (using the same technique as in the table data display template) if the statement was a SELECT, or an informational message otherwise. We could check for various types of DDL/ DML statements using ColdFusion's rich set of string search functions, and then display a more informative message. Notice, again, that we do not bother with any error checking within ColdFusion, since the database will return an error if the statement does not make sense, or violates any access permissions.

Wrapping Up
We hope we have shown you that building a basic database monitor using ColdFusion is really quite easy. It is also a good way to learn a bit about the Oracle data dictionary. This article discusses a simple implementation, leaving open several avenues for extending the application. Some of the more obvious extensions as:

  1. Add more object management features such as Users, Roles, Procedures and Functions.
  2. Allow the user to manipulate (ALTER, DROP, for example) objects in addition to displaying object information.
  3. Add performance monitoring features.
In closing, we cannot resist making a final remark on the more general theme of this article. We have described a concrete example of how developers can Web-enable client/server applications using ColdFusion - further possibilities are indeed limitless. However, the lack of a runtime version (or pricing) for the ColdFusion server makes it difficult for developers to shrink-wrap their applications. Hopefully Allaire will address this issue in the near future.

More Stories By Kailasnath Awati

Kailasnath Awati is senior consultant at Williams & Partner
Management Consulting where he manages the U.S. operations. He is an
experienced Internet/database application developer and architect as
well as an Allaire certified ColdFusion developer.

More Stories By Mario Techera

Mario Techera specializes in Internet application design and
development with relational databases. He is one of the founding
partners of Williams & Partner Management Consulting and works out of
Munich, Germany.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.