Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Oracle Unleashed

Oracle Unleashed oun24fi.htm

Previous Page TOC Next Page Home


Repository Administration

What Is Repository Administration?

Just as there are duties required to maintain a database system, usually designated to one or more database administrators, there are also maintenance duties required to ensure the proper performance and use of the Designer/2000 repository. The repository itself is a large enough and important enough entity in a database system to require it's own level of security, general administrative maintenance, and tuning. There are four utilities in the Designer/2000 suite to provide for this administration. Theses utilities are commonly known as

These administration utilities can provide complete access to every aspect of maintaining the Designer/2000 repository internally. When assigning the duties of repository management, it's wise to separate these duties from the database administrator's duties. The person(s) performing these duties should be the more experienced designers that have an understanding of case methodologies. The duties will include two distinct types of repository administration, which are External Database issues and Internal Repository issues. The persons performing repository administration will work directly with the database administrator in working out repository installation, sizing, performance and security issues. Not to mention that the repository will provide an excellent place for the database administrator to document exactly how each instance they administrate is setup. These same tools will also be used in managing all the internal objects of the repository.

The Repository Administration Utility

As seen in the previous discussion of Designer/2000 installation, the first utility that will be used is the Repository Administration Utility. This utility will perform all installations and upgrades of the repository. It will also provide for easy recreation and backup of the repository's objects, as well as some tuning. This will also be the utility to use when providing users access to the repository and extending the capabilities of the repository to meet the needs and requirements of the application and corporate policies. The tool can only be used by the repository owner. The repository owner user-id should be independent, only for repository administration, and not a database administrator.

An attempt to use this tool as SYS, SYSTEM, or any other subordinate, no matter what their security level is, will fail.

The repository administration utility will provide a dialogue box with the following screens accessible view tabs:

Items marked with an asterisk are not available during repository installation.

There will also be a button always visible at the bottom of the dialogue box labeled Disconnect. This button will allow the termination of the current repository owner session.

Repository Management Tab

When the repository administration utility is started, this will be the first tab screen open. During the initialization process, the utility will determine if the repository already exist. Based on this determination, it will automatically set the Install or Upgrade as the active radio button. There will also be some system information at the top of the screen showing the user-id and Repository Release if an upgrade should be performed. Figure 24.1 shows the Repository Management tab in install mode, and Figure 24.2 shows the tab in upgrade mode.

Figure 24.1. Repository Management Tab screen (install mode).

Figure 24.2. Repository Management Tab screen (upgrade mode).

If performing an upgrade, the version may be displayed in two different ways. Upgrading from CASE Designer 5.0 or 5.1, or any Designer/2000 Beta or Pre-Production versions, will cause the upgrade radio button label to read TO version. Upgrading from production releases of Designer/2000 will cause the upgrade radio button label to read TO version Inter-Release.

Create Section

The create section of the screen is only available to current installed versions of Designer/2000. Choosing the re-create radio button in this section will turn off the upgrade radio button and allow the repository administrator to re-create specific objects of the repository instance to correct problems. This may be a quicker solution to fixing problems, rather than running the entire upgrade process again. The options that can be selected from the re-create drop-down list box are:

Delete Section

This section can be very dangerous! The first option here is Remove Repository Objects. This will drop all database objects created for the repository instance. The second option is Remove All Objects. This option will drop ALL objects owned by the repository owner. Again, be aware that objects will be gone after executing one of these two options.

The Remove All Objects option is available when a new install option is detected. This could be useful in ensuring the repository owner owns nothing prior to repository installation. It is recommended that the database administrator ensure that objects to be dropped are indeed dropped.

Backup Section

This section provides for repository specific backup capabilities, outside of the backups performed by the database administrator. The repository manager has the capability of exporting and importing in the following ways:

Using the last two export options will provide for objects and tables not created by the Designer/2000 product but owned by the repository owner user ID.

Tablespace Selections

There are two tablespace selection drop-down list boxes. They are included to allow the repository manager to select tablespaces other than the default tablespace setup for the repository owner. It will also allow for separation of the repository indexes from the repository tables for tuning purposes. This is highly recommended.


