3 Liquibase Support in SQLcl

This chapter covers the Liquibase feature in SQLcl. It has the following topics:

• About Liquibase in SQLcl
• Requirements for Using Liquibase
• Supported Types
• Supported Liquibase Commands in SQLcl
• DATABASECHANGELOG_DETAILS VIEW
• ChangeSets in Liquibase
• Examples Using Liquibase

3.1 About Liquibase in SQLcl

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes.

For an understanding of the major concepts in Liquibase, see Major Concepts.

Note:

Liquibase is not available in SQLcl that is part of the SQL Developer installation. To use this feature, you need to download the standalone SQLcl offering.

The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs). You can process these objects manually using SQLcl or through any of the traditional Liquibase interfaces.

With the Liquibase feature in SQLcl, you can:

• Generate and execute single object changelogs
• Generate and execute schema changesets with object dependencies
• Automatically sort a changeset during creation based on object dependencies
• Record all SQL statements for changeset or changelog execution, as it is generated
• Provide full rollback support for changesets and changelogs automatically

3.2 Requirements for Using Liquibase

The two important aspects for using the Liquibase functionality are capturing and deploying objects in an Oracle database.

Capture Objects

To capture an object or a schema, you must have SQLcl 19.2 or later installed.

In this release, you can only capture objects from the schema you are connected to in SQLcl. You also need write permission on the directory in which you save the files.

If you are capturing an entire schema, the user you are connected to must have the privilege to create a table. The DATABASECHANGELOG_EXPORT table is created internally to gather object details and sort them correctly. The created object is automatically excluded from the capture process and destroyed upon capture completion.

Deploy Objects

Liquibase uses the DATABASECHANGELOG table to track the changesets that have been run. The DATABASECHANGELOGLOCK table ensures that only one instance of Liquibase is running at a time. The DATABASECHANGELOG_ACTIONS table tracks the object state and the SQL statements executed during deployment.

• SQLcl

  Deploying changes to any database through SQLcl requires the 19.2 release or later and the privilege to create a table. You must have necessary permissions to create any object type through the change that you are deploying.

• Liquibase

  If you use Liquibase directly to deploy changesets, you need to have:

  • the extension installed in your Liquibase environment. Add the dbtools-liquibase.jar file in liquibase/lib/ext.
  • the privileges to create a table and a package.

3.3 Supported Types

DDL types use create or replace syntax. A snapshot of the object is taken before applying the change so automatic rollback to the last known state is supported.

SXML types support automatic alter generation with automatic rollback support.

DDL types have their own change type.

• CONSTRAINT
• DIMENSION
• DIRECTORY
• FUNCTION
• JOB
• OBJECT_GRANT
• PACKAGE_BODY
• PACKAGE_SPEC
• PROCEDURE
• PUBLIC_SYNONYM
• REF_CONSTRAINT
• SYNONYM
• TRIGGER
• TYPE BODY
• TYPE SPEC

SXML types share the SXML change type.

• AQ_QUEUE
• AQ_QUEUE_TABLE
• AQ_TRANSFORM
• ASSOCIATION
• AUDIT
• AUDIT_OBJ
• CLUSTER
• CONTEXT
• DB_LINK
• DEFAULT_ROLE
• FGA_POLICY
• JOB
• LIBRARY
• MATERIALIZED_VIEW
• MATERIALIZED_VIEW_LOG
• OPERATOR
• PROFILE
• PROXY
• REFRESH_GROUP
• RESOURCE_COST
• RLS_CONTEXT
• RLS_GROUP
• RMGR_CONSUMER_GROUP
• RMGR_INTITIAL_CONSUMER_GROUP
• RMGR_PLAN
• RMGR_PLAN_DIRECTIVE
• ROLE
• ROLLBACK_SEGMENT
• SEQUENCE
• TABLE
• TABLESPACE
• TRIGGER XS_ACL
• TRUSTED_DB_LINK
• USER
• VIEW
• XMLSCHEMA
• XS_ACL_PARAM INDEX
• XS_DATA_SECURITY
• XS_ROLE
• XS_ROLESET
• XS_ROLE_GRANT
• XS_SECURITY_CLASS
• XS_USER

3.4 Supported Liquibase Commands in SQLcl

You can invoke the Liquibase commands in SQLcl with liquibase or lb.

To display a list of all available commands, execute liquibase or lb with no arguments.

• LB GENOBJECT
• LB GENSCHEMA
• LB GENCONTROLFILE
• LB UPDATE
• LB UPDATESQL
• LB ROLLBACK
• LB ROLLBACKSQL
• LB DIFF
• LB DBDOC
• LB CHANGELOGSYNC
• LB CLEARCHECKSUMS
• LB LISTLOCKS
• LB RELEASELOCKS
• LB STATUS
• LB VALIDATE
• LB VERSION

3.4.1 LB GENOBJECT

Generates change logs for APEX, ORDS RESTful Service Modules, or database objects.

APEX

Syntax

lb genobject -type {APEX} -applicationid APPLICATIONID -workspaceid WORKSPACEID 
[-instance] [-expWorkspace][-expMinimal] [-expFiles] [-skipExportDate] [-expPubReports] [-expSavedReports] 
[-expIRNotif] [-expTranslations] [-expFeedback] [-expTeamdevdata] [-deploymentSystem DEPLOYMENTSYSTEM] 
[-expFeedbackSince EXPFEEDBACKSINCE] [-expOriginalIds] [-expNoSubscriptions] [-expComments] 
[-expSupportingObjects {Y,N,I}] [-expACLAssignments] [-dir DIR] [-list LIST] 
[-changesSince CHANGESSINCE] [-changesBy CHANGESBY] [-nochecksum] [-split] 
[-expComponents EXPCOMPONENTS] [-context CONTEXT] [-label LABEL] 
[-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]

Generates a change log for APEX objects.

Named Parameters

Parameter	Description	Default
-type {APEX}	Generate output for APEX objects.	-
-applicationid APPLICATIONID	Specify ID for application to be exported.	-
-workspaceid WORKSPACEID	Specify workspace ID for all applications to be exported or the workspace to be exported.	-
-instance	Export all applications.	False
-expWorkspace	Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified.	False
-expMinimal	Export only workspace definition, users, and groups.	False
-expFiles	Export all workspace files identified by -workspaceid.	False
-skipExportDate	Exclude export date from application export files	False
-expPubReports	Export all user-saved public interactive reports.	False
-expSavedReports	Export all user-saved interactive reports.	False
-expIRNotif	Export all interactive report notifications.	False
-expTranslations	Export the translation mappings and all text from the translation repository.	False
-expFeedback	Export team development feedback for all workspaces or identified by -workspaceid.	False
-expTeamdevdata	Export team development data for all workspaces or identified by -workspaceid.	False
-deploymentSystem DEPLOYMENTSYSTEM	Specify deployment system for exported feedback.	-
-expFeedbackSince EXPFEEDBACKSINCE	Export team development feedback since date in the format YYYYMMDD.	-
-expOriginalIds	Export emits ids as they were when the application was imported.	False
-expNoSubscriptions	Does not export references to subscribed components.	False
-expComments	Export developer comments.	False
-expSupportingObjects {Y,N,I}	Export supporting objects. Pass (Y)es, (N)o or (I)nstall to override the default (default: N).	N
-expACLAssignments	Export ACL User Role Assignments.	False
-dir DIR	Save all files in the given directory. For example, -dir some/sub/directory.	-
-list LIST	List all changed applications in the workspace or components in the application.	-
-changesSince CHANGESSINCE	Expect date parameter (yyyy-mm-dd). Limit -list values to changes since the given date.	-
-changesBy CHANGESBY	Expect string parameter. Limit -list values to changes by the given user.	-
-nochecksum	Overwrite existing files even if the contents have not changed.	False
-split	Split applications into multiple files.	False
-expComponents EXPCOMPONENTS	Export application components. All remaining parameters must be of form TYPE:ID.	-

