Working with External Data on Oracle DB Docker
With multi-modal databases (such as Oracle and many more) you will typically work with data in different formats and for different purposes. One such data format is with data located external to the database. The data will exist in files on the operating systems on the DB server or on some connected storage device.
The following demonstrates how to move data to an Oracle Database Docker image and access this data using External Tables. (This based on an example from Oracle-base.com with a few additional commands).
For this example, I’ll be using an Oracle 21c Docker image setup previously. Similarly the same steps can be followed for the 18c XE Docker image, by changing the Contain Id from 21cFull to 18XE.
Step 1 – Connect to OS in the Docker Container & Create Directory
The first step involves connecting the the OS of the container. As the container is setup for default user ‘oracle’, that is who we will connect as, and it is this Linux user who owns all the Oracle installation and associated files and directories
docker exec -it 21cFull /bin/bash
When connected we are in the Home directory for the Oracle user.
The Home directory contains lots of directories which contain all the files necessary for running the Oracle Database.
Next we need to create a directory which will story the files.
As we are logged in as the oracle Linux user, we don’t have to make any permissions changes, as Oracle Database requires read and write access to this directory.
Step 3 – Upload files to Directory on Docker container
Open another terminal window on your computer (desktop/laptop). You should have two such terminal windows open. One you opened for Step 1 above, and this one. This will allow you to easily switch between files on your computer and the files in the Docker container.
Download the two Countries files, to your computer, which are listed on Oracle-base.com. Countries1.txt and Countries2.txt.
Now you need to upload those files to the Docker container.
docker cp Countries1.txt 21cFull:/opt/oracle/ext_data/Countries1.txt docker cp Countries2.txt 21cFull:/opt/oracle/ext_data/Countries2.txt
Step 4 – Connect to System (DBA) schema, Create User, Create Directory, Grant access to Directory
If you a new to the Database container, you don’t have any general users/schemas created. You should create one, as you shouldn’t use the System (or DBA) user for any development work. To create a new database user connect to System.
To use sqlplus command line tool you will need to install Oracle Instant Client and then SQLPlus (which is a separate download from the same directory for your OS)
To create a new user/schema in the database you can run the following (change the username and password to something more sensible).
create user brendan identified by BtPassword1
default tablespace users
temporary tablespace temp;
grant connect, resource to brendan;
alter user brendan quota unlimited on users;
Now create the Directory object in the database, which points to the directory on the Docker OS we created in the Step 1 above. Grant ‘brendan’ user/schema read and write access to this Directory
CREATE OR REPLACE DIRECTORY ext_tab_data AS '/opt/oracle/ext_data';
grant read, write on directory ext_tab_data to brendan;
Now, connect to the brendan user/schema.
Step 5 – Create external table and test
To connect to brendan user/schema, you can run the following if you are still using SQLPlus
SQL> connect brendan/BtPassword1@//localhost/XEPDB1
or if you exited it, just run this from the command line
Create the External Table (same code from oracle-base.com)
CREATE TABLE countries_ext ( country_code VARCHAR2(5), country_name VARCHAR2(50), country_language VARCHAR2(50) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( country_code CHAR(5), country_name CHAR(50), country_language CHAR(50) ) ) LOCATION ('Countries1.txt','Countries2.txt') ) PARALLEL 5 REJECT LIMIT UNLIMITED;
It should create for you. If not and you get an error then if will be down to a typo on directory name or the files are not in the directory or something like that.
We can now query the External Table as if it is a Table in the database.
SQL> set linesize 120 SQL> select * from countries_ext order by country_name; COUNT COUNTRY_NAME COUNTRY_LANGUAGE ----- ------------------------------------ ------------------------------ ENG England English FRA France French GER Germany German IRE Ireland English SCO Scotland English USA Unites States of America English WAL Wales Welsh 7 rows selected.
Oracle 18c XE Docker setup
During August (2021) Gerald Venzi of Oracle released a new set of Docker images and these included Oracle 18c XE Database. Check out Gerald’s blog post about this for a lot more details on these images. Great work Gerald, and it’s way simpler to set this up compared to previous.
The following is really just a reminder to myself of the commands needed to install and run one of the 18c XE docker images.
Gerald has provided 3 different versions of 18c XE Database. Check out his blog post for more details of what is included/excluded in each image.
I decided to go with the FULL docker image (oracle-xe-full), just because I use most of the DB features and like to play around with the rest. If you just want a Database then go with the medium or small sized docker images
|Docker Image Name||Description|
|oracle-xe-full||Contains full Oracle 18c XE Database installation. Containing all the bells and whistles. This is the largest docker image.|
|oralce-xe||This medium sized image has some things stripped out from the installation. Contains most of the functionality from the full image, but some of the edge case functionality has been removed.|
|oracle-xe-slim||This is the smallest image and has a lot of extra features remove. Probably only suitable if you want a basic Database.|
Before you run the following commands you will need to install Docker.
Step 1: Download the 18c XE image
docker pull gvenzl/oracle-xe
Step 2: Check the image exist in your Docker env
Step 3: Run the image
docker run -d -p 1521:1521 -e ORACLE_PASSWORD=SysPassword1 -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-xe
This command remaps the 1521 port to local 1521, changed/set the password and gives volume details to all any changes to the database and image to be persisted i.e. when you restart the image your previous work will be there
Step 4: Rename image [you can skip this step if you want. I just wanted a different name]
docker rename d95a3db95747 18XE
NB: Use the code/reference for your docker image. It will be different to mine (d95a3db95747)
Step 5: Connect to the Database as DBA/Admin schema
You can use SQL*Plus or some other client side tool to connect to the database
A simple query to check we are connected to the database.
select username from dba_users;
Step 6: Create your own (developer) Schema
create user demo identified by demo quota unlimited on users;
grant connect, resource to demo;
Exit SQL*Plus and log back into the Database using the DEMO schema you just created.
Step 7: Create a Table and enter some Records
create table test (col1 NUMBER, col2 VARCHAR2(10));
insert into test values (1, 'Brendan');
Step 8: Test the Docker image persists the data
Stop the docker image
docker stop 18XE
Check it is no-longer running
Nothing will be displayed
Step 9: Start the 18XE Docker image and Check data was persisted
docker start 18XE
You should see the docker image is running
sqlplus demo/demo@//localhost/XEPDB1 select table_name from user_tables; select * from test;
These last two commands should show the table and the record in the table. This means the data was persisted.
All done you now have a working Docker image of Oracle 18XE running.
Just remember to stop the image when you don’t need it on your computer. These will save you some resource usage.
Installing and configuring Oracle 18c XE
The following are the simple steps required to install Oracle 18c XE (express edition) on Oracle Linux. Check out my previous blog post on Oracle 18c XE. Also check out the product webpage for more details and updates. There is a very important word on that webpage. That word is ‘FREE’ and is something you don’t see too often. Go get and use the (all most) full enterprise version of the Oracle Database.
I’ve created a VM using Oracle Linux for the OS.
After setting up the VM, login as root and download the RPM file.
Run the following as root to perform dependency checks and configurations.
yum install -y oracle-database-preinstall-18c
You can now run the install using the following command.
yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm.
When the install has completed, the next step is to install the database. This is done using the following command.
You will be prompted to enter a common password for the SYS, SYSTEM and PDBADMIN users. You will need to change these at a later time.
Then to start the database, run
systemctl start oracle-xe-18c
The next time you restart the VM, you might find that the database hasn’t started or loaded. You will need to do this manually. This is a bit of a pain in the behind.
To avoid having to do this each time, run the following commands as root.
systemctl daemon-reload systemctl enable oracle-xe-18c
These commands will allow the database to be shutdown when the machine or VM is being shutdown and will automatically start up the database when the machine/VM startups again.
The final step is to connect to the database
sqlplus sys///localhost:1521/XE as sysdba
You can then go and perform all your typical admin tasks, set up SQLDeveloper, and create additional users.
Bingo! All it good now.
Putting Oracle 18c XE on docker is an excellent way to make it easily deployable and to build out solutions that require a DB.
Check out these links for instructions on how to setup a Docker container with Oracle 18c XE.