The buttons actually initiate actions from the repository management tab screen. The buttons initiate the following actions:

To PIN the repository packages, the repository owner must have the rights to execute the SYS.DBMS_SHARED_POOL package. This package can be loaded by your DBA via execution of the dbmspool.sql and prvtpool.sql as the user SYS.

Privileges Tab

This tab screen, as seen in Figure 24.3, is primarily a display of the necessary privileges needed to be a repository owner. If any privilege does not have X in it, the DBA will need to notified prior to installation or upgrade.

Figure 24.3. Repository Administration Privileges summary tab screen.

There are three buttons available in this tab screen also. These buttons are as follows.

If the DBA has created specific roles for repository owners and users, this can then be easily seen here.

Tablespace Tab

This tab screen, as seen in Figure 24.4, shows an overview of the tablespaces the repository owner has access to. This will be an important place for the repository manager to monitor the space requirements of the repository. The utility also identifies the repository owner's default and temporary tablespaces and user quota.

Figure 24.4. Repository Administration Tablespace Analysis tab screen.

Maintain User Tab

This tab screen, as shown in Figure 24.5, is where the internal security is managed for the Designer/2000 products. It does not correspond to database security equivalency in anyway. Thus, a user with read-only privileges here could still access the repository tables via such products as SQL*Plus.

Figure 24.5. Repository Administration Maintain User tab screen.

User Spread Table Items

The Oracle UserName can be selected via the drop-down list box. This will list all users on the database instance.

There are two Designer/2000 roles, USER and MANAGER. The USER role is the default, and gives access to all Designer/2000. The exception is that a USER cannot access the Application menu option in the Repository Object Navigator. The MANAGER role has access to all Designer/2000 tools except for Repository Administration Utility. This is only accessible by the repository owner. A MANAGER is also the only user type that can be an application system owner.

There are also two modes that can be assigned to users, READ and WRITE. The READ mode gives read-only access to the repository via the Designer/2000 tools. The WRITE mode gives full read and write access to the repository via the Designer/2000 tools.

The READ and WRITE modes only affect the Designer/2000 tools internally and do not correspond to user database privileges!

Scrolling the user spread table horizontally also shows an editable Description field. Here, the repository manager can put in some meaningful text to identify the repository user.

The final item in this spread table is the repository user creation date. This is not directly editable.

Privileges will still need to be granted for each application by the application owner. This can be done in the Repository Object Navigator under the Application | Grant Access menu option.


The buttons for this tab screen provide the following actions:

User Extensibility Tab

User extensibility allows organizations to add Element, Association, and Text Types to the repository. These extensions allow organizations to add items that are not currently included. An example would be to add a text type that identifies a contractor or project number. This text type then could be added to the Application System element type. An example of the User Extensibility tab can be seen in Figure 24.6.

Figure 24.6. User Extensibility tab screen.

Element Type

An element type would be a top level object in an application system. User extensions for the element type can have a short name of E0 - E99. Elements that come as a default for an application in Designer/2000 would be things like:

Association Type

An association type would defines the meta-model association between elements. User extensions for the association type can have a short name of A0 - A99. An example of a Designer/2000 supplied association would be such things as:

Text Type

A text type is basically any object best defined in a textual mode. These text items can be anything needed to better document your system. An example of Designer/2000 provided text items would be:

Extending the Repository

The extensions can be added in two ways, which are creating new types or modifying existing types. To add a new type:

Once an extension has been published, it cannot be edited or deleted.

The second way to extend the repository is to add new properties or text type usages to existing types. To do this either edit one of the Usrx objects and edit the properties or select the New button and select an existing type to add. Once this has been completed, publish the changes.

Once a Usrx extension has been published, only the length, precision, mandatory, and case mode fields can be changed.

UE Utilities Tab

This tab, as seen in Figure 24.7, provides three options for user extensions.

Figure 24.7. UE Utilities tab screen.

See the release notes for any problems associated with user extensions that may exist during conversion, extract or load.

The Matrix Diagrammer

