Figure 1. Confirm Import Preferences
You can work with the Oracle database management system using Oracle SQL Developer. Extensive documentation for this program is available online:
Oracle SQL Developer is avalable on the Linux systems at Chalmers. To run this program from a terminal window, type:
sqldeveloper
To install SQL Developer under you Chalmers account on the Windows systems, you need to have 500 MB of space available and follow these instructions:
The first time the program is run you will see this message:
Figure 1. Confirm Import Preferences
Select "No".
Next you will see this message:
Figure 2. Oracle Uasge Tracking
I chose not to allow this.
SQL Developer creates some files in $HOME/.sqldeveloper and if those files are present then you won't see these message windows when SQL Developer is run again. If you remove $HOME/.sqldeveloper and its contents then these message windows be shown again the next time you run SQL Developer, and $HOME/.sqldeveloper will be created again.
To create a new database connection, click on "Connections" in the connections navigator (Figure 3), then click on the green "+" symbol.
Figure 3. Connections navigator
Enter the connection information in the database connection dialog box as shown in Figure 4. You should also give your Oracle username and password. Then click on "Connect".
Figure 4. Database connection dialog box
While it is sufficient to work with SQL Developer on the Linux systems at Chalmers, you might find it convenient to install it on your own computer so that you can work on your assignments elsewhere. For information on how to do this, see the Oracle documentation on Installing Oracle SQL Developer.
Note: The Oracle username and password issued via the Fire system is only for connecting to the Oracle server at Chalmers; do not use that username and password anywhere else! In particular, to download SQL Developer from the Oracle web site you might need to register at that site and use a different username and password to access download the software. (added 2014-11-26)
Several people have used SQL Developer to connect to the Oracle server from home via VPN (Virtual Private Network).
Another solution is to use SSH-tunneling. Run this command on Mac/Linux:
ssh -L 1521:db.student.chalmers.se:1521 your_CID@remote11.chalmers.se
Replace 'your cid' by your actual Chalmers user,
when prompted for a password enter your computer password.
And then in SQL Developer, set hostname to localhost instead of db.student.chalmers.se and connect with the Oracle user/password you where given in the course.
On Windows this can be done using Putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/).
If SQL Developer is not already running, you can start it from a terminal window by typing:
sqldeveloper
To connect to the Oracle database, select (e.g. by double-clicking) "Oracle" in the Connections navigator on the left side of the SQL Developer window. This will bring up a dialog box where you can give your Oracle username and password. When this information is entered, a worksheet is opened in the Oracle tab of the main SQL Developer window.
Figure 5. Worksheet
You can type SQL statements into the worksheet (or use your preferred text editor, then copy-and-paste them into the worksheet).
To execute an SQL statement, move your cursor to it and click on the "Run" symbol (green arrow) just above the worksheet. The results of executing the statement will be shown in panel below the worksheet. To execute all statements in the worksheet, click on the "Run Script" symbol to the right of the "Run" symbol.
You can also select a part of a query and press run to run only that part. This is very useful to for instance run a subquery or see the contents of a view without first creating it.
ALTER USER <userName> IDENTIFIED BY <newPassword>;
The following script can be run to delete all of your tables, views and triggers:
begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
end;
/
begin
for c in (select * from user_objects) loop
execute immediate ('drop '||c.object_type||' '||c.object_name);
end loop;
end;
/
If you put this code at the top of your .sql file it will automatically
clean your database when you run the whole script.