Common Generator Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	False

Example

SQL> lb genobject -type apex -applicationid 4900
Exporting Application 4900
Action successfully completed please review created file f4900.xml

ORDS RESTful Service Modules

Syntax

lb genobject -type {ORDS} -name NAME [-noenable] [-noprivs] 
[-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] 
[-replace] [-runonchange] [-runalways] [-debug]

Generates a change log for an ORDS (Oracle REST Data Services) RESTful service module.

Named Parameters

Parameter	Description	Default
-type {ORDS}	Generate output for ORDS module	-
-name NAME	Module name	-
-noenable	Dictates whether the enable_schema call is included in the export	True
-noprivs	Dictates whether Privs is included in the export	True

Common Generator Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	False

Database Object

Syntax

lb genobject -type TYPE -name NAME 
[-context CONTEXT] [-label LABEL] [-emit_schema] 
[-fail] [-replace] [-runonchange] [-runalways] [-debug]

Generates a change log for a specified database object.

Named Parameters

Parameter	Description
-type TYPE	Type of object in the database
-name NAME	Object name

Common Generator Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	False

Database Object Types Supported

Type	Type	Type
AQ_QUEUE	AQ_QUEUE_TABLE	AQ_TRANSFORM
ASSOCIATION	AUDIT	AUDIT_OBJ
CLUSTER	CONSTRAINT	CONTEXT
DB_LINK	DEFAULT_ROLE	DIMENSION
FGA_POLICY	FUNCTION	INDEX
JOB	LIBRARY	MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG	OBJECT_GRANT	OPERATOR
PACKAGE_SPEC	PACKAGE_BODY	PROCEDURE
PROFILE	PROXY	PUBLIC_SYNONYM
REF_CONSTRAINT	REFRESH_GROUP	RESOURCE_COST
RLS_CONTEXT	RLS_GROUP	RLS_POLICY
RMGR_CONSUMER_GROUP	RMGR_PLAN	RMGR_PLAN_DIRECTIVE
ROLE	ROLLBACK_SEGMENT	SEQUENCE
SYNONYM	TABLE	TABLESPACE
TRIGGER	TRUSTED_DB_LINK	TYPE
TYPE_SPEC	TYPE_BODY	USER
VIEW	XMLSCHEMA XS_USER	XS_ROLE
XS_ROLESET	XS_ROLE_GRANT	XS_SECURITY_CLASS
XS_DATA_SECURITY	XS_ACL	XS_ACL_PARAM
XS_NAMESPACE	RMGR_INTITIAL_CONSUMER_GROUP

Example

SQL> lb genobject -type table -name zipcodes 
Action successfully completed please review created file zipcodes_table.xml

3.4.2 LB GENSCHEMA

Generates changelogs and controller file for the connected schema.

Syntax

liquibase(lb) genschema [-noreport] [-synonyms] [-grants] 
[-context CONTEXT] [-label LABEL] [-emit_schema] 
[-fail] [-replace] [-runonchange] [-runalways] [-debug]

Captures the entire schema that the user is connected to in SQLcl. An XML file is created in the current working directory for each object in the schema, and a controller.xml file is created. The controller file is a change log that includes all files in the proper order to enable the schema to be deployed correctly.

Named Parameters

Parameter	Description	Default
-noreport	Suppress screen output	True
-synonyms	Include public synonyms	False
-grants	Include grants	False

Common Generator Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	False

Example

SQL> lb genschema
lb genschema
[Type - TYPE_SPEC]:                          153 ms
[Type - TYPE_BODY]:                          29 ms
[Type - SEQUENCE]:                           48 ms
[Type - CLUSTER]:                            27 ms
[Type - TABLE]:                              36 ms
[Type - MATERIALIZED_VIEW_LOG]:              19 ms
[Type - MATERIALIZED_VIEW]:                  6 ms
[Type - VIEW]:                               148 ms
[Type - REF_CONSTRAINT]:                     272 ms
[Type - DIMENSION]:                          23 ms
[Type - FUNCTION]:                           27 ms
[Type - PROCEDURE]:                          64 ms
[Type - PACKAGE_SPEC]:                       171 ms
[Type - DB_LINK]:                            14 ms
[Type - SYNONYM]:                            22 ms
[Type - INDEX]:                              202 ms
[Type - TRIGGER]:                            51 ms
[Type - PACKAGE_BODY]:                       252 ms
[Method loadCaptureTable]:                   1864 ms
[Method parseCaptureTableRecords]:           7342 ms
[Method sortCaptureTable]:                   30 ms
[Method createExportChangeLogs]:             3 ms

Export Flags Used:
Export Grants               false
Export Synonyms             false

3.4.3 LB GENCONTROLFILE

Generates a blank controller.xml as a sample.

Syntax

liquibase(lb) gencontrolfile

Creates an empty changelog, master.xml, with a placeholder to include files in the current working directory. You can use this command when you are creating your own changelog with custom changeset inclusions.

Example

SQL> lb gencontrolfile
Action successfully completed please review created file controller.xml

3.4.4 LB UPDATE

Applies the specified change log using the current connection.

Syntax

liquibase(lb) update -changelog CHANGELOG [-context CONTEXT] 
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]

Enables you to apply database changes that you and other developers have added to the change log file.

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Common Update Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-log	Generate a log of work performed	False
-debug	Generate and display additional debug output	False

Example

SQL> lb update -changelog zipcodes_table.xml
ScriptRunner Executed:zipcodes_table.xml::3b128595dbfbc34086c0a18ff38e090529999::Generated

3.4.5 LB UPDATESQL

Generates SQL to update the database to the current version.

Syntax

liquibase(lb) updatesql -changelog CHANGELOG [-context CONTEXT] 
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]

Generates and renders to the screen the SQL statements that would be applied for a specific change log.

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Common Generator Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	False

Example

SQL> lb updatesql -changelog zipcodes_tablev2.xml
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: zipcodes_tablev2.xml
-- Ran at: 6/3/20 12:49 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************

-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)', 
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 12:49:05.779', 'YYYY-MM-DD HH24:MI:SS.FF') WHERE ID = 1 AND LOCKED = 0;

-- Changeset zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated
ALTER TABLE "ZIPCODES" ADD ("LON" NUMBER)
/