The matrix diagrammer is a designer/project leader tool. It is nothing more than a repository reporting tool that will display in a GUI format or provide printed output. Per its name, the reporting done is a matrix style. Thus, there will be rows, columns and a condition to report on. These reports are used to show completion or missing items in the application design process. An example of a matrix diagram is seen in Figure 24.8.

Figure 24.8. A matrix diagram.

Matrix items for the rows and columns are in a list box of choices, and are the same initially in the new matrix dialogue box. Once something is chosen in either list box, the other list box elements will only show those items that can be reported against. The items that will be shown in the two list boxes are as follows:

When both row and column options are chosen, a settings dialogue box will be displayed. Here is where row and column display items are chosen along with any filters and ordering conditions. Next intersection properties of the row and column are chosen. After the settings and filters are completed, the matrix diagram will be generated. Once the matrix diagram is created, row or column items can be drilled down on bringing up the property sheet or the matrix result can be drilled down on bringing up its property sheet. Items in the property sheets can then be edited. The columns and rows of the matrix can be added and deleted, and three different views can be shown based on preferences set. The matrix report can also be printed out, but in some cases the matrix lines will not print.

The Repository Object Navigator

As in the previous versions of Oracle CASE Dictionary and it's Main Menu, Designer/2000 also provides a tool for viewing any portion, or the entire repository, and launching any of the supplied utilities and design tools. This tool is called the Repository Object Navigator, sometimes referred to as RON. New to this tool is the use of the hierarchy tree display method and the visual basic like property sheet, as seen in Figure 24.9.

Figure 24.9. Repository Object Navigator.


First the user will either create a new application for the repository by selecting menu option File | New, or by opening an existing application by selecting menu option File | Open.

From the initial startup of the Repository Object Navigator, the user has several choices to choose from in the Filter dialogue box, as seen in Figure 24.10. This dialogue will be displayed by default, but can be suppressed by editing the Repository Object Navigator preferences.

Figure 24.10. Repository Object Navigator Filter dialog box.

The filters shown will turn on and off the viewing of specified object types. Using these filters can help keep unnecessary information from being displayed to designers. These filters are displayed as check boxes and filter the following:

Creating New Applications

The user will be placed at the top of the hierarchy tree in the repository object navigator and prompted for the new application's name. The new application name can be up to fourteen characters in length. The name should be meaningful, and cover the entire scope of the application. At this point, the initial application properties should be completed in the property sheet even thought only the name is required.

Opening Existing Applications

The open application dialogue is displayed and will list all applications available in the repository, application versions, and related user security assigned for the application. The user can also specify read-only mode if no changes are intended to be made. The open application dialogue can be seen in Figure 24.11.

Figure 24.11. Repository Object Navigator Open Application dialog box.

If multiple applications are opened, each will have its on application window available.

Viewing and Preferences

There are two child windows that will be initially visible in the Repository Object Navigator's main window. The first is always available when an application is open, which is the Application Hierarchy Tree window. The second, the Properties Window, is optional. There are four different colors used to identify items as Normal items, Error/Mandatory items, Modified items, and Shared/Non-Editable items. These colors can be modified in the user preferences. Think of the hierarchy tree as being rows or records of information in the repository. Think of properties being the actual columns that makeup these rows.

Navigating the Hierarchy Tree

The standard look and feel of the Designer/2000 and Developer/2000 products will use a hierarchy tree were appropriate. The Repository Object Navigator's hierarchy tree can be seen in Figure 24.12. The tree can be split into to two views by using the split bar located just above the window's vertical scroll bar.

Figure 24.12. Repository Object Navigator hierarchy tree.

Tree items can be expanded one at a time by either mouse click on the preceding colored +, or by selecting the object and using the Navigator | Expand menu option. Collapsing tree branches back can be done by mouse click on the preceding -, or by selecting the object and using the Navigator | Collapse menu option.

The entire hierarchy tree can be expanded or collapsed by using the Navigator | Expand All and Navigator | Collapse All menu options.

