1. technology and computing
2. software
3. databases

.2
CSCI315 Database Design and Implementation
Experiment 1.2
How to access Oracle database server from
SQL*Plus client running on SunOS system ?
Experimented and written by Dr. Janusz R. Getta
School of Computer Science and Software Engineering, University of Wollongong, Australia,
Bldg. 3, room 210, phone +61 02 42214339, fax +61 02 42214170,
e-mail: jrg@uow.edu.au, Web: http://www.uow.edu.au/∼jrg, Msn: jgetta, Skype: jgetta007
Table of contents
Step 0 How to begin and what you need to know before you start ?
Step 1 How to start SQLPlus* client and hown to connect to a database server
?
Step 2 How to disconnect from a database server ?
Step 3 How execute SQL statement ?
Step 4 How to create SQL script ?
Step 5 How to execute SQL script ?
Step 6 How to display SQL statements while a script is running ?
Step 7 How to change a password ?
Step 8 How to transfer SQL script files from PC system to Unix system ?
References
Actions
Step 0 How to begin and what you need to do before you start ?
0
These are the specifications of homeworks in a subject Database Design and Implementation (CSCI315)
taught in Spring session 2011 at School of Computer Science and Software Engineering, University of Wollongong by Dr. Janusz R. Getta
1-1
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-2
A printable copy of this experiment in pdf format is available here .If you would like to
access Oracle database server from your system at home then you have to first install Secure
Shell client (ssh ) or putty client on your system at home. Secure Shell client is already
installed on all systems in 3.125 (Database Lab). When ssh or putty client is installed,
download and uncompress SQL scripts in tar.gz format or SQL scripts in zip format used
in this homework. If you do not know how to transfer SQL script files from PC system
to Unix system then please go to Step 8 of this experiment. To connect to Oracle server
you have to first start ssh (Secure Shell) client or putty client and connect to a computer
banshee.cs.uow.edu.au or wumpus.cs.uow.edu.au on port 22 . A connection procedure
for ssh client is the following. First, activate a window of running ssh client and click at
Quick Connect option. The system should open Connect to Remote Host window. Type
either banshee.cs.uow.edu.au or wumpus.cs.uow.edu.au into Host Name field. Type in
a prefix of your email address at UoW, e.g. xyz007 into User Name field. Type 22 into
Port Number field. Leave the contents of Authentication Method unchanged. Finally,
click at Connect button. When the program asks you about Host Identification click at
Yes button. Next, type in you password. Your password is the same as a password of
your email account at UoW. To logon to one of SunOS systems you have to provide your
user name and password. User name is always the same as a prefix of your email address,
e.g. xyz99 . Password is always the same as a password to your email. Please remember
that command processor of SunOS system is case sensitive ! If you cannot logon ask your
tutor to guide you to appropriate support staff member who will open an account for you.
Your lab tutors have no privileges to grant you the access to SunOS system. If you are not
familiar with how to use a command line interface to SunOS system please visit our library,
get yourself a book on any Unix-like system, and invest one evening to read the first few
chapters. There are heaps of books on this subject. It is strongly recommended to learn a bit
about Unix-like systems. When you are connected as a remote user to one of Unix systems
running either on banshee.cs.uow.edu.au or wumpus.cs.uow.edu.au execute a command
hostname at $ prompt to check if you are connected to the right system. The following step
should be performed only once. The objective of this step is to update your .profile file
such that at login time operating system automatically appends a path to the binaries of a
remote Oracle database server to your PATH variable. To find what pathes are included in
PATH variable type:
echo $PATH
at $ prompt. The system should list the pathes included in PATH A sequence of pathes
should not include pathes to Oracle executable files. To automatically append the pathes at
login time we have to include a call to Unix shell script csoraenv.dragon into .profile file.
Type at $ prompt:
je .profile
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-3
and press Enter key. A command above starts Jove editor and opens a file .profile .
A documentation on how to use Jove editor is available here . You can also use any other
text editor available on banshee.cs.uow.edu.au or wumpus.cs.uow.edu.au like for instance
pico or vi .Type in the following line into .profile file:
.
/share/etc/csoraenv.dragon
Make sure that you left at least one blank between a starting ”dot” and the first ”slash”. Save
.profile file (use CTRL x s to save file and CTRL x c to quit an editor). A shell script file
csoraenv.dragon sets the values of Oracle variables ( ORACLE_SID, ORACLE_HOME, TWO_TASK, ORACLE
and appends the paths to Oracle binaries and libraries to PATH variable. Next, type at
$ prompt:
.
.profile
and press ENTER key to re-execute .profile file. Make sure that you left at least one
blank between the first and the second ”dot” in the line above. At this point execution
of csoraenv.dragon Unix shell script appends a path to Oracle database server binary
programs to your PATH variable. To display the contents of PATH variable, type at $ prompt:
echo $PATH
and press ENTER key. Your $PATH variable should contain among the others the following
pathes::
:/packages/csoracle/u02/app/oracle/product/10.1.0.2/bin:
/packages/csoracle/u02/app/oracle/product/10.1.0.2/oc4j/bin
The last step to start SQL*Plus client is to type at $ prompt:
sqlplus /nolog
and press ENTER key. Note, that in this case a default database server we connect to is
csci server. It is is explained later how to explicitely determine a server we would like to
connect to. The system should reply with a message:
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 2 12:59:52 2006
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-4
Copyright (c) 1982, 2009, Oracle.
All rights reserved.
SQL
Congratulations, if you got the message ! If not then please ask a tutor about help. Note,
that csci server runs Oracle 12c. Now, when you have SQL*Plus client running, type at:
SQL> prompt:
exit
and press ENTER key. This command quits SQL*Plus client and takes you back to ssh Unix
shell client. Good news is, that in the future you do not have to repeat the steps performed
above again !
Step 1 How to start SQLPlus* client and how to connect to a database server ?
First, you have to start Secure Shell ( ssh ) session and connect to either banshee.cs.uow.edu.au or
wumpus.cs.uow.edu.au . Use a prefix of your email address at UoW as your user name, e.g.
xyz007 , and password to your email at UoW as your password. Remember that command
processors of Unix systems are case sensitive ! If you are not familiar with a command line
interface to Unix systems please make a quick trip to a library, get yourself a book (any
”Unix in a Nutshell” style of book will do !) and invest one evening to read the first few
chapters. It should be completely enough to operate SQL*Plus client on Unix system. To
start SQL*Plus client and to connect to Oracle database server execute at $ prompt:
sqlplus
The system should display the following lines:
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 2 13:08:14 2006
Copyright (c) 1982, 2009, Oracle.
All rights reserved.
Enter user-name:
The system prompts you about user name. Assume that you would like to connect to a
database server identified by identifier-of-Oracle-database-server as a user your-user-name The
you should reply with:
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-5
your-user-name@identifier-of-Oracle-database-server
your-user-name denotes your user name, e.g. xyz007 . identifier-of-Oracle-database-server denotes an identifier of Oracle database server you would like to connect to. You have the
following options: data-pc01 , data-pc02 , data-pc03 , ... , data-pc40 , csci . If
you skip identifier of Oracle database server then by default the connection goes to
csci server. If you would like to connect to one of data-pc.. servers (Win 7 based servers) as
SCOTT/tiger then <your user name>is SCOTT , <identifier of Oracle database server>is
data-pc.. and password is tiger . If you would like to connect to csci server then
<your user name>is a prefix of you email account e.g. xyz007 , <identifier of Oracle
database server>is csci and password has been provided in an email sent to you by a
database administrator. The system should display the following line:
Enter password:
It means that the system asks you about your password. Type in your password and press
Enter key. If user name and password are correct then the system should reply with the
following messages:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL
SQL*Plus is a command line interface to Oracle database server. To execute SQL statement you have to either type it directly at SQL> prompt and press Enter key or you can
store one or more SQL statements in SQL script file and execute the script file. SQL scripts
are plain text files with an extension .sql . We shall practice both methods in the next few
actions.
Step 2 How to disconnect from a database server ?
To disconnect from a database server type at SQL> prompt:
exit
When a connection is closed, restart SQL*Plus and connect to a database server once more.
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-6
Step 3 How execute SQL statement ?
While connected to a database server execute the following SELECT statement in an interactive mode:
SELECT USER FROM DUAL;
The system should display a name of a user connected to a database server.
USER
-----------------------------<
where <your-user-name>is a name of usere connected to the system. DUAL is a ”dummy” relational table that does not exist and it must by present satisfy the syntactical requirements
of SQL because SELECT statement must consists of at least SELECT and FROM clauses.
Step 4 How to create SQL script ?
SQL script is a plain text file that contains one or more SQL statements. To create SQL
script with two SELECT statements type at SQL> prompt:
edit whoami
and press Enter key. The system should automatically start Jove editor and open an
empty file whoami.sql. Type into the file the following lines:
SELECT USER FROM DUAL;
SELECT SYSDATE FROM DUAL;
To save the file whoami.sql and to quit an editor type CTRL x s and CTRL x c. A file
whoami.sql is always saved in the current directory of SQL*Plus client. The current directory of SQL*Plus client is the same as the curent directory of a user who started SQL*Plus
client. It is possible to ”leave” SQL*Plus client for a while and start another copy of Unix
shell script. We shall use this feature to find where SQL script file whoami.sql have been
saved Type at SQL> prompt:
!
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-7
The system should leave SQL*Plus client and it should start another copy of Unix shell.
Next, at $ prompt execute a statement
ls -la
to list the contents of the current directory. A file whoami.sql is always saved in the current directory, or more precisely, in a directory, which was your current directory when you
started SQL*Plus client. If you would like to create a separate directory for your SQL scripts
(it is a very good idea !) then type exit to leave SQL*plus client, create a new directory (
mkdir directory-name ), move whoami.sql to the directory ( mv whoami.sql directory-name ),
make the directory current ( cd directory-name ). To quit Unix shell script and to return
to SQL*Plus client type:
exit
Our first SQL script is ready. It is possible to put into SQL script more than two statements.
Using SQL scripts has one disadvantage and many advantages and over the interactive execution of SQL statements directly at a prompt. Preparation of a script takes time and it
is not feasible to do it for just a single SQL statement. The advantages include the execution of several SQL statements in one run, repetition of the execution without re-typing
the statements, and recording of a work done during a lab class for a future reference. It is
strongly recommended to use SQL script files in any future work with SQL.
Step 5 How to execute SQL script ?
To execute the script file type at SQL> prompt:
@whoami
and press Enter key.The system should reply with the following messages:
USER
-----------------------------<
SYSDATE
-------------<
where <your-user-name>is a user name used to connect to the system and <current-date>is
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-8
the current date.
Step 6 How to display SQL statements while a script is running ?
If you would like to see SQL statements executed by the system while SQL script is running
then you have set echo option of SQL*Plus. To do so type:
edit whoami
The system should start Jove editor. Replace the second occurrence of SQL*Plus statement:
set echo off
with SQL*Plus statement:
set echo on
Save and execute the script whoami.sql .
Step 7 How to change a password ?
To change a password, execute the following statement at SQL prompt:
ALTER USER user-name IDENTIFIED BY new-password;
where the metasymbols: user-name , and new-password should be replaced with your user
name and new password. For example, the execution of ALTER USER statement:
ALTER USER SCOTT IDENTIFIED by pszczola;
changes a password of user SCOTT to pszczola.
Step 8 How to transfer SQL script files from PC system to Unix system ?
To be able to use SQL script files provided with the experiments you have to transfer the
scripts from PC files system to your account on Unix system. Download SQL scripts in
tar.gz format Dowload and uncompress SQL scripts in tar.gz format or SQL scripts in zip
format used in this homework. Start Secure Shell ( ssh ) client and connect your account
on Unix system. Next, form a client’s menu select an option Window->New File Transfer .
The system should open a new window with the left panel view of your PC file system
Experiment 1.2: How to access Oracle database server from SQL*Plus client running on SunOS system ? 1-9
and right panel view of you Unix file system. Now, you should navigate the left panel to a
location where a file with SQL scripts in tar.gz fromat or SQL scripts in zip format has
been saved. Next, set option Operation->Binary and drag a file with SQL scripts in tar.gz
format or SQL scripts in zip format text to the right panel view. After a file with SQL
scripts in tar.gz format has been transferred return to Secure Shell ( ssh window and execute
the following command at $ prompt:
gunzip home1.tar.gz
to uncompress the scripts. Finally, execute a command:
tar -xvf home1.tar
to unpack the scripts. After a file with SQL scripts in zip format has been transferred
return to Secure Shell ( ssh window and execute the following command at $ prompt:
unzip home1.zip
to uncompress the scripts. Finally, execute a command: Start SQL*Plus client and execute a script hello.sql .
References
SQL*Plus User’s Guide and Reference
SQL*Plus Quick Reference