The Database Design Resource Center

Configuring and tuning Oracle

After the installation process is complete, configuring and tuning Oracle it is very necessary to configure various tools so that these can be used with Oracle without any problem.

Once the database is running, the SQL*Plus tool can be used to connect to the database.

Go to the Start -> Programs -> Oracle - OraHomeX0 -> Application Development program group and run the SQL Plus application. Once SQL Plus is running, you will be prompted for 3 things: Username, password and Host String.

The default Database Administrator's account has a username of SYSTEM and a default password of MANAGER. You may have changed the SYSTEM account password in a previous step so use the new password.

Fill in the Username and password fields with the SYSTEM account and leave the Host String field blank. Since we are connecting to a local Oracle database, no Host String is required. Click on the OK button as shown below:

Configuring and tuning Oracle

If the database is running and the username and password are typed correctly, SQL*Plus should log the SYSTEM user in and present the SQL> prompt as shown below:

Configuring and tuning Oracle

Configuring Oracle Server

Web Application Framework needs a valid Oracle username and password during an Oracle installation in order to install and run properly.

The user should have full privileges on its schema, which can be spread across one or more tablespaces. In addition, make sure your version of Oracle is configured as follows:

  • UTF-8 character set
  • Minimum 8KB db blocksize
  • Intermedia, if you need full-text search capabilities

Configuring Oracle Client

Although you can install the WAF with the Oracle Thin driver, there are some known issues with the way Oracle's thin driver handles LOBs. For configuring and tuning Oracle, it is highly recommended to use the OCI driver instead. Most likely, the Oracle JDBC driver will be already in place at:

Configuring and tuning Oracle

This is also the default value offered by the installation program when configuring the location of the Oracle JDBC driver, assuming that at least the Oracle client library is available and that ORACLE_HOME is set accordingly.

If this file does not exist, you must install it. The OCI driver depends on native Oracle client libraries which must be properly set via LD_LIBRARY_PATH.

Make sure that the user running the servlet conatiner has read access to the Oracle JDBC driver and client libraries. To get the Thin JDBC driver to work, the target Oracle machine (for example, the one you are connecting to) must have its listener enabled for TCP connections, even if the WAF and Oracle installations are on the same host.

If the OCI driver is an option (see above), you could use the OCI driver to eliminate the need for the TCP listener.

A number of the commands for installation and day-to-day development assume that the Oracle environment has been setup for all users on the machine. This can be trivially accomplished by creating the file /etc/profile.d/ as follows (you will need to correct the paths to match your local installation):

Configuring and tuning Oracle

Enable version control, if required

During configuring and tuning Oracle, if you require version control of repository objects, you must enable this functionality by opening the Repository Administration Utility and selecting Options -> Enable Version Support. Caution: You cannot undo this operation.

Before enabling the version control features, ensure this is really necessary for this repository and its data. If in doubt, defer this operation; you can perform it at any time.

Create a test workarea and container

I recommend the following:

  • Create a test workarea and container to make sure that you can create element definitions in the repository (but note that, if you have not enabled versioning, you will not be able to create a workarea additional to the default one).
  • check whether you can use the Repository Object Navigator to create objects in the repository.

Create subordinate users

If other users at your site are to have access to the repository, you will need to create subordinate users. This is the term given to repository users other than the repository owner.

Any usernames you want to use for subordinate users must already have been created as Oracle usernames via a CREATE USER statement in SQL*Plus or the Enterprise Manager.

See the Oracle Repository online help for details on configuring and tuning Oracle about how to grant repository access to an Oracle user, thus creating a subordinate user. Subordinate users must be assigned the CONNECT and RESOURCE roles in SQL*Plus or the Enterprise Manager.

To do so, connect as SYS and issue the following:

grant connect, resource to subordinate_user;

Grant privileges to users

The following table is a checklist of the system privileges that are required by a repository owner and subordinate users, together with the operation or process with which they are principally associated.

Configuring and tuning Oracle

* these privileges are granted to subordinate users when they are assigned the CONNECT and RESOURCE roles.

Diagnostics privileges are required if, for example, you wish to enable SQL TRACE.

Registration privileges are required for registration of Oracle schemas in the repository. See the online help for Oracle Repository.

Reconcile user privileges allow subordinate users to be enabled or disabled (synonyms created or dropped) via the Reconcile button on the Maintain Users dialog box of the Repository Administration Utility.

Set up the broadcast server

The broadcast server enables repository users to be notified immediately of changes made to repository objects in the current container.

Users can use this feature locally (changes made in one tool are immediately visible in other tools) or over the network (same as locally, and in addition changes made by one user are immediately visible to other users), or they can disable it.

The following table shows what you need to do, depending on how the feature is to be used.

Configuring and tuning Oracle

If you are using this feature over the network, you need to decide whether to run the broadcast server from either a client workstation or from the database server where the repository is installed, and install the broadcast server files accordingly.

Return to installing the Oracle database

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.