Getting Started with Oracle

Contents

  1. Accessing SQL*Plus using the DCS Computers
  2. Accessing SQL*Plus using your Own Computer
  3. Troubleshooting Problems with SQL*Plus
  4. Tips when using SQL*Plus
  5. SQL Developer (Graphical interface)

Accessing SQL*Plus using the DCS Computers

To access Oracle:
  1. Open a terminal window.
  2. Type ssh csuxxx@mimosa.warwick.ac.uk, replacing csuxx with your ITS usercode.
  3. Enter your ITS password.
  4. Once you're logged in, enter orasetup
  5. Log out of mimosa and repeat Steps 2 and 3.
  6. Enter sqlplus /
  7. Enter an SQL statement. Ensure you put a semi-colon (;) at the end of your statement. If you want then you can press enter without entering a semi-colon to spread the statement across multiple lines.

Steps 4 and 5 only need to be done the first time you use use Oracle.

To leave Oracle:
  1. Save changes to the database by typing COMMIT;
  2. Leave Oracle by typing EXIT;
  3. Log out of mimosa by typing logout

Accessing SQL*Plus using your Own Computer

If you use Windows then you need to download and run putty.exe. If you use a different operating system then you should use SSH to access Oracle in the same way as within the department.

  1. Run putty.exe
  2. Enter mimosa.warwick.ac.uk in the "Host Name" box.
  3. Ensure that SSH is selected as the "Connection type".
  4. Go to the connection settings, set 30 seconds between keepalives and tick to enable TCP keepalives.
  5. Return to the session settings, choose a name for the connection (e.g. Mimosa) and Click "Save".
  6. Click on "Open".

Next time you use putty you can just double click the name that you chose in Step 5 to open a connection.

Troubleshooting Problems with SQL*Plus

I receive a message "ssh: Could not resolve hostname mimosa: Name or service not known", "Network error: Connection timed out" or similar.

Check that you're using the full name mimosa.warwick.ac.uk, e.g. ssh csuxxx@mimosa.warwick.ac.uk. The name "mimosa" on its own is not enough.

I receive a message when I try to connect to mimosa saying my password is incorrect, e.g. "Permission denied".

You have three computer accounts: In certain circumstances, you may have even more accounts, for example if you have changed your degree course. If one ITS username begins with cs and another does not then use the one beginning with cs.

Although they have the same username, your three accounts are completely separate. You can have a different password for each account. You must use the password for your ITS Unix account. Usually the password for your ITS Unix account is the same as the password for your ITS Windows account but it is possible to get them out of sync. If you have a problem then try using the ITS password that you used when you first joined the university. If you still have have problems then contact the IT Services Help Desk and ask them to reset your password for mimosa. Be sure to state that it is mimosa that you need your password reset for.

If you receive a different error message, for example stating that your account has been suspended or disabled then inform the IT Services Help Desk.

I receive the message "orasetup: command not found" or similar.

Ensure that you're logged into mimosa correctly. Enter hostname to check the name of the computer that you're logged onto. If the machine name is not mimosa then you haven't successfully logged onto mimosa yet.

I receive the message "sqlplus: command not found" or similar.

Run orasetup, log out and then log into mimosa again.

sqlplus prompts for a password or does not accept my password.

Ensure that you enter sqlplus /. The forward slash (/) is required to start SQL*Plus without a password. No password is needed.

SQL*Plus writes "2" instead of running my SQL.

Type a semi-colon (;) and press enter.

I receive the message "rows will be truncated".

The query results are too wide to fit on the terminal. There are several things you can do.

I made a syntax error. How can I correct it without having to type the whole statement out again?

  1. Type EDIT; The previous statement appears in a text editor. If you get an error message then type DEFINE _EDITOR='nano'; and try again.
  2. Alter the SQL as needed.
  3. Press Ctrl+o, followed by Ctrl+x.
  4. Type RUN;