Objects can be added to the various object types selecting the object type and using the + button on the toolbar, or by using the Edit | Create menu option. Objects can be deleted from object types by selecting the object, and by using the x button on the toolbar or using the Edit | Delete menu option.

The Property Sheet

Selecting an object on the tree will cause the object's properties to be shown in the properties window, where applicable, if the properties window is open. In the properties window, the details about the object will be displayed and can be added or modified directly here. An example property sheet can be seen in Figure 24.13.

Figure 24.13. Repository Object Navigator property sheet.

All editing will be done by selecting the property and using the supplied edit method at the top of the property sheet. The edit methods will be either a plain edit field, a list box, or an edit box with a bubble button to the right which can be selected to bring up an editor window.

A new property sheet can be opened by selecting the Window | New Property Sheet menu option. Currently, all property sheets open showing only the properties to the currently selected object. Thus, you can only compare properties using the Union/Intersect button and highlighting multiple objects.

The Toolbar

The Repository Object Navigator provides an iconic toolbar just below the menu to provide mouse shortcuts that map directly to menu items for repository actions. The tool bar can be seen in Figure 24.14.

Figure 24.14. Repository Object Navigator Toolbar.

Holding the mouse cursor above the button for a short period of time will cause bubble help dialogues to appear describing the button's action.

From left to right the buttons perform the following actions:

The Menu

The menu can provide for initiating all actions available in the Repository Object Navigator. The menu also provides a driver for initiating any of the other Designer/2000 tools.

File Menu Option

The file menu provides basic application and repository actions. The file menu options are described in Table 24.1.

Menu Item



Create a new application in current repository.


Open an existing application in current repository.


Save any application changes made to the repository.


Loads object definitions from a text file into the current application hierarchy.


Creates a text file record of all owned repository entries. Also any associations or shared entries will be recorded.

Unload for Exchange

Creates an input file for Oracle*Exchange to import repository items into third party products.

Check Out

Unloads user defined sets and the elements in them to a text file.

Check In

Loads a user defined set text file into the repository. Either updating or creating new entries.

Lock Set

Locks elements in a user defined set so they cannot be updated.

Unlock Set

Unlocks elements in a user defined set so they can be updated.

Change Connection

Log in as a new user.


Print the current hierarchy display.

Print Preview

Display what would be printed.

Print Setup

Setup print options.


Exit the Repository Object Navigator.

Edit Menu

This menu provides basic editing capabilities to the user. The edit menu options are described in Table 24.2.

Menu Item



Undoes last change made, prior to a save.


Create a new object under current position in the tree.


Delete the selected tree object.

Copy Properties

Copies selected properties to the clipboard.

Paste Properties

Pastes copied properties to the current property sheet.

View Copied Properties

Provides a dialogue box to preview properties that have been copied prior to pasting.


Specifies Repository Object Navigator Preferences.

View Menu

This menu option provides for some repository viewing preferences. The menu options are described in Table 24.3.

Menu Item



Requeries the repository for the current definitions of all objects under the currently selecting object type.

Sort by Type Sequence

Sorts the hierarchy by normal entry sequence.

Sort by Type Name

Sorts the hierarchy alphabetically.


Turns on and off the property sheet toolbar.

Status Bar

Turns on and off the Repository Object Navigator's status bar.

Navigator Menu

This menu option provides for all hierarchy navigation. The menu options are described in Table 24.4.

Menu Item



Expand the tree object one level.


Collapse the currently expanded object.

Expand All

Expand all objects under the currently selected object.

Collapse All

Collapse all objects under the currently selected object.

Set Mark

Mark the current object for later.

Go to Mark

Navigate to a previously set mark.

Go to Referenced Object

Navigate to the object identified as referenced.


Find the next occurrence of a text string in the hierarchy.

Utilities Menu

The utilities menu option provides for execution of all repository utilities. The menu options are described in Table 24.5.

Menu Item


Database Design Wizard

Starts the database design wizard utility.

Application Design Wizard

Starts the application wizard utility.

Generate DDL

Starts the Server DDL Generation utility.

Generate Module

Starts one of the Client Application Generation utilities for Oracle Forms or Oracle Reports modules.

