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.
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. The account holds an application schema on its own, separate tablespace. Two steps must be performed:
- Create the new tablespace
- Create the user, assign it to the tablespace and provide further roles and permissions
Out of habit I create the changelog in XML format. 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 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:
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 try applying the changelog.
Test
Let’s test the scripts! Using sqlcl, connect to your target environment with an account granted the necessary sys-privs to create the tablespace and user. Then, apply the changelog.
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.
This concludes the operation! As you can see the account has been created successfully. You can see that in Liquibase’s history:
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!
Martins Blog 