Database Management Systems

by Raghu Ramakrishnan and Johannes Gehrke

[Database Management Systems (3rd Edition)] [WWW Resources] [Material for the Third Edition] [Material for the First Edition] [Material for the Second Edition]

Configuring Oracle for Course Use

Oracle can be used in a class for many things, but most commonly it is used when instructing on SQL Queries, schemas design in SQL, and database programming with a paradigm such as JDBC. This guide is not a comprehensive description of how to install Oracle, that depends on your system configuration and is covered by the Oracle documentation. This guide will help you to use Oracle in your course once it is installed, by giving you guidance on how to set it up for use with your class.

Defining Privileges

The first step in setting up Oracle is to decide which privileges the students will have (most likely you will want to give the course staff full privileges if they are qualified). In order to do this you should create a new role. You may do this in DBA Studio or from the command line with the create role command. Once created you will want to grant basic rights, such as the ability to create a database session, create tables, etc. as well as any additional privileges for the course you are using, such as CREATE TRIGGER if Triggers are used. You may want to first grant other roles to the student role; the CONNECT Role, which is supplied with Oracle has many basic privileges. Here is some SQL for a typical Role:

CREATE ROLE "STUDENT" NOT IDENTIFIED;
GRANT CREATE INDEXTYPE TO "STUDENT"
GRANT CREATE PROCEDURE TO "STUDENT"
GRANT CREATE TRIGGER TO "STUDENT"
GRANT "CONNECT" TO "STUDENT";

To use DBA Studio go to: Start > Programs >Oracle > Database Administration > DBA Studio. It will ask you for the server name and your administrative account. You can go to the Security/Roles tab, select create Role, and then grant other Roles or System Privileges as necessary. You also will want to set clear expectations of students, including a reference to official policy for your institution stating acceptable use for the Oracle system.

Creating Accounts

The next step is to create an account for each student. This involves an automated script, the class roster, and a prototype account.

For your prototype account you will need to know the default and temporary table spaces (if you created several) and a quota. The first phase of the script will, from Unix generate the Oracle sql script, which can be run in SQL*Plus. You may, of course, call sqlplus from your script if you so desire. A quota of some sort is important, but too small a quota can be problematic. If no special projects are used 20MB should be sufficient, otherwise 40MB initially might be better. The following creates accounts on a basic system from a file separated by | where the students id is in column 4. The initial password is generated based on the id followed by 'pw'. A more sophisticated system could generated passwords randomly and send them via email. If you have several tables you can use the row number of the file to divide students among table spaces. (i.e.NR%2+1 for USER1 and USER2 with gawk). You can use a similar system for group accounts.

#!/usr/bin/sh
cat roster | awk -F'|' '{ printf "CREATE USER " $4 " IDENTIFIED BY " $4 "pw DEFAULT TABLESPACE USERS" \
" TEMPORARY TABLESPACE TEMP PROFILE STUDENT QUOTA 20 M ON USERS ACCOUNT UNLOCK;\n GRANT CONNECT TO " \
 $4 ";\n GRANT STUDENT TO " $4 ";\n ALTER USER " $4 " DEFAULT ROLE ALL;\N" }' >create.sql

Then in SQL * Plus you just type @create.sql and Oracle will create all the accounts you need.

Providing Configuration instructions

Now that the accounts are ready, you need to provide students with connection instructions. You can provide them with the information needed to login to Unix. As for using Oracle, they will need to modify their login scripts (such as .cshrc) to set several environmental variables both for programming and for SQL*Plus. In the example below, the SID and HOME are set to use Oracle, the LD_LIBRARY_PATH is needed for programming, and the CLASSPATH is needed for using Java (JDBC). The path statement allows the student to avoid typing the full path to each utility. It assumes Oracle is installed in /usr/cs/oracle and has SID dbclass. You should adjust these for your install and provide them to students. You will also need the connect string such as dbclass.cs.somewhere.edu from the tnsnames.ora file, so they will login with id@connectstring.

setenv ORACLE_HOME  /usr/cs/oracle
setenv ORACLE_SID dbclass
set path = (/usr/cs/oracle/bin $path)

setenv LD_LIBRARY_PATH /usr/cs/oracle/lib:$LD_LIBRARY_PATH
setenv LD_LIBRARY_PATH  /usr/cs/oracle/jdbc/lib:$LD_LIBRARY_PATH
setenv CLASSPATH $ORACLE_HOME/jdbc/lib/classes12.zip:.
setenv CLASSPATH $ORACLE_HOME/jdbc/lib/nls_charset12.zip:$CLASSPATH
setenv CLASSPATH $ORACLE_HOME/sqlj/lib/translator.zip:$CLASSPATH

Have the students add these lines, allow the changes to take effect, run SQL*Plus, login, change there password, and perhaps do some exercises. You may want to provide step by step instructions, and a sample file:

Add the following lines to your .cshrc file: [insert lines]
Type 'source .cshrc' to have them take effect. You need only do this once and will not have to repeat it when you next login.
Run sqlplus
At the prompt type id@connectstirng where id is your id.
Type passw to change your password.

Note if you get error:

Error while trying to retrieve text for error ORA-12545

You did not enter your connect string.

Managing Processes

One resource you may find is not adequate in a default configuration is process usage as set by the ora.ini file. Students may have several processes going, and students may all login at once when deadlines are due. You should insure that the number of processes is adequate for the entire class to login at once, with several additional processes for staff or students who may be on multiple sessions. The server requires at least 5 internal processes. You may need to change the number of semaphores available on your Unix server.

You may halt processes in DBA Studio, or with the alter system kill session command. This command requires the sid and serial# number from the v$session view, which you may use as any other view. Of course, this can be automated.

alter system kill session '30,9559';

Deleting Accounts

The easiest way to delete accounts is to make use of the fact that only those students with the student role need be deleted. This avoids having to save old rosters, although this is an alternative. The following script selects all accounts and can delete them if you uncomment the last lines. You may specify specifica exemptions in the where clause, or inspect the delete.sql file manually before running it in SQL *Plus and removing all the accounts. Be sure not to delete your own accounts or any system accounts!

set escape off
set escape \
set escape on
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL delete.sql
select 'drop user ', u.username, ' cascade\;' from dba_users u,
dba_role_privs r where r.grantee=u.username and
r.granted_role='STUDENT'
order by u.username;
spool off
remark @delete
remark quit

Back to the list of supporting material.