Starting ORACLE and Setting Environment Variables

This section will provide a basic understanding of how to start ORACLE and some environment variables that may be of use. This section will be kept as general as possible and specific information, such as the name of the database instance, will be provided in the lecture, lab, or assignment writeup.

ORACLE is a relational database management system and as such has many development and production tools (i.e. SQL*PLUS, SQL*MENU, SQL*FORMS ..). You must evaluate the available tools and select those that are most appropriate for your application. Each tool that is available for your use will be explained in the following chapters.

ORACLE can be used from many different platforms and on each platform it can be used in many different environments. For example, you can use ORACLE from openwindows, olwm, twm, gwm or sunview on UNIX platforms. Although ORACLE can be used from many environments, it has been tested from the openwindows shelltool environment and seems to work better from this environment. If you are running ORACLE from an xterm, you need to bring up xterm with the ``sf'' option.

Before you can use any of the tools, you MUST set a few environment variables allowing you to identify yourself to ORACLE, the database instance you want to use and the location where ORACLE binaries can be found.

You should source the script ``/usr/local/bin/coraenv'' (``source /usr/local/bin/coraenv'') for csh shell users and ``/usr/local/bin/oraenv'' for sh users, from your lab machines to help you set the necessary variable parameters. The variables below may or may not indicate the correct settings and may change from time to time. See your TA, lab coordinator or assignment writeup for the correct settings to the variables below:

        setenv ORACLE_HOME /usr/gwynne3/oracle 
                    (or ``setenv ORACLE\_HOME ~oracle'')
        setenv ORACLE_SID crs
        setenv T2KDEV sun (or xsun)
        setenv TWO_TASK crs
        setenv TERM sun (or xsun)

ORACLE_HOME is set to the home directory of the ``oracle'' user.

ORACLE_SID is the name of the database instance that you are using.

T2KDEV is set to the type of terminal you are using; sun if you are using openwindows; xsun if you are using X windows.

TERM is set to the type of terminal you are using. Note: If you are using openwindows, you may have to redefine the variable TERM to sun (instead of cmd-sun) and you should execute ORACLE from a shelltool.

TWO_TASK is set to the location where ORACLE server can be found and will normally be the name of the database instance. This variable helps simplify the command line for starting ORACLE tools.

	call to ORACLE without TWO_TASK and SQL*NET
	     sqlplus scott/tiger@T:gwynne:cr
	call to ORACLE without TWO_TASK but with SQL*NET
	     sqlplus scott/tiger@crs
	call to ORACLE with TWO_TASK and SQL*NET
	     sqlplus scott/tiger
Note: T above stands for the protocol name (tcp/ip in this case). ``gwynne'' is the name of the database instance server and crs is the name of the database instance.

Starting ORACLE

If you are not given an ORACLE user ID or PASSWORD, then your user ID will be the same as your system login ID and your password will be verified by the system login. So to start an ORACLE tool SQL*PLUS, with TWO_TASK variable set with password identified by the system and SQL*NET installed, type:

	sqlplus /

Setting Up Your Preferred Editor

You may prefer to use your own editor when using the ORACLE tools instead of the default system editor (ed - type ``q'' to quit). This can be done by setting the environment variable EDITOR to the editor of your choice.

	setenv EDITOR /usr/ucb/vi

Online Error Messages

Use the online error message facility ``oerr'' to find out the descriptive error messages that are associated with the error codes that ORACLE produces.

	% oerr facility error

Facility is identified by the three-letter prefix in the error string. For example, if you get ORA-7300, ``ora'' is the facility and ``7300'' is the error, so you should type ``oerr ora 7300''. If you get LCD-111, type ``oerr lcd 111'' and so on.

An Introduction to the Data Dictionary

One of the most important parts of an ORACLE database is its data dictionary. The data dictionary is a set of tables to be used as a read-only reference, which provides information about its associated database. For example, a data dictionary can provide the following information:

The data dictionary is structured in tables and views, just like other database data. To access the data dictionary, you use SQL (see SQL*Plus). Because the data dictionary is read-only, users can only issue queries (SELECT statement) against the tables and views of the data dictionary.

The views of the data dictionary serve as a reference for all database users. Certain views are accessible to all ORACLE users, while others are intended for administrators only.

The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:

        USER    user's view (what is in the user's schema)
	ALL 	expanded user's view (what the user can access)
	DBA 	database administrator's view (what all users can access)

The views most likely to be of interest to typical users are those with the prefix USER. These views:

For example, the following query returns all the objects contained in your schema:

	SELECT * FROM user_objects;

You can obtain a list of all possible views by querying the data dictionary itself:

	SELECT * FROM dict;

Reporting Problems

If you have any valid suggestions on improving this manual, you should send a message to the user ``oracle''.

If you encounter any problems using ORACLE, you should see your TA, lab coordinator or send a message to the user ``oracle''.

For system problems or other ORACLE problems which require immediate attention, see the file ``/etc/motd'' for the method of reporting problems.