I followed instructions from the Oracle documentation to connect to our remote server with a public IP address from home and check that everything was running ok. It is easy when you know how, as usual!
First off, make sure you have enough swap space (over 1GB) to install the Oracle Express Client. I didn’t have enough so had to use a livecd and a third party partition manager to resize my swap partition to a reasonable size.
Next up I downloaded the Oracle Express Client from Oracle themselves (click here to download it) and installed it using the ubuntu package manager. Easiest way, double click on it, click install!
Now the oracle client is installed you have SQL*Plus, a command line interface to your remote and local databases. It isn’t great to use, no command completion, no remembering previous commands but it is the quickest way to get a SQL command line interface.
Open up SQL*Plus (in the main menu > Oracle Client 10g Express Edition > Run SQL Command Line) and now you have a SQL> prompt. This is where you enter “connect” and your connection string and user details etc. For example, connecting as “sys”:
connect firstname.lastname@example.org/instancename as sysdba
The oracle default instance name (if you installed the example database) is “orcl”. So if your password is “foobar” and your ip address is “192.168.1.1″ then you need to type into the command prompt:
connect email@example.com/orcl as sysdba
Of course you can change the login to whoever you want and if you look at the documentation you will find a better way to do this in future using a connection identifier. If you are successful then SQL*Plus will say “Connected.” and you now have a connection, it’s just like being there.
To test out your connection as “sys” try typing the following (thanks Viktor) into the terminal to list all the current schemas/users in the database:
select username from all_users;
You should get a list back and the number of rows selected (about 27 rows if you installed the example database). Now you know how to connect you can run any SQL commands (inc PL/SQL etc) you want plus all the SQL*Plus commands, which I am just beginning to get to grips with.