Generate Module As

Generates Oracle Forms from report modules or Oracle Reports from form modules.

Regenerate Module

Regenerates a form module using new changes.

Generate Reference Tables

Creates text help tables for use by end users.

Reverse Engineer DDL

Starts the database reverse engineering utility.

Reverse Engineer Form

Starts the forms reverse engineering utility.

Reverse Engineer Report

Starts the reports reverse engineering utility.

Table Entity Retrofit

Creates entities for tables not linked to current entities.


Starts the Server reconciliation utility.

Create Function Attribute Matrix

Starts dialogue for creating a function/attribute matrix.

Create Default Module Data Usages

Creates module data usages either from summary usages or function usages.

Update Attributes in a Domain

Update changed attributes in a domain.

Update Columns in a Domain

Update changed columns in a domain.

Transfer Ownership

Transfers object ownership to another user.


Allows access to an object by another application.


Revokes access to an object from other applications.


Copies elements to specified applications.

Force Delete

Deletes an object and all references to it in all applications.

Application Menu

This menu option provides a means to perform application specific operations and maintenance. Requires the manager role to be set in the Repository Administration utility for the user. The menu options are described in Table 24.6.

Menu Item



Will lock/unlock an application to prevent or allow modification.


Gives a new name to the application if owned by the user.


Transfers application ownership to another user.

Grant Access

Grants, revokes and specifies user access to defined applications.

New Version

Creates a new version of the application. The previous version is frozen.


Deletes an owned application.


Archives a specified application.


Exports archive tables to a file.


Imports an exported application.


Restores an archived application.


Clears archive tables.

Tools Menu

This menu option provides an easy way to directly execute all the Designer/2000 application tools. Executing from this menu will automatically create a new session using the same user-id and password that is currently logged in.

Window Menu

Provides generic window manipulation and navigation.

Help Menu

Provides access to the Repository Object Navigator's online help. Also provides access to the Repository Object Navigator's about box that will not only give it's version number, but also the repository version, database version, and many others that will be helpful when calling Oracle Support.

Database Design Wizard

This tool is the first part of moving from requirements definitions to design definitions. This dialogue box provides a textual method of defining and maintaining entity to table or attribute to column links. Used in creating initial table designs from the requirements defined. Will also use your designs to create foreign keys, subtype tables etc.

This utility must be run prior to running the Application Design Wizard so that modules can properly reference tables.

Application Design Wizard

This tool is the second part of moving from requirements definitions to design definitions. It will be used once all of the business requirement models are complete and you are ready to migrate to the actual physical design models. It basically creates a rough draft of what it thinks you want and need on the physical application side in the form of PL/SQL, Form and Report modules. This is done using the rules and definitions created and the options set in the Application Design Wizard.

Ensure that the Database Design Wizard has been run and Quality Checked prior to running this tool for optimum results.

Repository Reports

The repository reports module provides a way to run all of the predefined repository reports. The reports are spilt into the following categories:

The actual report definition files are located in your %ORACLE_HOME%\REPADM10\SRW directory. Thus, these reports can be copied and/or customized to meet organizational needs. The reports can be used throughout the entire life-cycle of applications and the repository itself. Very good tool to use when analyzing what it would take to change or add requirements. This will then translate into a quicker return in time, and resource requirements being reported to end users requesting changes. It is also a good tool to use when looking for gaps or flaws in a current application design.

Depending on Repository and Application size, these reports may take a while to run.


The repository administration tools are the most important tools available in the Designer/2000 suite, but at times they are also the most under utilized. It is important also to understand that a quality application design effort will require good repository management. Without a disciplined management of the repository or any CASE tool for that matter, it may become junk. This of course is what is hoped to be avoided when using any CASE tool. Remember that the graphical tools are provided to help in visualizing what is being designed. A tool like the Repository Object Navigator and the Repository Reports module could be used exclusively and still provide all that is needed to create an entire application. Oracle has provided lots of tools for use in maintaining a its repository. All of these are not necessary for a good and complete application design, but it is good to know what is available to you.

Previous Page TOC Next Page Home