-- Logging Oracle Extension actions to the Database. 
DECLARE 
id varchar2(200) := '3595dbfbc34086449c0a18ff38e090529999';
rawAction clob;
rawSxml clob;
myrow varchar2(2000);
action clob := '';
sxml clob := '';
dep varchar2(200) := '1202945773';
author varchar2(200) := 'Generated';
filename varchar2(200) := 'zipcodes_tablev2.xml';
insertlog varchar2(200) := 'insert into DATABASECHANGELOG_ACTIONS (id,author,filename,sql,sxml,deployment_id) 
values (:id,:author,:filename,:action,:sxml,:dep) returning rowid into :out';
updateaction varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sql = sql ||:action where rowid = :myrow ';
updatesxml varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sxml = sxml ||:sxml where rowid = :myrow ';
begin 
action := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'
{QUxURVIgVEFCTEUgIlpJUENPREVTIiBBREQgKCJMT04iIE5VTUJFUikKLwogIC0tIE9SQS0zOTM0MTogQ
2Fubm90IGNvbnZlcnQgYW4gZXhpc3RpbmcgY29sdW1uIGludG8gYW4gaWRlbnRpdHkgY29sdW1uLgov}')));
sxml := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'
{PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+PFRBQkxFIHhtbG5zPSJodHRwOi8veG1sbnMub3Jh
Y2xlLmNvbS9rdSIgdmVyc2lvbj0iMS4wIj4KICAgPFNDSEVNQT5TS1VUWjwvU0NIRU1BPgogICA8TkFNRT5aSVBDT0RFUzwvTkFNRT4KICAgPFJFT
EFUSU9OQUxfVEFCTEU+CiAgICAgIDxDT0xfTElTVD4KICAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxOQU1FPklEPC9OQU1FPg
ogICAgICAgICAgICA8REFUQVRZUEU+TlVNQkVSPC9EQVRBVFlQRT4KICAgICAgICAgICAgPE5PVF9OVUxMLz4KICAgICAgICAgPC9DT0xfTElTVF9
JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+Q09ERTwvTkFNRT4KICAgICAgICAgICAgPERBVEFUWVBFPlZBU
kNIQVIyPC9EQVRBVFlQRT4KICAgICAgICAgICAgPExFTkdUSD42PC9MRU5HVEg+CiAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0N
PTVA8L0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BT
UU+Q0lUWTwvTkFNRT4KICAgICAgICAgICAgPERBVEFUWVBFPlZBUkNIQVIyPC9EQVRBVFlQRT4KICAgICAgICAgICAgPExFTkdUSD41MDwvTEVOR1R
IPgogICAgICAgICAgICA8Q09MTEFURV9OQU1FPlVTSU5HX05MU19DT01QPC9DT0xMQVRFX05BTUU+CiAgICAgICAgIDwvQ09MX0xJU1RfSVRFTT4KI
CAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxOQU1FPlNUQVRFPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+VkFSQ0hBUjI
8L0RBVEFUWVBFPgogICAgICAgICAgICA8TEVOR1RIPjUwPC9MRU5HVEg+CiAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0NPTVA8L
0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+Q09
VTlRZPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+VkFSQ0hBUjI8L0RBVEFUWVBFPgogICAgICAgICAgICA8TEVOR1RIPjUwPC9MRU5HVEg+C
iAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0NPTVA8L0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICA
gICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+QVJFQV9DT0RFPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+TlVNQkVSP
C9EQVRBVFlQRT4KICAgICAgICAgICAgPFBSRUNJU0lPTj4zPC9QUkVDSVNJT04+CiAgICAgICAgICAgIDxTQ0FMRT4wPC9TQ0FMRT4KICAgICAgICA
gPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+TEFUPC9OQU1FPgogICAgICAgICAgICA8R
EFUQVRZUEU+TlVNQkVSPC9EQVRBVFlQRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICA8L0NPTF9MSVNUPgogICAgICA8UFJJTUFSWV9
LRVlfQ09OU1RSQUlOVF9MSVNUPgogICAgICAgICA8UFJJTUFSWV9LRVlfQ09OU1RSQUlOVF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxDT0xfTElTV
D4KICAgICAgICAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgICAgICAgIDxOQU1FPklEPC9OQU1FPgogICAgICAgICAgICAgICA8L0N
PTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDwvQ09MX0xJU1Q+CiAgICAgICAgICAgIDxVU0lOR19JTkRFWD4KICAgICAgICAgICAgICAgPElOREVYX
0FUVFJJQlVURVM+CiAgICAgICAgICAgICAgICAgIDxQQ1RGUkVFPjEwPC9QQ1RGUkVFPgogICAgICAgICAgICAgICAgICA8SU5JVFJBTlM+MjwvSU5
JVFJBTlM+CiAgICAgICAgICAgICAgICAgIDxNQVhUUkFOUz4yNTU8L01BWFRSQU5TPgogICAgICAgICAgICAgICAgICA8VEFCTEVTUEFDRT5VU0VSU
zwvVEFCTEVTUEFDRT4KICAgICAgICAgICAgICAgICAgPExPR0dJTkc+WTwvTE9HR0lORz4KICAgICAgICAgICAgICAgPC9JTkRFWF9BVFRSSUJVVEV
TPgogICAgICAgICAgICA8L1VTSU5HX0lOREVYPgogICAgICAgICA8L1BSSU1BUllfS0VZX0NPTlNUUkFJTlRfTElTVF9JVEVNPgogICAgICA8L1BSS
U1BUllfS0VZX0NPTlNUUkFJTlRfTElTVD4KICAgICAgPERFRkFVTFRfQ09MTEFUSU9OPlVTSU5HX05MU19DT01QPC9ERUZBVUxUX0NPTExBVElPTj4
KICAgICAgPFBIWVNJQ0FMX1BST1BFUlRJRVM+CiAgICAgICAgIDxIRUFQX1RBQkxFPgogICAgICAgICAgICA8U0VHTUVOVF9BVFRSSUJVVEVTPgogI
CAgICAgICAgICAgICA8U0VHTUVOVF9DUkVBVElPTl9ERUZFUlJFRC8+CiAgICAgICAgICAgICAgIDxQQ1RGUkVFPjEwPC9QQ1RGUkVFPgogICAgICA
gICAgICAgICA8UENUVVNFRD40MDwvUENUVVNFRD4KICAgICAgICAgICAgICAgPElOSVRSQU5TPjE8L0lOSVRSQU5TPgogICAgICAgICAgICAgICA8T
UFYVFJBTlM+MjU1PC9NQVhUUkFOUz4KICAgICAgICAgICAgICAgPFRBQkxFU1BBQ0U+VVNFUlM8L1RBQkxFU1BBQ0U+CiAgICAgICAgICAgICAgIDx
MT0dHSU5HPlk8L0xPR0dJTkc+CiAgICAgICAgICAgIDwvU0VHTUVOVF9BVFRSSUJVVEVTPgogICAgICAgICAgICA8Q09NUFJFU1M+TjwvQ09NUFJFU
1M+CiAgICAgICAgIDwvSEVBUF9UQUJMRT4KICAgICAgPC9QSFlTSUNBTF9QUk9QRVJUSUVTPgogICA8L1JFTEFUSU9OQUxfVEFCTEU+CjwvVEFCTEU+}')));

execute immediate insertlog using id,author,filename,action,sxml,dep returning into myrow;
end;
/
--;

INSERT INTO JDOE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, 
COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3595dbfbc34086449c0a18ff38e090529999', 
'Generated', 'zipcodes_tablev2.xml', SYSTIMESTAMP, 2, '8:5262bd1c3989655295b5641b82868158', 
'createSxmlObject objectName=ZIPCODES, ownerName=JDOE, '', 'EXECUTED', NULL, NULL, '3.8.9', '1202945773');

-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

