Using SQLcl Liquibase
This post summarizes the most useful SQLcl Liquibase commands and how they can be used. The output of the commands are shown as well.
Table of Contents
Overview
Liquibase is a database schema change management tool that helps developers track, version, and deploy database changes in a controlled and consistent way. It allows teams to automate database updates, rollbacks, and migrations across different environments (e.g., development, testing, production).
Environment
- Oracle 19.3
Using Liquibase
Capture and deploy a single table:
-- connect to the source schema
conn -n win3_orcl_user1
create table t as select * from all_source where 1=0;
desc t
-- Capture table t, overwrite existing xml files (-ovf)
lb generate-db-object -ovf -obt table -obn t
-- connect to the destination schema
conn -n win3_orcl_user2
desc t
-- Deploy the object in another schema
lb update -changelog-file t_table.xml
desc t
-- add a column to the source table t
conn -n win3_orcl_user1
alter table t add (newcolumn number);
-- capture the changes to table t into a new changelog file
lb geo -obt table -ovf -obn t
-- apply the change to user2.t
conn -n win3_orcl_user2
lb update -changelog-file t_table.xml
desc t
Screen Output (click):
SQL> -- connect to the source schema
SQL> conn -n win3_orcl_user1
Connected.
SQL> create table t as select * from all_source where 1=0;
Table T created.
SQL> desc t
Name Null? Type
________________ ________ _________________
OWNER VARCHAR2(128)
NAME VARCHAR2(128)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
ORIGIN_CON_ID NUMBER
SQL> -- Capture table t, overwrite existing xml files (-ovf)
SQL> lb generate-db-object -ovf -obt table -obn t
--Starting Liquibase at 2025-07-20T16:08:07.752148600 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Changelog created and written out to file t_table.xml
Operation completed successfully.
SQL> -- connect to the destination schema
SQL> conn -n win3_orcl_user2
Connected.
SQL> desc t
ERROR:
ORA-04043: object t does not exist
SQL> -- Deploy the object in another schema
SQL> lb update -changelog-file t_table.xml
--Starting Liquibase at 2025-07-20T16:08:09.219823400 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t_table.xml::37399abaae1ee767cb1084484729d0e4a0b9c6da::(USER1)-Generated
Table "T" created.
UPDATE SUMMARY
Run: 1
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 1
Liquibase: Update has been successful. Rows affected: 1
Operation completed successfully.
SQL> desc t
Name Null? Type
________________ ________ _________________
OWNER VARCHAR2(128)
NAME VARCHAR2(128)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
ORIGIN_CON_ID NUMBER
SQL> -- add a column to the source table t
SQL> conn -n win3_orcl_user1
Connected.
SQL> alter table t add (newcolumn number);
Table T altered.
SQL> -- capture the changes to table t into a new changelog file
SQL> lb geo -obt table -ovf -obn t
--Starting Liquibase at 2025-07-20T16:08:13.998278100 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Changelog created and written out to file t_table.xml
Operation completed successfully.
SQL> -- apply the change to user2.t
SQL> conn -n win3_orcl_user2
Connected.
SQL> lb update -changelog-file t_table.xml
--Starting Liquibase at 2025-07-20T16:08:15.058325600 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t_table.xml::b923121747e8e49f504d399b58e46243e7aa49d7::(USER1)-Generated
Table "T" altered.
UPDATE SUMMARY
Run: 1
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 1
Liquibase: Update has been successful. Rows affected: 1
Operation completed successfully.
SQL> desc t
Name Null? Type
________________ ________ _________________
OWNER VARCHAR2(128)
NAME VARCHAR2(128)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
ORIGIN_CON_ID NUMBER
NEWCOLUMN NUMBER
SQL>Capture and deploy a complete schema
conn -n win3_orcl_user1
-- create a second table
create table t2 as select * from t where 1=0;
-- Capture the complete schema
lb ges
-- Deploy the changelog file to user2
conn -n win3_orcl_user2
lb update -chf controller.xml
select table_name from user_tables;
Sample Output (click):
SQL> conn -n win3_orcl_user1
Connected.
SQL> -- create a second table
SQL> create table t2 as select * from t where 1=0;
Table T2 created.
SQL> -- Capture the complete schema
SQL> lb ges
--Starting Liquibase at 2025-07-20T16:13:57.610030300 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 355 ms
[Type - TYPE_BODY]: 174 ms
[Type - SEQUENCE]: 61 ms
[Type - DIRECTORY]: 54 ms
[Type - CLUSTER]: 1966 ms
[Type - TABLE]: 18251 ms
[Type - MATERIALIZED_VIEW_LOG]: 63 ms
[Type - MATERIALIZED_VIEW]: 37 ms
[Type - VIEW]: 3837 ms
[Type - REF_CONSTRAINT]: 196 ms
[Type - DIMENSION]: 62 ms
[Type - PACKAGE_SPEC]: 113 ms
[Type - FUNCTION]: 107 ms
[Type - PROCEDURE]: 106 ms
[Type - DB_LINK]: 63 ms
[Type - SYNONYM]: 77 ms
[Type - INDEX]: 3097 ms
[Type - TRIGGER]: 379 ms
[Type - PACKAGE_BODY]: 152 ms
[Type - JOB]: 71 ms
[Method loadCaptureTable]: 29221 ms
[Method sortCaptureTable]: 40 ms
[Method writeChangeLogs]: 14 ms
Changelog created and written out to file controller.xml
Operation completed successfully.
SQL> -- Deploy the changelog file to user2
SQL> conn -n win3_orcl_user2
Connected.
SQL> lb update -chf controller.xml
--Starting Liquibase at 2025-07-20T16:14:28.420219 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t2_table.xml::fbbba76b8e2f2f49a75698fd600155c8ebb2fb38::(USER1)-Generated
Table "T2" created.
UPDATE SUMMARY
Run: 1
Previously run: 1
Filtered out: 0
-------------------------------
Total change sets: 2
Liquibase: Update has been successful. Rows affected: 1
Operation completed successfully.
SQL> select table_name from user_tables;
TABLE_NAME
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
T
T2
5 rows selected.
SQL>Rollback to a previous version
conn -n win3_orcl_user2
select table_name from user_tables;
-- tag the current version
lb ta -ta before_change
-- make and deploy a change
conn -n win3_orcl_user1
create table t3 as select * from t where 1=0;
lb ges
conn -n win3_orcl_user2
lb update -chf controller_1.xml
select table_name from user_tables;
-- rollback to the previous version
lb rb -chf controller_1.xml -ta before_change
select table_name from user_tables;
Sample Output (click):
SQL> conn -n win3_orcl_user2
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
T
T2
5 rows selected.
SQL> -- tag the current version
SQL> lb ta -ta before_change
--Starting Liquibase at 2025-07-20T16:22:27.651142800 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Successfully tagged 'USER2@jdbc:oracle:thin:@win3.fritz.box:1521/orcl.fritz.box'
Operation completed successfully.
SQL> -- make and deploy a change
SQL> conn -n win3_orcl_user1
Connected.
SQL> create table t3 as select * from t where 1=0;
Table T3 created.
SQL> lb ges
--Starting Liquibase at 2025-07-20T16:22:29.378403200 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 99 ms
[Type - TYPE_BODY]: 69 ms
[Type - SEQUENCE]: 61 ms
[Type - DIRECTORY]: 33 ms
[Type - CLUSTER]: 55 ms
[Type - TABLE]: 18006 ms
[Type - MATERIALIZED_VIEW_LOG]: 62 ms
[Type - MATERIALIZED_VIEW]: 36 ms
[Type - VIEW]: 3192 ms
[Type - REF_CONSTRAINT]: 198 ms
[Type - DIMENSION]: 62 ms
[Type - PACKAGE_SPEC]: 50 ms
[Type - FUNCTION]: 53 ms
[Type - PROCEDURE]: 51 ms
[Type - DB_LINK]: 40 ms
[Type - SYNONYM]: 41 ms
[Type - INDEX]: 64 ms
[Type - TRIGGER]: 42 ms
[Type - PACKAGE_BODY]: 71 ms
[Type - JOB]: 73 ms
[Method loadCaptureTable]: 22358 ms
[Method sortCaptureTable]: 23 ms
[Method writeChangeLogs]: 28 ms
Changelog created and written out to file controller_1.xml
Operation completed successfully.
SQL> conn -n win3_orcl_user2
Connected.
SQL> lb update -chf controller_1.xml
--Starting Liquibase at 2025-07-20T16:22:53.006321300 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t3_table.xml::ae261bebfc52f1baa82c17a68e2c95eb22fbacf3::(USER1)-Generated
Table "T3" created.
UPDATE SUMMARY
Run: 1
Previously run: 2
Filtered out: 0
-------------------------------
Total change sets: 3
Liquibase: Update has been successful. Rows affected: 1
Operation completed successfully.
SQL> select table_name from user_tables;
TABLE_NAME
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
T
T2
T3
6 rows selected.
SQL> -- rollback to the previous version
SQL> lb rb -chf controller_1.xml -ta before_change
--Starting Liquibase at 2025-07-20T16:22:55.052740100 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Rolling Back Changeset: t3_table.xml::ae261bebfc52f1baa82c17a68e2c95eb22fbacf3::(USER1)-Generated
Table "T3" dropped.
Operation completed successfully.
SQL> select table_name from user_tables;
TABLE_NAME
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
T
T2
5 rows selected.
SQL>Include table data in the capture and deploy
-- create a table and data
conn -n win3_orcl_user1
drop table t;
create table t (a number);
insert into t values (1);
commit;
select count(*) from t;
-- generate changelog files
lb generate-db-object -ovf -obt table -obn t
lb da -ino t -ouf t_data.xml -ovf
conn -n win3_orcl_user2
-- drop all objects in the target schema
lb drop-all
-- create the table and insert data
lb update -changelog-file t_table.xml
lb up -chf t_data.xml
select count(*) from t;
Sample Output (click):
SQL> -- create a table and data
SQL> conn -n win3_orcl_user1
Connected.
SQL> drop table t;
Table T dropped.
SQL> create table t (a number);
Table T created.
SQL> insert into t values (1);
1 row inserted.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
___________
1
1 row selected.
SQL> -- generate changelog files
SQL> lb generate-db-object -ovf -obt table -obn t
--Starting Liquibase at 2025-07-20T16:25:36.247148900 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Changelog created and written out to file t_table.xml
Operation completed successfully.
SQL> lb da -ino t -ouf t_data.xml -ovf
--Starting Liquibase at 2025-07-20T16:25:36.627796400 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed. Some database objects and their dependencies cannot be represented automatically, and they may need to be manually updated before being deployed.
Command completed successfully please review file c:/sw/sqlcl/bin/t_data.xml
Operation completed successfully.
SQL>
SQL> conn -n win3_orcl_user2
Connected.
SQL> -- drop all objects in the target schema
SQL> lb drop-all
--Starting Liquibase at 2025-07-20T16:25:38.815844600 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
INFO: The drop-all command may result in unrecoverable destructive changes to objects at 'jdbc:oracle:thin:@win3.fritz.box:1521/orcl.fritz.box'.
To protect against unwanted drops, set --requireForce=true, which will require a --force=true flag on the command.
Learn more at https://docs.liquibase.com/dropall.
All objects dropped from USER2@jdbc:oracle:thin:@win3.fritz.box:1521/orcl.fritz.box
Operation completed successfully.
SQL> -- create the table and insert data
SQL> lb update -changelog-file t_table.xml
--Starting Liquibase at 2025-07-20T16:26:06.298589500 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t_table.xml::6208a66ed33d5914197245d5dd723a25496e3de0::(USER1)-Generated
Table "T" created.
UPDATE SUMMARY
Run: 1
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 1
Liquibase: Update has been successful. Rows affected: 1
Operation completed successfully.
SQL> lb up -chf t_data.xml
--Starting Liquibase at 2025-07-20T16:26:10.122801400 using Java 17.0.11 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: t_data.xml::1753021537705-1::User1 (generated)
UPDATE SUMMARY
Run: 1
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 1
Liquibase: Update has been successful. Rows affected: 2
Operation completed successfully.
SQL> select count(*) from t;
COUNT(*)
___________
1
1 row selected.
SQL>Cleaning up
These commands clean up the changes done by the previous examples.
conn -n win3_orcl_user2
lb drop-all
conn -n win3_orcl_user1
drop table t;
drop table t2;
drop table t3;
The .xml files can also be deleted.
Leave a Reply