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/tigerNote: 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.
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:
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
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.
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;
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.