3.4.6 LB ROLLBACK

Rolls back the state requested.

Syntax

liquibase(lb) rollback -changelog CHANGELOG [-context CONTEXT] 
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-log] 
[-debug] (-count COUNT | -date DATE)

Rolls back changes starting from the last change applied using the input change log. The count can be higher than the changes in the change log. 999, which is the maximum size, rolls back all the changes.

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Rollback Style Options

Select one of the following options:

Option	Description
-count COUNT	Number of changes to apply
-date DATE	Date to roll back to

Common Update Parameters

Parameter	Description	Default
-context CONTEXT	Specify changeset contexts to execute	-
-label LABEL	Filter the changelog using labels	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-log	Generate a log of work performed	False
-debug	Generate and display additional debug output	False

Example

lb rollback -changelog zipcodes_tablev2.xml -count 100
ScriptRunner Executed:zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated

3.4.7 LB ROLLBACKSQL

Writes SQL to roll back the database to the state requested.

Syntax

liquibase(lb) rollbacksql -changelog CHANGELOG [-context CONTEXT] 
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug] 
(-count COUNT | -date DATE)

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Rollback Style Options

Select one of the following options:

Option	Description
-count COUNT	Number of changes to apply
-date DATE	Date to roll back to

Common Update Parameters

Parameter	Description	Default
-label LABEL	Filter the changelog using labels	-
-context CONTEXT	Specify changeset contexts to execute	-
-emit_schema	Include the schema when generating DDL	False
-fail	Set failOnError value in changeset	False
-replace	Set replaceIfExists value in changeset	False
-runonchange	Set runOnChange value in changeset	False
-runalways	Set runAlways value in changeset	False
-debug	Generate and display additional debug output	-

Example

lb rollbacksql -changelog zipcodes_tablev2.xml -count 100

-- *********************************************************************
-- Rollback 100 Change(s) Script
-- *********************************************************************
-- Change Log: zipcodes_tablev2.xml
-- Ran at: 6/3/20 12:55 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************

-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)', 
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 12:55:19.175', 'YYYY-MM-DD HH24:MI:SS.FF') 
WHERE ID = 1 AND LOCKED = 0;

-- Rolling Back ChangeSet: 
zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated

ALTER TABLE "JDOE"."ZIPCODES" DROP ("LON");

delete from DATABASECHANGELOG_actions where id = '3595dbfbc34086449c0a18ff38e090529999' and 
filename = 'zipcodes_tablev2.xml' and author ='Generated' and sequence = (select Max(sequence) 
from DATABASECHANGELOG_actions where id = '3595dbfbc34086449c0a18ff38e090529999' and 
filename = 'zipcodes_tablev2.xml' and author ='Generated');

DELETE FROM JDOE.DATABASECHANGELOG WHERE ID = '3595dbfbc34086449c0a18ff38e090529999' AND 
AUTHOR = 'Generated' AND FILENAME = 'zipcodes_tablev2.xml';

-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

3.4.8 LB DIFF

Writes a description of differences between two databases to standard output.

Syntax

liquibase(lb) diff -url URL -user USER -pass PASS [-noreport]

Displays differences between the current connection and the specified database. When the report is true, the output will be in the form of a text report. When the report is false, which is the default, the output will be in the form of a change log.

The destination URL format is HOST:PORT:SID or HOST:PORT/SERVICE.

Named Parameters

Parameter	Description	Default
-url URL	JDBC URL for destination database	-
-user USER	Destination user	-
-pass PASS	Destination password	-
-noreport	Results output as a changelog, not a text report	True

Example

lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr

Action successfully completed please review created file diffResult.txt

!more diffResult.txt
Reference Database: JDOE @ jdbc:oracle:thin:@localhost:1521/ORCLPDB1 (Default Schema: JDOE)
Comparison Database: HR @ jdbc:oracle:thin:@localhost:1521/ORCLPDB1 (Default Schema: HR)
Compared Schemas: JDOE -> HR
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s): 
     JDOE.ZIPCODES.AREA_CODE
     JDOE.DATABASECHANGELOG.AUTHOR
     JDOE.DATABASECHANGELOG_ACTIONS.AUTHOR
     JDOE.DATABASECHANGELOG_DETAILS.AUTHOR
     JDOE.ZIPCODES.CITY
     JDOE.ZIPCODES.CODE
     JDOE.DATABASECHANGELOG.COMMENTS
     JDOE.DATABASECHANGELOG_DETAILS.COMMENTS
     JDOE.DATABASECHANGELOG.CONTEXTS
     JDOE.DATABASECHANGELOG_DETAILS.CONTEXTS
     JDOE.ZIPCODES.COUNTY
     JDOE.DATABASECHANGELOG.DATEEXECUTED
     JDOE.DATABASECHANGELOG_DETAILS.DATEEXECUTED
     JDOE.DATABASECHANGELOG.DEPLOYMENT_ID
     JDOE.DATABASECHANGELOG_ACTIONS.DEPLOYMENT_ID
     JDOE.DATABASECHANGELOG_DETAILS.DEPLOYMENT_ID
     JDOE.DATABASECHANGELOG.DESCRIPTION
     JDOE.DATABASECHANGELOG_DETAILS.DESCRIPTION
     JDOE.DATABASECHANGELOG.EXECTYPE
     JDOE.DATABASECHANGELOG_DETAILS.EXECTYPE
     JDOE.DATABASECHANGELOG.FILENAME
     JDOE.DATABASECHANGELOG_ACTIONS.FILENAME
     JDOE.DATABASECHANGELOG_DETAILS.FILENAME
     JDOE.DATABASECHANGELOG.ID
     JDOE.DATABASECHANGELOGLOCK.ID
     JDOE.DATABASECHANGELOG_ACTIONS.ID
     JDOE.DATABASECHANGELOG_DETAILS.ID
     JDOE.ZIPCODES.ID
     JDOE.DATABASECHANGELOG.LABELS
     JDOE.DATABASECHANGELOG_DETAILS.LABELS
     JDOE.ZIPCODES.LAT
     JDOE.DATABASECHANGELOG.LIQUIBASE
     JDOE.DATABASECHANGELOG_DETAILS.LIQUIBASE
     JDOE.DATABASECHANGELOGLOCK.LOCKED
     JDOE.DATABASECHANGELOGLOCK.LOCKEDBY
     JDOE.DATABASECHANGELOGLOCK.LOCKGRANTED
     JDOE.ZIPCODES.LON
     JDOE.DATABASECHANGELOG.MD5SUM
     JDOE.DATABASECHANGELOG_DETAILS.MD5SUM
     JDOE.DATABASECHANGELOG.ORDEREXECUTED
     JDOE.DATABASECHANGELOG_ACTIONS.SEQUENCE
     JDOE.DATABASECHANGELOG_ACTIONS.SQL
     JDOE.DATABASECHANGELOG_DETAILS.SQL
     JDOE.ZIPCODES.STATE
     JDOE.DATABASECHANGELOG_ACTIONS.SXML
     JDOE.DATABASECHANGELOG_DETAILS.SXML
     JDOE.DATABASECHANGELOG.TAG
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): 
     PK_DATABASECHANGELOGLOCK UNIQUE  ON JDOE.DATABASECHANGELOGLOCK(ID)
     SYS_C00122620 UNIQUE  ON JDOE.DATABASECHANGELOG_ACTIONS(ID, AUTHOR, FILENAME, SEQUENCE)
     SYS_C00122637 UNIQUE  ON JDOE.ZIPCODES(ID)
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): 
     PK_DATABASECHANGELOGLOCK on JDOE.DATABASECHANGELOGLOCK(ID)
     SYS_C00122620 on JDOE.DATABASECHANGELOG_ACTIONS(ID, AUTHOR, FILENAME, SEQUENCE)
     SYS_C00122637 on JDOE.ZIPCODES(ID)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): 
     DATABASECHANGELOG
     DATABASECHANGELOGLOCK
     DATABASECHANGELOG_ACTIONS
     ZIPCODES
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): 
     DATABASECHANGELOG_DETAILS
