Using Oracle 11g Express

Oracle is the market leader in relational database technology. It is based on the same theory and standards as SQL Server, but it has a longer history and is generally considered more mature. You will be using Oracle 11g Express Edition, which is a free version originally intended as a competitor to Microsoft Access. It has a number of restrictions that will not cause any problems for this module.

Before getting started, it is useful to have an image of how you can connect to a database in order to manipulate the data it contains. You can think of the database as an independent system which resides on a server (in this case, that server is your own laptop or virtual PC). Oracle provides a number of different access methods as illustrated below.

We will be mainly using the SQL Developer application to communicate with the database because it combines all of the functions needed for this module. We will also make some use of the SQL*Plus command line and briefly discuss how database operations can be embedded within application programs. We will not be using the APEX (APplication EXpress) interface, but it is mentioned here since it is included by default in the Oracle 11g Express installation.

If possible, you should install Oracle 11g and SQL Developer on your own laptop. You can download them using Further reading, and the video link guides you through the installation process.

If you do not have your own laptop, you will be using the standard instalation on the machines in the JKCC. Please note that if you want to keep any of the work you do in the JKCC, you must save it explicitly to some form of permanent storage (cloud storage, network drive, USB stick, etc.). Any changes that you make will be erased when you quit SQL Developer.

Backups

An important aspect of database administration is ensuring that your data is safe. As you are working on a database, it is entirely possible that an error of some kind will occur that damages one or more database objects, or that corrupts or deletes some of the data. This can be very frustrating and can take a long time to fix. Backing up your database from time to time is therefore good practice.

Oracle provides a variety of backup tools that have advantages and disadvantages in different circumstances. A full discussion of the available options is beyond the scope of this module, and we will only be looking at one scenario which is the schema export using a tool called Data Pump.

Export and import are database administrator (DBA) operations. Before you can do them, you will need to set up a special DBA connection to your database.