If you connected using Putty then enter the following at the command prompt just before starting sqlplus to get the Home, End and Del keys to work when using the EDIT command.

export TERM=putty
bind '"\e[3~": delete-char'
bind '"\e[4~": end-of-line'

The arrow keys, Home, End and Del do not work when using SQL*Plus.

There are several things you can do:
  1. Use the EDIT command, as described above, to correct a mistake.
  2. Open a text editor (e.g. Notepad or gedit), prepare your statement in the text editor and copy-and-paste onto mimosa when you've finished editing.
  3. Start SQL*Plus using rlwrap sqlplus / instead of sqlplus /. This lets you edit the current statement using the left, right, Home, End and Del keys and retrieve previous commands using the up and down arrow keys. Use rlwrap with caution. There are bugs that may cause you to loose work. In particular, do not copy and paste when using rlwrap. rlwrap might be removed in the future.

I get disconnected from mimosa if I don't type anything in the window for a few minutes.

Check that keepalives are enabled.

Tips when using SQL*Plus

Saving the last command for future reference

Enter SAVE somesql.sql APPEND; A file called somesql.sql is created on your mimosa Unix account. If somesql.sql already exists then the last statement will be added onto the end of the existing file. You can connect to mimosa using FTP to download the file onto your own computer.

Running SQL statements written in a file.

Enter @somesql.sql to run all of the statements contained in the file somesql.sql.

Graphical Interface for Oracle

A graphical user interface for Oracle called SQL Developer is available.

SQL Developer is provided for your convenience but without any guarantee of support. They may be bugs or other problems that we have not anticipated. Although we appreciate any feedback that you have about SQL Developer we will not be obliged to answer any questions concerning the graphical interface. Do NOT rely on SQL Developer as your only way of accessing Oracle. Make sure you are familiar with SQL*Plus.

(1) Download and Install Oracle SQL Developer

Download Oracle SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html and install it. There are different versions for different operating systems. If in doubt then choose the one for 32-bit Windows including the JDK. If you already have JDK 1.6 installed, for example if you've previously taken a Java programming module, then you can choose the version without the JDK but you must have JDK 1.6 installed. At the time of writing the current version of SQL Developer (3.0.04.34) does not work correctly using JDK 1.7.

(2) Configure Putty

If you're using Putty then some settings need to be changed.
  1. Open Putty
  2. If if you previously saved some settings then single click on mimosa from the saved session list and click on "Load".
  3. Select Connection->SSH->Tunnels from the tree on the left.
  4. Enter 1521 in the "Source port" box.
  5. Enter mimosa.warwick.ac.uk:1521 in the "Destination" box.
  6. Ensure "Local" is selected.
  7. Click on "Add".
  8. Select Connection from the tree on the left and click "Save".

(3) Connect using SSH

Connect to mimosa. Enter your password to log onto mimosa in the usual way. If you're using the Unix SSH command instead of Putty then use ssh -L 1521:mimosa.warwick.ac.uk:1521 csuxxx@mimosa.warwick.ac.uk where csuxxx is your username.

(4) Choose Your Password (first time only)

You need to choose a password. This password is specifically for using SQL Developer. Start SQL*Plus in the usual way and enter the SQL statement alter user ops$csuxxx identified by password; where csuxxx is your username and password is your chosen password. Stay logged into mimosa.

(5) Configure SQL Developer

  1. Open SQL Developer.
  2. Right click on "Connections" from the left-hand pane and choose "New Connection...".
  3. Choose a name for the connection and enter it in the "Connection Name" box.
  4. Enter OPS$csuxxx in the "Username" box, where csuxxx is your ITS username.
  5. Enter the password that you chose in Step (4).
  6. Check that the hostname is localhost and the port is 1521.
  7. Enter mistdb in the SID text box.
  8. Click "Test", "Save" and "Connect".

Using Oracle SQL Developer

Enter one or more SQL statements and press F5 to execute them.