Occasionally it is necessary to perform administrative tasks in a (Pluggable) Database. Especially in the context of CI (Continuous Integration) pipelines you may want to perform certain setup tasks up front. This posts shows how easy that can be with Oracle Database, sqlcl and it’s built-in support for Liquibase.

Why Liquibase? Chances are you are using Liquibase as your schema migration tool. It sounds logical to perform administrative work with Liquibase support as well – although you’d certainly need a different account, with elevated privileges. I refrain from using such an account from deploying the application.

As a rule of thumb, don’t use generic (sometimes called system accounts) with elevated privileges to deploy your application code.

The setup

The following software was used when writing this article:

  • Oracle SQLDeveloper Command-Line (SQLcl) version: 24.2.0.0 build: 24.2.0.180.1721
  • Oracle Database 19c

User creation

Let’s assume you need to create a new account in your database for a greenfield project. The account holds an application schema on its own, separate tablespace. Two steps must be performed to achieve this goal:

  1. Create the new tablespace
  2. Create the user, assign it to the tablespace and provide further roles and permissions

The format used for the (main) changelog does not matter too much. This one has been generated by lb generate-controlfile -output-file controller.xml and updated to suit the project’s needs. It references all the files to be executed:

<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
  <include file="create_tablespace.sql"/> 
  <include file="create_user.sql"/> 
</databaseChangeLog>

Both scripts referenced in the include directive use the SQL format. The create_tablespace.sql script assumes that db_create_file_dest is set.

--liquibase formatted sql
--changeset martincarstenbach:1 failOnError:true --labels:demo

create tablespace my_tablespace
datafile size 100m;

The user creation in create_user.sql references the newley created tablespace:

--liquibase formatted sql
--changeset martincarstenbach:2 failOnError:true --labels:demo

create user my_user 
    identified by values 'S:F329AD0794FBDC33BF2A27B578700E...881'   
    default tablespace my_tablespace
    quota 10m on my_tablespace
    temporary tablespace temp;

grant create sequence to my_user;          
grant create procedure to my_user;         
grant create cluster to my_user;           
grant create indextype to my_user;         
grant create operator to my_user;          
grant create type to my_user;              
grant create trigger to my_user;           
grant create table to my_user; 
grant create session to my_user;

With the setup work complete it’s time to apply the changelog.

Test

Let’s test the scripts! Using sqlcl, connect to your target environment with an account granted the necessary privileges to create the tablespace and user. Then, apply the changelog. Here is some example output

SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 24.2.0.0 build: 24.2.0.180.1721

SQL> lb tag -tag demotag
--Starting Liquibase at 2024-07-05T13:12:55.448253 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Successfully tagged 'SYSTEM@jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=devpdb1)))'


Operation completed successfully.

SQL> lb update -changelog-file controller.xml
--Starting Liquibase at 2024-07-05T13:13:21.340159 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: create_tablespace.sql::1::martincarstenbach
Running Changeset: create_user.sql::2::martincarstenbach

UPDATE SUMMARY
Run:                          2
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            2

Liquibase: Update has been successful. Rows affected: 2


Operation completed successfully.

Both changesets have been successfull applied. Activity tracking is without a shadow of a doubt one of Liquibase’s great advantages. You can see the history on the command line. If you connected Oracle REST Data Services to your database you can view the Liquibase History in your browser.

SQL> lb history
--Starting Liquibase at 2024-07-05T13:13:29.662790 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Liquibase History for jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=devpdb1)))
- Database updated at 05/07/2024, 11:12. Applied 1 changeset(s), DeploymentId: 0177975536
  liquibase-internal::1720177975721::liquibase

- Database updated at 05/07/2024, 11:13. Applied 2 changeset(s) in 0.118s, DeploymentId: 0178001811
  create_tablespace.sql::1::martincarstenbach
  create_user.sql::2::martincarstenbach




Operation completed successfully.

Happy automating!