Unexpected View(s): NONE
Changed View(s): NONE


SQL> lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr -noreport

Action successfully completed please review created file diffResult.xml

diffResult.xml
!more diffResult.xml
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd 
http://www.liquibase.org/xml/ns/dbchangelog 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
    <changeSet author="jdoe (generated)" id="1591203948991-1">
        <createTable tableName="DATABASECHANGELOG_ACTIONS">
            <column name="ID" type="VARCHAR2(255 BYTE)">
                <constraints nullable="false"/>
            </column>
            <column name="SEQUENCE" type="NUMBER">
                <constraints nullable="false"/>
            </column>
            <column name="SQL" type="CLOB"/>
            <column name="SXML" type="CLOB"/>
            <column name="AUTHOR" type="VARCHAR2(255 BYTE)">
                <constraints nullable="false"/>
            </column>
            <column name="FILENAME" type="VARCHAR2(255 BYTE)">
                <constraints nullable="false"/>
            </column>
            <column name="DEPLOYMENT_ID" type="VARCHAR2(10 BYTE)"/>
        </createTable>
    </changeSet>
    <changeSet author="jdoe (generated)" id="1591203948991-2">
        <createView fullDefinition="true" viewName="DATABASECHANGELOG_DETAILS">
CREATE OR REPLACE FORCE VIEW DATABASECHANGELOG_DETAILS (DEPLOYMENT_ID, ID, AUTHOR, 
FILENAME, SQL, SXML, DATEEXECUTED, EXECTYPE, MD5SUM, DESCRIPTION, COMMENTS, LIQUIBASE, 
CONTEXTS, LABELS) AS SELECT  da.deployment_id,    da.id,    da.author,    
da.filename,  da.sql,     da.sxml,    d.dateexecuted,    d.exectype,    d.md5sum,  
d.description,    d.comments,   d.liquibase,    d.contexts,    d.labels 
FROM databasechangelog d  LEFT JOIN databasechangelog_actions da ON d.id = da.id 
AND d.author = da.author AND 
d.filename = da.filename ORDER BY   1,7</createView>
    </changeSet>
    <changeSet author="jdoe (generated)" id="1591203948991-3">
        <createTable tableName="ZIPCODES">
            <column name="ID" type="NUMBER">
                <constraints nullable="false" primaryKey="true" primaryKeyName="SYS_C00122637"/>
            </column>
            <column name="CODE" type="VARCHAR2(6 BYTE)"/>
            <column name="CITY" type="VARCHAR2(50 BYTE)"/>
            <column name="STATE" type="VARCHAR2(50 BYTE)"/>
            <column name="COUNTY" type="VARCHAR2(50 BYTE)"/>
            <column name="AREA_CODE" type="NUMBER(3, 0)"/>
            <column name="LAT" type="NUMBER"/>
            <column name="LON" type="NUMBER"/>
        </createTable>
    </changeSet>
    <changeSet author="jdoe (generated)" id="1591203948991-4">
        <createIndex indexName="SYS_C00122620" tableName="DATABASECHANGELOG_ACTIONS" unique="true">
            <column name="ID"/>
            <column name="AUTHOR"/>
            <column name="FILENAME"/>
            <column name="SEQUENCE"/>
        </createIndex>
    </changeSet>
    <changeSet author="skutz (generated)" id="1591203948991-5">
        <addPrimaryKey columnNames="ID, AUTHOR, FILENAME, SEQUENCE" constraintName="SYS_C00122620" 
forIndexName="SYS_C00122620" tableName="DATABASECHANGELOG_ACTIONS"/>
    </changeSet>
</databaseChangeLog>

3.4.9 LB DBDOC

Generates Javadoc-like documentation based on the current database and change log.

Syntax

liquibase(lb) dbdoc -changelog CHANGELOG -dir DIR [-context CONTEXT] [-label LABEL]

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use
-dir DIR	Saves all files in the given directory, example, -dir some/sub/directory

Common Update Parameters

Parameter	Description
-context CONTEXT	Specify the changeset contexts to execute
-label LABEL	Filter the changelog using labels

Example

SQL> lb dbdoc -changelog states_table.xml -dir ./doc
SQL> !ls -l ./doc
total 56
drwxr-xr-x   3 jdoe  staff    96 Jun  3 13:25 authors
-rw-r--r--   1 jdoe  staff   449 Jun  3 13:25 authors.html
drwxr-xr-x   3 jdoe  staff    96 Jun  3 13:25 changelogs
-rw-r--r--   1 jdoe  staff   474 Jun  3 13:25 changelogs.html
drwxr-xr-x  31 jdoe  staff   992 Jun  3 13:25 columns
-rw-r--r--   1 jdoe  staff   555 Jun  3 13:25 currenttables.html
-rw-r--r--   1 jdoe  staff   899 Jun  3 13:25 globalnav.html
-rw-r--r--   1 jdoe  staff   769 Jun  3 13:25 index.html
-rw-r--r--   1 jdoe  staff   445 Jun  3 13:25 overview-summary.html
drwxr-xr-x   4 jdoe  staff   128 Jun  3 13:25 pending
drwxr-xr-x   3 jdoe  staff    96 Jun  3 13:25 recent
-rw-r--r--   1 jdoe  staff  1202 Jun  3 13:25 stylesheet.css
drwxr-xr-x   4 jdoe  staff   128 Jun  3 13:25 tables

Figure 3-1 shows the index page that is created by the DBDOC command. It enables you to navigate the various details of the changelog used to produce the document.

Figure 3-1 Index Page

Description of "Figure 3-1 Index Page"

3.4.10 LB CHANGELOGSYNC

Writes the SQL statements to mark all changes in the change log as executed in the database to a file.

Syntax

liquibase(lb) changelogsync -changelog CHANGELOG

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

SQL> lb changelogsync -changelog zipcodes_table.xml
-- *********************************************************************
-- SQL to add all changesets to database history table
-- *********************************************************************
-- Change Log: zipcodes_table.xml
-- Ran at: 6/3/20 1:28 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************

-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)', 
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 13:28:24.686', 'YYYY-MM-DD HH24:MI:SS.FF') 
WHERE ID = 1 AND LOCKED = 0;

