Acknowledgment: This note is a slight modified version of a similar document from the Stanford DB class website.
Oracle is installed on dbclass. You need to log on to dbclass to work (SSH to dbclass should do fine).
To set up the correct environment variables, add the following to your .cshrc
if ($HOST == dbclass ) then
setenv ORACLE_SID CS482
setenv ORACLE_HOME /oracle/app/oracle/product/11.1.0/db_1
set path =( ${ORACLE_HOME}/bin $path )
endif
After the environment variables are setup, you can log in to Oracle by typing:
sqlplus <yourAccountID>
Here, sqlplus is Oracle's generic SQL interface.
<yourAccountID> is set to the first letter of your first name and your last name. For example, my first name is Son and my last name is Tran, my account ID is the string stran.
You will be prompted for your password. This password is initially set to 'the first letter of your last name and the eight digits of your NMSU student ID' and must be changed as soon as possible. For example, my NMSU ID is '12345678', my initial password is set to 't12345678'. For security reasons, we suggest that you not use your regular CS password. Oracle is recently reinstalled. As such, previous accounts are no longer valid.
After you enter the correct password, you should receive the prompt
SQL>
In response to the SQL> prompt, type
ALTER USER <yourAccountID> IDENTIFIED BY <newPassword>;
where <newPassword> is the password you would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.
For example, if I want to change the password of my account 'stran' to 't2222', I will type
ALTER USER txxx IDENTIFIED BY t2222;
Note that SQL is completely case-insensitive. Once you are in sqlplus, you can use capitals or not in keywords like ALTER; SEELCT. Even your password is case insensitive. We tend to capitalize keywords and not other things.
In sqlplus we can execute any SQL command. One simple type of command creates a table (relation). The syntax is
CREATE TABLE <tableName> (
<list of attributes and their types>
);
You may enter text on one line or on several lines. If your command runs over several lines, you will be prompted with line numbers until you type the semicolon that ends any command. (Warning: An empty line terminates the command but does not execute it; see Editing Commands in the Buffer.) An example table-creation command is:
CREATE TABLE mytable (
ssn int,
name char(40)
);
This command creates a table named test with two attributes. The first, named ssn, is an integer, and the second, named name, is a character string of length (up to) 40.
To create a table that declares attribute a to be a primary key:
CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
To create a table that declares the set of attributes (a,b,c) to be a primary key:
CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));
Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command:
INSERT INTO <tableName>
VALUES( <list of values for attributes, in order> );
For instance, we can insert the tuple (111223333, 'Tran XXX') into relation mytable by
INSERT INTO test VALUES(111223333, 'Tran XXX');
We can see the tuples in a relation with the command:
SELECT *
FROM <tableName>;
For instance, after the above create and insert statements, the command
SELECT * FROM mytable ;
produces the result
SSN Name
---------- ----------
111223333 Tran XXX
To remove a table from your database, execute
DROP TABLE <tableName>;
We suggest you execute
DROP TABLE mytable;
after trying out this sequence of commands to avoid leaving a lot of garbage around that will be still there the next time you use the Oracle system.
The system keeps information about your own database in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:
SELECT TABLE_NAME
FROM USER_TABLES;
More information about your tables is available from USER_TABLES. To see all the attributes of USER_TABLES, try:
SELECT *
FROM USER_TABLES;
It is also possible to recall the attributes of a table once you know its name. Issue the command:
DESCRIBE <tableName>;
to learn about the attributes of relation <tableName>.
If you want to know about the constraints that you have created, use
SELECT CONSTRANT_NAME FROM USER_CONSTRAINTS;
The constraints you (and the system created for you) are stored in a table named 'USER_CONSTRAINTS'. You can also view the expression of the constraint. See the description of the file USER_CONSTRAINTS and you have an idea, what you can view.
To leave sqlplus, type
quit;
in response to the SQL> prompt.
Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.
To run the file myfile.sql, type:
@myfile
sqlplus assumes by default the file extension ".sql" if there is no extension. So you could have entered @myfile.sql at the SQL> prompt, but if you wanted to execute the file mytest.txt, you would have to enter @mytest.txt.
You can also run a file at connection by using a special form on the Unix command line. The form of the command is:
sqlplus <yourName>/<yourPassword> @<fileName>
For instance, if user txxx, whose password is t1111, wishes to execute the file myfile.sql, he can use the following commant:
sqlplus txxx/t1111 @myfile
Notice that this mode presents a risk that the password of txxx will be discovered, so it should be used carefully.
If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or a single slash (/).
You may also edit the command in the buffer before you execute it. Here are some useful editing commands. They are shown in upper case but may be either upper or lower.
| LIST | lists the command buffer, and makes the last line in the buffer the "current" line |
| LIST n | prints line n of the command buffer, and makes line n the current line |
| LIST m n | prints lines m through n, and makes line n the current line |
| INPUT | enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns" |
| CHANGE /old/new | replaces the text "old" by "new" in the current line |
| APPEND text | appends "text" to the end of the current line |
| DEL | deletes the current line |
All of these commands may be executed by entering the first letter or any other prefix of the command except for the DEL command.
An alternative is to edit the file where your SQL is kept directly from sqlplus. If you say
edit foo.sql
the file foo.sql will be passed to an editor of your choice. The default is vi. However, you may say
DEFINE _EDITOR = "emacs"
if you prefer to use the emacs editor; other editor choices may be called for in the analogous way. In fact, if you would like to make emacs your default editor, there is a login file that you may create in the directory from which you call sqlplus. Put in the file called login.sql the above editor-defining command, or any other commands you would like executed every time you call sqlplus.
We tend to not using this feature very often.
There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is
script [ -a ] [ filename ]
The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type
exit
For more information on how to run the script command, check out its man page.
sqlplus provides the command spool to save query results to a file. At the SQL> prompt, you say:
spool mycmd;
and a file called mycmd.lst will appear in your current directory and will record all user input and system output, until you exit sqlplus or type:
spool off;
Note that if the file mycmd.lst existed previously, it will be overwritten, not appended.
SQL*Plus provides internal help facilities for SQL*Plus commands. No help is provided for standard SQL keywords. To see a list of commands for which help is available, type help topics or help index in response to the SQL> prompt. To then look up help for a particular keyword (listed in the index), type help followed by the keyword. For example, typing help accept will print out the syntax for the accept command.
The output from help, and in general, the results of many SQL commands, can be too long to display on a screen. You can use
set pause on;
to activate the paging feature. When this feature is activated, output will pause at the end of each screen until you hit the "return" key. To turn this feature off, use
set pause off;
Las modified 08/23/2007