Your Oracle account is not the same as your UNIX account.

To log into the "stu" database with SQL*Plus, SSH into the department's servers and type the following at the prompt: sqlplus ora_<CWL username>@stu. When prompted for a password, type in "a" (without the quotation marks) and then your student number. For example, if you had "platypus" as your CWL username and 12345678 as your student number, your SQL*Plus login credentials would be ora_platypus@stu and a12345678.

In UBC's undergrad environment, only two connections to Oracle are allowed per user per session. Therefore, if you are having problems logging in, check first that you have at most one other connection to Oracle running.

Once Sql*Plus is started, you will see the prompt:
SQL>
which is your cue that you can begin entering SQL statements or SQL*Plus commands.

Below are some important rules for names in SQL statements:

Database object names, such as column and table names, that are not enclosed with double quotation marks:

• cannot contain quotation marks or spaces
• are not case sensitive
• must begin with an alphabetic character
• can contain only alphanumeric characters and the underscore (_) sign
• cannot be a reserved word (click here)

If database object names are enclosed with double (not single) quotation marks, then database object names:

• can contain spaces and symbols
• are case sensitive
• can be a reserved word
• must be enclosed with double quotation marks whenever they are used

Oracle interprets database object names that are not enclosed with double quotation marks as having all capital characters. Therefore, branch and BRANCH are the same as "BRANCH", but they are not the same as "branch".

Text and character literals are enclosed with single quotation marks, and they are case sensitive. For example, where branch_name = 'Main' is not the same as where branch_name = 'main'. To specify an apostrophe, use two single quotes e.g. 'Richard''s car'.

Viewing the Schema of a Table

DESCRIBE <table_name> outputs the schema of a relation, showing the table name, attribute (also called field or column) names, whether or not the attribute can be left blank (i.e., NOT NULL means the field is required to have a value at all times), and the domain (datatype) for the attribute.

Viewing All the Tables You Have Created in the Database

select table_name from user_tables

user_tables is a view in your database's data dictionary that contains a description of all your tables. Oracle stores information on each user database in a data dictionary, which just consists of tables and views.

Deleting All the Tables You Have Created in a Database

select 'drop table '||table_name||' cascade constraints;' from user_tables;

This query will generate all the drop table statements you need to drop every single user created table in the database. Once you run this query, you can either save the output to a file or copy and paste the output to run each statement to clear your database. This can be a very dangerous operation so proceed carefully- you cannot recover your tables once you have dropped them.

After running all the delete table statements, the tables will be sent to the recyle bin. In order to truly free up the space, you need to run purge recyclebin after the delete table statements.

Re-executing Commands

SQL*Plus keeps the most recent SQL command in a command buffer. Type / at the prompt.

You should see the results of the last SQL statement which you have typed (the current command in the command buffer). Now, type run at the prompt, and you should see both the SQL command and the results of the query.

Both the / and the run commands re-execute the last SQL statement, with the difference being that run displays the statement before executing it.

Changing Statements

Often you may make a mistake when typing in your SQL statement. For example, imagine there was a table called driver in the database and you typed select from driver;.

Oracle will return the error message:

ERROR at line 1:
            ORA-00936: missing expression

If we wanted to correct this command, we need SQL*Plus' CHANGE function. Type change /from/* from/ at the prompt.

The format change /something/something else/ replaces the text string something with the text string something else. The effect of the above change, as outputted by Oracle, is:
select * from driver

We can now execute the statement with the run command.

If you have a multi-line SQL statement that needs changing, you need to first type in the line number and press Enter before you can use the change command to correct anything. For example, in the following multi-line query, there is an error in line 5.
select driver.driver_sin, driver_name,
  license_no, branch_name, branch_city
  from driver, license, branch
  where driver.driver_sin = license.driver_sin and
  license.branch_id = branches.branch_id and
  license_type = 'D';


When we run this command, Oracle should return the following error statement:

ERROR at line 5:
            ORA-00904: invalid column name
            

To correc this, type 5 and press enter. This displays line 5 and makes line 5 current.

We can now apply the correction with the CHANGE command by executing something like change /branches.branch_id/branch.branch_id/.

Running SQL Queries from a File

You can save one or more SQL queries into a file and have SQLPlus run all the queries in that file in one go. To do this, first create a file with the SQL queries you wish to run and save that file with a .sql extension. Then, transfer that file to the undergraduate server. When you log into the undergraduate server, make sure you launch SQLPlus from the directory where your file is located. For example, if I saved my file to a directory called cs304, then I should cd into the cs304 directory before running sqlplus ora_cwl@stu. Once you have logged into SQLPlus, you can run the queries in the file by start <filename>.sql

Saving

We can save the most recent SQL statement into a file. Type at the SQL prompt: save somethingelse.sql.

This saves the last statement into a file called somethingelse.sql. To append the statement to an existing file, type save <filename> append. If we wanted to examine the contents of the file, we can use the GET <filename> command. To actually run the file, type start somethingelse or @ somethingelse.

To save the output of the current SQL*Plus session in a file, type spool <filename>.

Everything that is displayed on the screen after this command is entered will be saved in the given file. If you do not provide a file extension for the filename, a default extension (on most systems .LST or .LIS) will be added to the filename.

To stop writing to the file, type spool off.

Other Useful Commands

• rlwrap sqlplus: rlwrap is a tool that enables you to use the arrow keys in sqlplus
• quit: exits sqlplus
• host <host command>: This will allow you to run host operating system commands. For example, typing host ls *.sql executes ls *.sql.
• edit: Edits the SQL statement in the command buffer using the host operating system's default text editor (for us, it will probably be emacs). You will see the '/' symbol at the end of the file; just leave it there. Note: you cannot have more than one SQL statement in the file. After you save the changes and exit the editor, the new SQL statement will be placed in the command buffer. Use the / or run command to execute it.
• stty erase ^h: Enter this before getting into SQL*Plus. It is used to enable the backspace/delete key to delete characters that you accidentally typed on the command line.
• start myQuery or @myQuery: Assuming you have a file called myQuery.sql in the directory you run SQL*Plus from, this command will execute the SQL statements in that file.
• set pause "Press ENTER to continue ...";: Allows for page breaks in your output (rather than have everything scroll by). Follow this command with set pause on and (later) set pause off.
• set linesize 200: This will resize your output width to be 200 characters rather than some embarrassingly narrow width for a default.
• set pagesize 50: This will resize your output page (i.e., how frequently you see the column headings in the output from your SQL query).

Here are some other commands that may be useful when examining your account's space usage:

• SELECT * FROM user_segments: Shows the specifics of what I'm using on disk — I can see how much space a certain table is using
• SELECT SUM(bytes)/1024/1024 Size_MB FROM user_segments: Shows the sum of all segments I'm using in MB
• SELECT * from USER_TS_QUOTAS: Shows my quota
• SELECT * FROM RECYCLEBIN: What's in my recycle bin?
• PURGE RECYCLEBIN: Clear my recycle bin
• SELECT * from USER_TABLESPACES: How are the tablespaces I have access to configured?