INSERT INTO JDOE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, 
MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) 
VALUES ('e6568d24910039bf3d4241e7edff5ec82612c60f', 'Generated', 'zipcodes_table.xml', 
SYSTIMESTAMP, 3, '8:e4fe731b3a40f479dec11e234e4a69ac', 'createSxmlObject objectName=ZIPCODES, 
ownerName=JDOE', '', 'EXECUTED', NULL, NULL, '3.8.9', '1205304697');

-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

3.4.11 LB CLEARCHECKSUMS

Removes current checksums from the database. For the next update, changesets that have already been deployed will have their checksums recomputed, and changesets that have not been deployed will be deployed.

Syntax

liquibase(lb) clearchecksums -changelog CHANGELOG

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

SQL> lb clearchecksums -changelog zipcodes_table.xml

3.4.12 LB LISTLOCKS

Lists who currently has locks on the database change log.

Syntax

liquibase(lb) listlocks -changelog CHANGELOG

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

SQL> lb listlocks -changelog zipcodes_table.xml
No locks currently in use

3.4.13 LB RELEASELOCKS

Releases all locks on the database change log.

Syntax

liquibase(lb) releaselocks -changelog CHANGELOG

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

SQL> lb releaselocks  -changelog zipcodes_table.xml

3.4.14 LB STATUS

Outputs a list of change sets that have not been applied.

Syntax

liquibase(lb) status -changelog CHANGELOG

Checks the status of the change log using the current connection. This shows if the change log has been applied and the result of the change log.

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

SQL> lb status -changelog zipcodes_table.xml

1 change sets have not been applied to JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1     
zipcodes_table.xml::e6568d24910039bf3d4241e7edff5ec82612c60f::Generated

3.4.15 LB VALIDATE

Checks the changelog for errors.

Syntax

liquibase(lb) validate -changelog CHANGELOG

Named Parameters

Parameter	Description
-changelog CHANGELOG	Specify the changelog file to use

Example

lb validate -changelog zipcodes_table.xml
No issues were found in file zipcodes _table.xml, validation passed

3.4.16 LB VERSION

Display product version information.

Syntax

liquibase(lb) version

Example

SQL> lb version

Liquibase version:   3.8.9
Extension Version:   2.0.0.0.0

3.4.17 LB HELP

Lists the available Liquibase commands.

Syntax

liquibase(lb) help command

Type a command with no options for help on a specific command.

Example

SQL> lb help
usage: lb COMMAND ...

Commands:
The following commands are available within the liquibase feature.
lb help COMMAND for command specific help

COMMAND
genobject            Generate change log for a specific database object
genschema            Generate changelogs and controller for connected schema
gencontrolfile       Remove locks held by changelog
update               Updates database to current version
updatesql            Generates SQL to update database to current version
rollback             Rolls back the state requested
rollbacksql          Writes SQL to roll back the database to the state requested
diff                 Writes description of differences between two databases to standard out.
dbdoc                Generates Javadoc-like documentation based on current database and change log.
changelogsync        Mark all changes as executed in the database.
clearchecksums       Removes current checksums from  database.
listlocks            Lists who currently has locks on the database changelog.
releaselocks         Releases all locks on the database changelog.
status               Outputs list of unrun change sets.
validate             Checks the changelog for errors.
version              Display product version information

3.5 DATABASECHANGELOG_DETAILS VIEW

DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables for easier reporting and troubleshooting.

This view enables a better understanding of the status and work performed by each change applied to the database. The SQL column shows the actual SQL that was run in the database. The SXML column shows the state of the object prior to the change being applied.

Example

SQL> desc DATABASECHANGELOG_DETAILS


  Name            Null?         Type 
________________ ___________ ________________ 
DEPLOYMENT_ID                VARCHAR2(10)     
ID                           VARCHAR2(255)    
AUTHOR                       VARCHAR2(255)    
FILENAME                     VARCHAR2(255)    
SQL                          CLOB             
SXML                         CLOB             
DATEEXECUTED     NOT NULL    TIMESTAMP(6)     
EXECTYPE         NOT NULL    VARCHAR2(10)     
MD5SUM                       VARCHAR2(35)     
DESCRIPTION                  VARCHAR2(255)    
COMMENTS                     VARCHAR2(255)    
LIQUIBASE                    VARCHAR2(20)     
CONTEXTS                     VARCHAR2(255)    
LABELS                       VARCHAR2(255)    

3.6 ChangeSets in Liquibase

The following table lists the changeSets and provides a description for each of them. To learn more about changeSets, see <changeSet> tag.

ChangeSet	Description
CreateOracleConstraint	Creates a constraint from SQL.
CreateOracleDirectory	Creates a directory from SQL.
CreateOracleFunction	Creates a function from SQL.
CreateOracleGrant	Creates a grant from SQL.
CreateOraclePackageBody	Creates a package body from SQL.
CreateOracleJob	Creates a DBMS_Scheduler job from SQL.
CreateOraclePackageSpec	Creates a package specification from SQL.
CreateOracleProcedure	Creates a procedure from SQL.
CreateOraclePublicSynonym	Creates a public synonym from SQL.
CreateOracleRefConstraint	Creates a referential constraint from SQL.
CreateOracleSynonym	Creates a synonym from SQL.
CreateOracleTrigger	Creates a trigger from SQL.
CreateOracleTypeBody	Creates a type body from SQL.
CreateOracleTypeSpec	Creates a type spec from SQL.
CreateSxmlObject	Creates a function from SQL.
DropOracleConstraint	Drops a constraint.
DropOracleFunction	Drops a function.
DropOracleGrant	Drops a grant.
DropOraclePackageBody	Drops a package body.
DropOraclePackageSpec	Drops a package specification.
DropOracleProcedure	Drops a procedure.
DropOracleRefConstraint	Drops a referential constraint.
DropOracleTrigger	Drops a trigger.
DropOracleTypeBody	Drops a type body.
DropOracleTypeSpec	Drops a type specification.
DropOracleSynonym	Drops a synonym.
DropSxmlObject	Drops an SXML object. If the object was created through createSxmlObject, this rolls back the object to the last state. If not created, the object is just dropped. This is primarily used internally for SXML object handling.
RunOracleScript	Executes a SQL script using the SQLcl engine and therefore supports all SQLcl commands. Supports script, file, and URL sources. See Execute Custom SQL with RunOracleScript

3.7 Examples Using Liquibase

Some examples of using the Liquibase functionality for database change management tasks:

• Capture and Deploy an Object
• Capture and Deploy a Schema
• Generate the Master Control File
• Capture and Deploy a Schema and then Upgrade it and Redeploy
• Execute Custom SQL with RunOracleScript

3.7.1 Capture and Deploy an Object

To deploy the EMPLOYEES table from HR to HR2:

Note:

The syntax for the GENOBJECT command has changed. See LB GENOBJECT

1. Connect to HR.

   SQL> connect hr/hr
   Connected.

2. Capture the object.

   SQL> lb genobject -type table -name employees
   Action successfully completed please review created file employees_table1.xml

3. Connect to the other user.

   SQL> connect hr2/hr2
   Connected.

4. Ensure the object does not already exist.

   SQL> drop table employees
   Table EMPLOYEES dropped.

5. Create the object in the current schema.

   Note:

   As the schema name has changed, you must set include schema to false or it will try and create the object in the hr schema.

   SQL> lb update -changelog employees_table.xml
   ScriptRunner Executed:/Users/jdoe/Documents/lbtest/v2/employees_table.xml::6486f968-93fe-4e1c-ac59-17ef392e1423::Generated
   

6. Verify the object was created.

   SQL> desc employees  
   ---- ----- ------ 
   EMPLOYEE_ID       NOT NULL    NUMBER(6)       
   FIRST_NAME                    VARCHAR2(20)    
   LAST_NAME         NOT NULL    VARCHAR2(25)    
   EMAIL             NOT NULL    VARCHAR2(25)    
   PHONE_NUMBER                  VARCHAR2(20)    
   HIRE_DATE         NOT NULL    DATE            
   JOB_ID            NOT NULL    VARCHAR2(10)    
   SALARY                        NUMBER(8,2)     
   COMMISSION_PCT                NUMBER(2,2)     
   MANAGER_ID                    NUMBER(6)       
   DEPARTMENT_ID                 NUMBER(4)       
   

3.7.2 Capture and Deploy a Schema

To capture the HR schema and reproduce it in the HR2 schema:

sql.exe hr/hr@pdb1

SQL> lb genschema
[Type - TYPE_SPEC]:                          107 ms
[Type - TYPE_BODY]:                           80 ms
[Type - SEQUENCE]:                            26 ms
[Type - CLUSTER]:                             25 ms
[Type - TABLE]:                              721 ms
[Type - MATERIALIZED_VIEW_LOG]:               18 ms
[Type - MATERIALIZED_VIEW]:                    9 ms
[Type - VIEW]:                                81 ms
[Type - REF_CONSTRAINT]:                     128 ms
[Type - DIMENSION]:                           14 ms
[Type - FUNCTION]:                            43 ms
[Type - PROCEDURE]:                           47 ms
[Type - PACKAGE_SPEC]:                        24 ms
[Type - DB_LINK]:                             14 ms
[Type - SYNONYM]:                             23 ms
[Type - INDEX]:                              102 ms
[Type - TRIGGER]:                             25 ms
[Type - PACKAGE_BODY]:                        33 ms
[Type - JOB]:                                 14 ms
[Method loadCaptureTable]:                  1534 ms
[Method parseCaptureTableRecords]:          5479 ms
[Method sortCaptureTable]:                    37 ms
[Method createExportChangeLogs]:               0 ms

Export Flags Used:

Export Grants	      false
Export Synonyms	    false
SQL> 

setup the hr2 user --

drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;

SQL> drop user hr2 cascade;
User HR2 dropped.

SQL> create user hr2 identified by hr2;
User HR2 created.

SQL> grant connect,resource, create view to hr2;
Grant succeeded.

SQL> alter user hr2 quota unlimited on users;
User HR2 altered.

SQL> alter user hr2 quota unlimited on sysaux;
User HR2 altered.

SQL> connect hr2/hr2
Connected.

SQL> lb update -changelog controller.xml
ScriptRunner Executed:person_typ_type_spec.xml::32492b89db4b0e2a115caa3e7e6c3d29c11fa1db::Generated
ScriptRunner Executed:emp_person_typ_type_spec.xml::cb314e652bc79126fa0a2b5261720d15728c7e04::Generated
ScriptRunner Executed:people_typ_type_spec.xml::cc5c2298290e3ce2dc1563da82ddd9ea33a768c8::Generated

SQL> tables

TABLES
___________________________
DATABASECHANGELOG
DATABASECHANGELOGLOCK
DATABASECHANGELOG_ACTIONS
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY

10 rows selected.

3.7.3 Generate the Master Control File

SQL> lb gencontrolfile
Action successfully completed please review created file controller.xml

SQL> !more controller.xml
<?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-3.1.xsd">
<include file="{filename.xml}"/>
</databaseChangeLog>

3.7.4 Capture and Deploy a Schema and then Upgrade it and Redeploy

The steps are:

1. Migrate the HR schema to the HR2 schema, then modify the HR schema and update HR2.

   setup the hr2 user --
   drop user hr2 cascade;
   create user hr2 identified by hr2;
   grant connect,resource, create view to hr2;
   alter user hr2 quota unlimited on users;
   alter user hr2 quota unlimited on sysaux;
   
   SQL> drop user hr2 cascade;
   User HR2 dropped.
   
   SQL> create user hr2 identified by hr2;
   User HR2 created.
   
   SQL> grant connect,resource, create view to hr2;
   Grant succeeded.
   
   SQL> alter user hr2 quota unlimited on users;
   User HR2 altered.
   
   SQL> alter user hr2 quota unlimited on sysaux;
   User HR2 altered.
   
   SQL> connect hr2/hr2
   Connected.
   
   SQL> !mkdir v1 
   
   SQL> cd v1
   SQL> connect hr/hr
   Connected.
   SQL> lb genschema
   [Type - TYPE_SPEC]:                          185 ms
   [Type - TYPE_BODY]:                           84 ms
   [Type - SEQUENCE]:                            41 ms
   [Type - CLUSTER]:                             38 ms
   [Type - TABLE]:                              702 ms
   [Type - MATERIALIZED_VIEW_LOG]:               20 ms
   [Type - MATERIALIZED_VIEW]:                   10 ms
   [Type - VIEW]:                                85 ms
   [Type - REF_CONSTRAINT]:                     142 ms
   [Type - DIMENSION]:                           21 ms
   [Type - FUNCTION]:                            45 ms
   [Type - PROCEDURE]:                           52 ms
   [Type - PACKAGE_SPEC]:                        30 ms
   [Type - DB_LINK]:                             18 ms
   [Type - SYNONYM]:                             30 ms
   [Type - INDEX]:                              125 ms
   [Type - TRIGGER]:                             31 ms
   [Type - PACKAGE_BODY]:                        39 ms
   [Type - JOB]:                                 19 ms
   [Method loadCaptureTable]:                  1717 ms
   [Method parseCaptureTableRecords]:          6243 ms
   [Method sortCaptureTable]:                    42 ms
   [Method createExportChangeLogs]:               1 ms
   
   Export Flags Used:
   Export Grants          false
   Export Synonyms        false
   

2. Modify the HR schema.

   SQL> ALTER TABLE DEPARTMENTS ADD (COLUMN1 VARCHAR2(20) );
   Table DEPARTMENTS altered.
   
   SQL> ALTER TABLE COUNTRIES ADD (COLUMN1 VARCHAR2(20) );
   Table COUNTRIES altered.
   
   SQL> ALTER TABLE LOCATIONS ADD (COLUMN1 VARCHAR2(20) );
   Table LOCATIONS altered.
   
   SQL> CREATE OR REPLACE PROCEDURE PROCEDURE2 (PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2,PARAM3 IN VARCHAR2, PARAM4 IN VARCHAR
   2) AS BEGIN NULL; END PROCEDURE2;
   Procedure PROCEDURE2 compiled

3. Make a change to the v2 directory.

   SQL> cd .. 
   SQL> !mkdir v2 
   SQL> cd v2 
   

4. Export v2 of the HR schema.

   SQL> lb genschema
   [Type - TYPE_SPEC]:                          102 ms
   [Type - TYPE_BODY]:                           74 ms
   [Type - SEQUENCE]:                            26 ms
   [Type - CLUSTER]:                             25 ms
   [Type - TABLE]:                              534 ms
   [Type - MATERIALIZED_VIEW_LOG]:               13 ms
   [Type - MATERIALIZED_VIEW]:                   10 ms
   [Type - VIEW]:                                78 ms
   [Type - REF_CONSTRAINT]:                     119 ms
   [Type - DIMENSION]:                           15 ms
   [Type - FUNCTION]:                            44 ms
   [Type - PROCEDURE]:                           46 ms
   [Type - PACKAGE_SPEC]:                        25 ms
   [Type - DB_LINK]:                             14 ms
   [Type - SYNONYM]:                             24 ms
   [Type - INDEX]:                              100 ms
   [Type - TRIGGER]:                             25 ms
   [Type - PACKAGE_BODY]:                        34 ms
   [Type - JOB]:                                 14 ms
   [Method loadCaptureTable]:                  1322 ms
   [Method parseCaptureTableRecords]:          5452 ms
   [Method sortCaptureTable]:                    37 ms
   [Method createExportChangeLogs]:               0 ms
   Export Flags Used:
   Export Grants      false
   Export Synonyms    false
   

   To import v1, change back to the v1 directory and connect as HR2.

   SQL> cd ..
   SQL> connect hr2/hr2 
   Connected. 
   

5. Now import the capture into the HR2 schema.

   SQL> lb update -changelog controller.xml
   ScriptRunner Executed:person_typ_type_spec.xml::f9d8cb37f8030674b1d0060b054254a28f96dd51::Generated
   ScriptRunner Executed:emp_person_typ_type_spec.xml::cabf96af49def8fc164a6c0aa7be69c1a78d46c4::Generated
   ScriptRunner Executed:people_typ_type_spec.xml::6a98e2a2ec56a7ca6b716a114804081f8760ec78::Generated
   ScriptRunner Executed:dept_persons_typ_type_spec.xml::e250a6757e4e023a30c1b9f458149e4dfa0ad6bc::Generated
   ScriptRunner Executed:employee_type_type_spec.xml::aa2ef7c172e6df08e4082a4f139f4c6edf6e14bc::Generated
   ScriptRunner Executed:person_typ_type_body.xml::bd29fd1d0827b70ae4b644781eeb9a69f7caf956::Generated
   ScriptRunner Executed:departments_seq_sequence.xml::ea03838db9dc0613a5128090c9388ff1b8dc8a1a::Generated
   ScriptRunner Executed:locations_seq_sequence.xml::2aba6107a95a977632161be9ca920bdb836f18fc::Generated
   ScriptRunner Executed:employees_seq_sequence.xml::2379ab3cbd7f84f52f6596fa16479bb6ccf721ca::Generated
   ScriptRunner Executed:countries_table.xml::8e87e94fd74b172dc76d950216e057827e2e3f83::Generated
   ScriptRunner Executed:countries_comments.xml::8797a740e38cf3ef7da36b6ba4aa5497cfd6b457::Generated
   

6. Check a table to verify.

   SQL> show user
   USER is "HR2"
   
   SQL> desc departments
   
   Name                  Null?          Type
   __________________ ___________ _______________
   DEPARTMENT_ID        NOT NULL       NUMBER(4)
   DEPARTMENT_NAME      NOT NULL       VARCHAR2(30)
   MANAGER_ID                          NUMBER(6)
   LOCATION_ID                         NUMBER(4)

7. Switch to v2 and import the capture.

   SQL> cd v2
   SQL> lb update -changelog controller.xml
   ScriptRunner Executed:countries_table.xml::31a15fa402822ae67ae588a74e2b0d9506f1dc26::Generated
   ScriptRunner Executed:locations_table.xml::9548f26f7f8b53bc3639869e23b118b4fe09363b::Generated
   ScriptRunner Executed:departments_table.xml::560727e73ed902cd0ad3e25793987d323580b823::Generated
   ScriptRunner Executed:emp_email_uk_index.xml::99a0278c50a75912985023c6a075514ec939d233::Generated
   ScriptRunner Executed:secure_employees_trigger.xml::cfe2f3f3443973ee9d43f4cc77701d211c6023ba::Generated
   ScriptRunner Executed:update_job_history_trigger.xml::aa8e35e3de04b9bb0ef3c47885f175e20be6c59e::Generated
   

8. Verify that you are on v2.

   SQL> desc departments
   
   Name                  Null?         Type
   __________________ ___________ _______________
   DEPARTMENT_ID        NOT NULL     NUMBER(4)
   DEPARTMENT_NAME      NOT NULL     VARCHAR2(30)
   MANAGER_ID                        NUMBER(6)
   LOCATION_ID                       NUMBER(4)
   COLUMN1                           NUMBER          

3.7.5 Execute Custom SQL with RunOracleScript

Create a RunOracleScript changeset where the source can be a script, a link to a file, or a URL. The changeset in this example shows how to create a table and use PL/SQL variables in the script.

#### SCRIPT - STRING EXAMPLE ####

<?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" 
  xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
  <changeSet id="runScriptString" author="jdoe">
    <n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="STRING">
       <n0:source><![CDATA[DEFINE table_name = RUNNERSTRING;create table &&table_name (id number);]]></n0:source>
    </n0:runOracleScript>
  </changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------
 
SQL> tables
no rows selected
 
SQL> lb update -changelog stringscript.xml
ScriptRunner Executed:stringscript.xml::runScriptString::jdoe
 
SQL> tables
TABLES 
____________________________ 
DATABASECHANGELOGLOCK        
DATABASECHANGELOG            
DATABASECHANGELOG_ACTIONS    
RUNNERSTRING                          

#### SCRIPT - FILE EXAMPLE ####

filescript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
<?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" 
  xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
  <changeSet id="RunScriptFile" author="jdoe">
     <n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="FILE">
         <n0:source><![CDATA[/Volumes/SSD/work/sqlcl/scriptFILE.sql]]></n0:source>
     </n0:runOracleScript>
  </changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------

scriptFILE.sql
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE RUNNERFILE (id number)
-------------------------------------------------------------------------------------------------------------------------------------------

SQL> tables
TABLES 
____________________________ 
DATABASECHANGELOGLOCK        
DATABASECHANGELOG            
DATABASECHANGELOG_ACTIONS    
RUNNERSTRING                 

SQL> lb update -changelog filescript.xml
ScriptRunner Executed:/Volumes/SSD/work/sqlcl/filescript.xml::RunScriptFile::jdoe

SQL> tables
TABLES 
____________________________ 
DATABASECHANGELOGLOCK        
DATABASECHANGELOG            
DATABASECHANGELOG_ACTIONS    
RUNNERSTRING                 
RUNNERFILE     

#### SCRIPT - URL EXAMPLE ####

urlscript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
<?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" 
  xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
  <changeSet id="RunScriptUrl" author="jdoe">
     <n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="URL">
         <n0:source><![CDATA[file:///Volumes/SSD/work/sqlcl/scriptURL.sql]]></n0:source>
     </n0:runOracleScript>
  </changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------

scriptURL.sql
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE RUNNERURL (id number)
-------------------------------------------------------------------------------------------------------------------------------------------

SQL> tables
TABLES 
____________________________ 
DATABASECHANGELOGLOCK        
DATABASECHANGELOG            
DATABASECHANGELOG_ACTIONS    
RUNNERSTRING                 
RUNNERFILE                   

SQL> lb update -changelog urlscript.xml
ScriptRunner Executed:/Volumes/SSD/work/sqlcl/urlscript.xml::RunScriptUrl::jdoe

SQL> tables
TABLES 
____________________________ 
DATABASECHANGELOGLOCK        
DATABASECHANGELOG            
DATABASECHANGELOG_ACTIONS    
RUNNERSTRING                 
RUNNERFILE                   
RUNNERURL