Using the Oracle Staff Student Interactive Database (SSID)
SSID is an Oracle database that eSolutions has created for students and staff to develop their IT skills in using and creating applications that use SQL and enterprise standards such as Oracle RDBMS.
This help article explains the different ways to log in, give other users access to your data, and how to back up and restore your data. There are also tips included to optimise your use of the SSID, and provide some help in handling the most common errors.
Connecting to the SSID
To connect to the SSID database, SSH to the host interactive.deakin.edu.au.
Upon connecting to the host you will be prompted with the following:
Use your arrow keys and go to the line that reads SQLplus to SSID and press enter. You will now be prompted to enter your password.
Note: If you connect to the interactive host and the menu does not appear, it is most likely that you have a corrupt .bashrc file. To fix this file from on campus, delete it from your home directory (rm ~/.bashrc), off-campus students can use the command rm .bashrc. You must then re-login to the interactive host (interactive.deakin.edu.au). The file will be recreated with the correct contents.
Connecting directly from the Unix prompt
To connect directly from the Unix prompt you must do the following:
1. Ensure your Oracle environment is setup correctly:
bash$ export ORACLE_HOME=/opt/oracle/product/server/22.214.171.124
bash$ export ORACLE_SID=SSID
2. Run the SQLplus program and begin your session in SSID:
Connecting via the web
You can access your SSID database from the web in many different ways. One of the most popular ways to access databases from the web is using PHP. Here is a small example of what you will need to connect to SSID from the web using PHP.
$dbuser = "gary"; // Your user name here
$dbpass = "garyspassword"; // Your password here
$connect = OCILogon($dbuser, $dbpass, $db);
$query = "grant select on phnum to barry";
$command = ociparse($connect, $query);
This example will connect to SSID as the user 'gary' and execute the query 'grant select on phnum to barry'.
Note: eSolutions are not able to assist students with connecting to the database in this manner. This example is here simply for a guide.
I am unable to login to SSID. What can I do?
If SSID will not accept your username and password, you probably have not changed your password recently or it may be possible that you have an odd character in your password. Oracle does not like passwords with characters that Unix uses in commands, e.g. !@<>. for example, if you had the character @ in your password you would also need to put your password in quotation marks, e.g. "myh@rdpassword". Doing this may allow you to login.
Otherwise you will have to change your password. Keep in mind the change will take time to propagate to the Oracle system. You should restrict your password to upper and lower case letters and numbers and a length of eight characters.
Giving other users access to data
To grant other users access to data in your database, you will need to perform one of the following commands:
• This example will give the user 'barry' access to view the data within a table called 'phnum':
SQL> grant select on phnum to barry;
• This will give 'barry' access to insert data into the table 'phnum':
SQL> grant insert on phnum to barry;
• This will allow 'barry' to update/change data that is in the table 'phnum':
SQL> grant update on phnum to barry;
• This will allow 'barry' to delete data from the table 'phnum':
SQL> grant delete on phnum to barry;
To remove access, follow the examples below:
• This will stop 'barry' from viewing data in the table 'phnum'.
SQL> revoke select on phnum from barry;
• This will prevent 'barry' from doing anything with the table 'phnum'.
SQL> revoke update, insert, delete on phnum from barry;
Backup data in SSID
Each night eSolutions automatically backs up data within the database. However, it is a good idea to create a backup of your data yourself. Here is a small example of how to do it:
bash$ $ORACLE_HOME/bin/exp file=~/mybackup.dmp
You will be prompted for your username and password. Once this is completed all your data will now be backed up in the file 'mybackup.dmp'.
Restore data from a backup
To restore your data from a backup to the database you will need to do the following:
1. Ensure the tables you want to recover are not in the database:
SQL> select table_name from user_tables;
If the table is in there you will need to drop it before you replace the table from your backup:
2. Insert your table with the following command:
bash$ $ORACLE_HOME/bin/imp file=mybackup.dmp tables=phnum
This command will replace the table phnum and all the data that was in it from the time you performed a backup (as above).
I get the following error when I try to create/insert/update a table: ORA-01536: space quota exceeded for tablespace users
This means that your database quota is full. Each user within SSID has been given 5MB of storage inside the database.
- The TeraTerm connection settings should look like:
- Students will have to provide Oracle with their password. Oracle does not echo anything when entering the password- the system has not hung.
- Ensure you login with just your username, and not your email address.
- Resizing the terminal window is advisable. A good size in the Deakin computer labs is 132 x 50. The Oracle command set linesize 132 is also needed to take advantage of the resized window.
- You can paste into Oracle, however if the command is multi-line Oracle may echo back the line numbers after you paste in the command. This is not a problem. Pasting out of Oracle will not work without editing, because you also get Oracle's line numbers and any other prompts mixed in with it.