In this blog, Digia's expert Jani Hurskainen shares his views on the joys of Oracle database programming and hands out a quick start guide for developers to get Oracle database up and running locally.
Recently I had an opportunity to return to the joys of Oracle database programming with PL/SQL after a long break. I have always found the local database installation handy, so I was looking for the options for today to install the most current free Oracle Express Edition (aka Oracle XE) database management system in Ubuntu-based Linux distros.
Note that this is not a detailed DBA guide but a quick start guide for developers to get Oracle database up and running locally with reasonable (IMO) defaults without any previous knowledge.
The easiest way to use Oracle XE in Ubuntu-based Linux distros is the Docker container provided by Oracle. The container can be found in Oracle's container registry:
https://container-registry.oracle.com/
> Database
> express
The page above also contains usage instructions but because I had not used Docker before I had to study the Docker basics a little bit before I was able to record the transcript below.
Note that at the time of writing Oracle version 18.4.0.0 is the latest and that's what is referred to here by the latest Docker tag.
I used Lubuntu 20.04 LTS for the guide but the steps should apply to any Ubuntu derivative.
Install:
sudo apt install docker.io
Basic Docker commands I found useful:
# download image
sudo docker pull <IMAGE>
# list all images
sudo docker image ls
# create new (start/run) container from the image
sudo docker run --detached --name <NAME> <IMAGE>
# list all (both running and stopped) containers
sudo docker ps --all
# stop container
sudo docker stop <NAME>
# restart stopped container
sudo docker start <NAME>
# remove container
sudo docker rm <NAME>
# show container logs
sudo docker logs <NAME>
# list container port mappings
sudo docker port <NAME>
Note that in Ubuntu one must always use sudo with Docker.
The image is huge so it might take some time with slow connections:
sudo docker pull container-registry.oracle.com/database/express:latest
With this method the database can't be accessed outside of the container:
sudo docker run \
--detached \
--name <NAME> \
container-registry.oracle.com/database/express:latest
The passwords for SYS, SYSTEM and PDBADMIN are created automatically during the container setup and can be found from the logs:
$ sudo docker logs <NAME> | head 1
ORACLE PASSWORD FOR SYS AND SYSTEM: <PASSWORD>
Now the database can be accessed by running sqlplus inside the container:
# connect to CDB$ROOT as sysdba
sudo docker exec --interactive --tty <NAME> sqlplus sys/<PASSWORD>@xe as sysdba
# connect to CDB$ROOT as non-sysdba
sudo docker exec --interactive --tty <NAME> sqlplus system/<PASSWORD>@xe
# connect to the XEPDB1 (pluggable) database
sudo docker exec --interactive --tty <NAME> sqlplus pdbadmin/<PASSWORD>@xepdb1
That's nice but even nicer would be to access the database from the outside of the container with your preferred tools!
What I really want to do is to access the database from the outside of the Docker container.
The Docker container has several configuration points that you can tailor to your needs. In the commands below:
--publish 1521:1521 exposes Oracle Listener port.--publish 5500:5500 exposes Oracle EM that should be accessed https://localhost:5500/em/ but I just ran into the following error with the latest Firefox: "Secure Connection Failed - An error occurred during a connection to localhost:5500. PR_END_OF_FILE_ERROR". I simply gave up as I didn’t need Oracle EM --env ORACLE_PWD=<PASSWORD> sets the password for SYS, SYSTEM and PDBADMIN --volume /var/lib/oracle/<NAME>:/opt/oracle/oradata bind mounts directory /var/lib/oracle/<NAME> on the host machine into a container /opt/oracle/oradata Note that the host directory must exist and be writable by the container's Oracle user.The command:
# create the host directory
sudo mkdir -p /var/lib/oracle/<NAME>
# make the directory writable by container's Oracle user
sudo chmod a+w /var/lib/oracle/<NAME>
# start the container
sudo docker run \
--detached \
--name <NAME> \
--publish 1521:1521 \
--publish 5500:5500 \
--env ORACLE_PWD=<PASSWORD> \
--volume /var/lib/oracle/<NAME>:/opt/oracle/oradata \
container-registry.oracle.com/database/express:latest
Other possible configurations are:
ORACLE_CHARACTERSET but I found the default (AL32UTF8) fine.Now I can use Oracle SQLcl (or SQL Developer) to access the database:
# sqlcl below is just a handy alias for the actual Oracle SQLcl executable
# connect to CDB$ROOT as sysdba
sqlcl -noupdates -LOGON sys/<PASSWORD>@//localhost:1521/XE as sysdba
# connect to CDB$ROOT as non-sysdba
sqlcl -noupdates -LOGON system/<PASSWORD>@//localhost:1521/XE
# connect to the XEPDB1 pluggable database
sqlcl -noupdates -LOGON pdbadmin/<PASSWORD>@//localhost:1521/XEPDB1
I'm not going into Oracle DBMS server details here but only show how to use the ready-made XEPDB1 pluggable database.
sqlcl -noupdates -LOGON sys/<PASSWORD>@//localhost:1521/XE as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XEPDB1 READ WRITE NO
SQL> alter session set container = xepdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
XEPDB1
SQL>
SQL> create user foo identified by foo;
User FOO created.
SQL> grant create session to foo;
Grant succeeded.
SQL> alter user foo default tablespace users temporary tablespace temp;
User FOO altered.
SQL> grant unlimited tablespace to foo;
Grant succeeded.
SQL> grant create table to foo;
Grant succeeded.
sqlcl -noupdates -LOGON foo/foo@//localhost:1521/XEPDB1
SQL> show con_name
CON_NAME
------------------------------
XEPDB1
SQL>
SQL> create table a(b number(1));
Table A created.
SQL> insert into a values(1);
1 row inserted.
SQL> commit;
Commit complete.
SQL> select * from a;
B
----------
1
SQL>
Now all you need to do is enjoy your local Oracle database instance!