3 Using Liquibase

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
• Using SQLcl Liquibase Functionality with Open-Source Liquibase
• Dynamic Object Transformation with SQLcl Liquibase and DBMS_METADATA Oracle Database Package
• Liquibase Open-Source Changesets with SQLcl Liquibase
• 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 with the Liquibase plug-in available.

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 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:

  • the extension installed in your Liquibase environment. Add the following jar files from sqlcl/lib and sqlcl/lib/ext folders to the liquibase/lib folder:
    • dbtools-liquibase.jar
    • dbtools-apex.jar
    • guava-with-lf.jar
    • xmlparserv2_sans_jaxp_services.jar
    • dbtools-common.jar
  • the privileges to create a table and a package.
  • to update your properties file by adding the following line:

    change-exec-listener-class: liquibase.changelog.visitor.OracleActionChangeListener

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.

The SQLcl Liquibase commands that were initially launched in SQLcl release 19.2 has deprecated. In this release, the SQLcl plug-in has been updated to synchronize with the new command format introduced by Liquibase.

Syntax

Liquibase|lb COMMAND {OPTIONS}
Liquibase|lb  help|he [-example|-ex]
Liquibase|lb  help|he COMMAND [-syntax|-sy] [-example|-ex]

To see the help for Liquibase in SQLcl, type one of the following:

help Liquibase or lb

Liquibase or lb help

To quickly view the syntax and exclude other details, type:

lb help -syntax

To only view the examples in help, type:

lb help -example

<OPTIONS>: The list of options for each Liquibase command is provided in the following sections.

Database Diff Commands

New Command	Description	Deprecated SQLcl Command
diff	Writes description of differences between two databases to standard out.	diff
diff-changeLog	Adds any differences between the databases specified to a changelog. Can append in any of the supported changelog formats.	-

Database Rollback Commands

New Command	Description	Deprecated SQcl Command
rollback	Rolls back the database to the state it was in when the tag was applied.	rollback { -tag }
rollback-sql	A helper command that produces the raw SQL Liquibase would run when using the rollbackByTag command.	rollbacksql { -tag }
rollback-to-date	Rolls back the database to the state it was in at the given date/time.	rolback { -date }
rollback-to-date-sql	A helper command that allows you to inspect the SQL Liquibase will run while using the rollback-to-date command.	rollbacksql { -date }
rollback-count	Rolls back the last <value> changesets.	rollback { -count }
rollback-count-sql	Writes SQL to roll back the last <value> changesets to STDOUT.	rollbacksql { -count }
future-rollback-sql	Writes SQL to roll back the database to the current state after the changes in the changelog have been applied.	-
future-rollback-count-sql	Generates the SQL that Liquibase would use to sequentially revert the number of changes associated with undeployed changesets, which are added to a changelog file.	-
future-rollback-from-tag-sql	Produces the raw SQL Liquibase would need to roll back all undeployed changes made up to the specified tag.	-

Database Snapshot Commands

New Command	Description	Deprecated SQLcl Command
data	Generate changelogs for the data.	-
generate-apex-object	Generate apex objects from a database	genobject { -apex }
generate-control-file	Generate an empty control file that you can use to start a new changelog.	-
generate-object	Generate database objects from a database	genobject
generate-ords-module, generate-ords-schema	Generate ords objects from a database	genobject { -ords }
generate-schema	Generate all supported objects in a schema and controller file.	genschema
generate-changelog	Generate a changelog from a database when adding Liquibase to a new project. This is synonymous with snapshot with the exception of saving the output as XML in the changelog.	-
snapshot	Gathers the current database schema and displays that information to STDOUT. With options, can save the schema in JSON format, and that JSON snapshot can serve as a comparison database.	-
update-to-tag-sql	A helper command that inspects the SQL code Liquibase will run when using the update-to-tag <tag> command.	-
snapshot-reference	Captures the current state of the referenceURL database, which is the source database.	-

Database Update Commands

New Command	Description	Deprecated SQLcl Command
update	Updates the database to the current version.	update
update-sql	A helper command that enables you to inspect the SQL code that Liquibase will run when using the update command.	updateSql
update-count	Applies the next <value> changesets.	-
update-count-sql	Writes SQL to apply the next <value> changesets to STDOUT.	-
update-testing-rollback	Updates the database, then rolls back changes before updating again.	-
update-to-tag	Applies sequential changes to your database from the newest changeset to the changeset with the tag you specified and applied earlier.	-
update-to-tag-sql	A helper command that inspects the SQL code Liquibase will run when using the update-to-tag <tag> command.	-

Documentation Commands

New Command	Description	Deprecated SQLcl Command
db-doc	Generates Javadoc-like documentation based on current database and changelog.	dbdoc

Maintenance Commands

New Command	Description	Deprecated SQLcl Command
calculate-checksum	Calculates and prints a checksum for the changeset with the specified id in the following format: filepath::id::author.	-
changelog-sync	Marks all changes as executed in the database.	-
changelog-sync-sql	Writes SQL to mark all changes as executed in the database to STDOUT.	changelogsync
changelog-sync-to-tag	Marks all undeployed changesets from your changelog up to and including the specified tag as executed in your database.	-
changelog-sync-to-tag-sql	Produces the raw SQL that Liquibase would run when using the changelog-sync-to-tag command to mark all undeployed changesets associated with the specified tag as executed in your database.	-
clear-checksums	Removes current checksums from database. On next update changesets that have already been deployed will have their checksums recomputed, and changesets that have not been deployed will be deployed.	clearchecksums
drop-all	Drops all database objects owned by the user.	-
list-locks	Lists who currently has locks on the database changelog.	listlocks
mark-next-changeset-ran	Mark the next changeset as executed in the database.	-
mark-next-changeset-ran-sql	Inspects the SQL Liquibase will run while using the mark-next-changeset-ran command.	-
release-locks	Releases all locks on the database changelog.	releaselocks
tag	"Tags" the current database state for future rollback.	tag
tag-exists	Checks whether the given tag already exists.	tagexists
unexpected-changesets	Produces a list of changesets that were run in the database but do not exist in the current changelog.
validate	Checks the changelog for errors.	validate

Status Commands

New Command	Description	Deprecated SQLcl Command
history	Lists all deployed changesets and their deploymentIds.	-
status	Outputs the count (or list, if --verbose) of changesets that have not been deployed.	-

3.4.1 calculate-checksum

Calculates and prints a checksum for the changeset with the given ID in the format filepath::id::author.

Syntax

Liquibase|lb calculate-checksum|cac OPTIONS

The calculate-checksum command is typically used to compute an MD5 checksum, which serves as a unique identifier for the changeset. As a result, you can see whether the changeset has been changed and whether it has to be deployed differently. The MD5SUM column in the DATABASECHANGELOG table contains a checksum of the changeset and any change made in the changeset will result in a different checksum.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-changeset-identifier | -chi	Changeset ID identifier of form filepath::id::author.	-	--changesetIdentifier
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
--log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Calculate a checksum for changeset:

SQL> lb calculate-checksum --changelog-file countries_table.xml --changeset-identifier
countries_table.xml::382e51cedfbfc7ba59568dd09dcd4e110b9fbeca::(USER)-Generated 
SQL> liquibase calculate-checksum changelog.oracle.sql::myID_123::Steve

3.4.2 changelog-sync

Marks all changes as executed in the database.

Syntax

Liquibase|lb changelog-sync|chs OPTIONS

Uses include:

• Creating a new baseline database.
• Excluding objects from a database.
• Marking a change as executed. The change was created manually.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Mark changes as executed in the database:

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target and execute command
SQL> connect <db-connect-string>
SQL> lb changelog-sync -changelog-file countries_table.xml

3.4.3 changelog-sync-sql

Output the raw SQL used by Liquibase when running changelogsync.

Syntax

Liquibase|lb changelog-sync-sql|chss

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to mark changes as executed in the database:

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target and execute command
SQL> connect <db-connect-string>
SQL> lb changelog-sync-sql -changelog-file countries_table.xml -outputfile countries_synch.sql

3.4.4 changelog-sync-to-tag

Marks all undeployed changesets as executed starting from the top of the changelog file and moving down up to and including the tag.

Syntax

Liquibase|lb changelog-sync-to-tag|chstt

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Mark changes as executed in database up to and including tag.

cd <lb-changes-directory>
-- Edit changelog file and add tagDatabase entries for versions.
-- Execute command using a tag specified in tagDatabase in the changelog file.
SQL> lb changelog-sync-to-tag -tag version1 -changelog-file controller.xml

3.4.5 changelog-sync-to-tag-sql

Output the raw SQL used by Liquibase when running changelogSyncToTag.

Syntax

Liquibase|lb changelog-sync-to-tag-sql|chstts OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'	-	-
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Mark changes as executed in database up to and including tag.

cd <lb-changes-directory>
-- Edit changelog file and add tagDatabase entries for versions.
-- Execute command using a tag specified in tagDatabase in the changelog file.
SQL> lb changelog-sync-to-tag-sql -tag version1 -changelog-file controller.xml -outputfile synch.sql

3.4.6 clear-checksums

Clears all checksums and nullifies the MD5SUM column of the DATABASECHANGELOG table so that they will be recomputed on the next database update.

Syntax

Liquibase|lb clear-checksums|clc OPTIONS

Options

Option	Description	Default	Deprecated
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Clear checksums:

-- Connect to target and execute command.
SQL> connect <db-connect-string> 
SQL> clear-checksums

3.4.7 data

Generate changelogs for the data. Creates a changelog for data from all objects or as filters are specified.

Syntax

Liquibase|lb data|da OPTIONS

Options

Option	Description	Default	Deprecated
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-exclude-objects | -exo	Objects to exclude from diff.	-	--excludeObjects
-include-objects | -ino	Objects to include in diff.	-	--includeObjects
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-schemas | -sc	Schemas to include in diff.	-	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.8 db-doc

Generates JavaDoc documentation for the existing database and changelogs.

Syntax

 Liquibase|lb db-doc|dbd OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-output-directory | -oud	The directory where the documentation is generated.	-	--outputDirectory
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate db documentation:

-- Set default output path
SQL> cd <output-files-path>
-- Generate doc
SQL> lb db-doc -output-directory ./dbdoc -changelog-file controller.xml

3.4.9 diff

Compare two databases.

Syntax

Liquibase|lb diff|di OPTIONS

Options

Option	Description	Default	Deprecated
Required
-reference-password | -rep	The reference database password.	-	--referencePassword
-reference-url | -reur	The JDBC reference database connection URL.	-	--referenceUrl
-reference-username | -reu	The reference database username.	-	--referenceUsername
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-diff-types | -dit	Types of objects to compare	catalogs, tables, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints, data, storedprocedures, sequences{catalogs| tables| views| columns| indexes| foreignkeys| primarykeys| uniqueconstraints| data| storedprocedures| sequences}	--diffTypes
-exclude-objects | -exo	Objects to exclude from diff	-	--excludeObjects
-include-objects | -ino	Objects to include in diff	-	--includeObjects
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-

Examples

Compare two databases and spool output.

-- Set default output path
SQL> cd <output-files-path>
SQL> spool diff.sql
-- Connect to target database
SQL> connect <db-connect-string> 
-- Compare to a reference database
SQL> lb diff -reference-url <reference-db-url> -reference-username <reference-db-user> -reference-password <reference-db-password>
SQL> spool off

Compare tables, indexes and views between two databases.

-- Set default output path
SQL> cd <output-files-path>
SQL> spool diff.sql
-- Connect to comparison database
SQL> connect <db-connect-string> 
-- Compare to a reference database
SQL> lb diff -diff-types tables,indexes,views -reference-url <db-url> 
-reference-username <db-user> -reference-password <db-password>

3.4.10 diff-changeLog

Compare two databases to produce changesets and write them to a changelog file.

Syntax

Liquibase|lb diff-changelog|dic OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-reference-password | -rep	The reference database password.	-	--referencePassword
-reference-url | -reur	The JDBC reference database connection URL.	-	--referenceUrl
-reference-username | -reu	The reference database username.	-	--referenceUsername
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-diff-types | -dit	Types of objects to compare	catalogs, tables, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints, data, storedprocedures, sequences{catalogs| tables| views| columns| indexes| foreignkeys| primarykeys| uniqueconstraints| data| storedprocedures| sequences}	--diffTypes
-exclude-objects | -exo	Objects to exclude from diff	-	--excludeObjects
-include-objects | -ino	Objects to include in diff	-	--includeObjects
-include-schema | -ins	If true, the schema is included in generated changeSets.	False	--includeSchema
-include-tablespace | -int	Include the tablespace attribute in the changelog	False	--includeTableSpace
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-reference-default-schema-name | -redsn	The reference default schema name to use for the database connection.	-	--referenceDefaultSchemaName
-schemas | -sc	Schemas to include in diff	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Examples

Create changelog to synchronize two databases.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target database
SQL> connect <db-connect-string> 
-- Compare to a reference database and create a changelog to synchronize them
SQL> lb diff-changelog  -changelog-file diff.xml -reference-url <db-url> -reference-username <db-user> -reference-password <db-password>

Create changelog to synchronize table excluding employees between two databases.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to comparison database
SQL> connect <db-connect-string> 
-- Compare tables to a reference database
SQL> lb diff-changelog -changelog-file diff.xml -diffTypes=tables -exclude-objects employees -reference-url <db-url> 
-reference-username <db-user> -reference-password <db-password>

3.4.11 drop-all

Drops all database objects owned by the user.

Syntax

Liquibase|lb drop-all|dra

dropAll will not drop functions, procedures, or packages for the community version of Liquibase.

Functions, procedures, packages, and synonyms can only be dropped for Liquibase Pro supported objects.

dropAll is typically used when there is a need to prepare an environment schema to be identical to another environment schema. dropAll is useful in the developer and test environments to remove unwanted objects to reset the database to empty.

The command makes it easier to standardize another schema, compared to manually deleting the objects, or dropping and recreating the desired schema. dropAll should not be used in a production environment to prevent removal of required objects.

3.4.12 future-rollback-count-sql

Generates SQL that is used to sequentially revert the specified number of undeployed changes.

Syntax

Liquibase|lb future-rollback-count-sql|furcs OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-count | -cu	The number of changes to roll back.	-	-
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true, they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back the specified number of undeployed changes.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Generate SQL to roll back 1 undeployed change
SQL> lb future-rollback-count-sql -count 1 -changelog-file controller.xml

3.4.13 future-rollback-from-tag-sql

Generates SQL to revert future undeployed changes up to the specified tag.

Syntax

 Liquibase|lb future-rollback-from-tag-sql|furfts OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'.	-	-
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true, they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back undeployed changes to tag.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Generate SQL to roll back 1 undeployed change
SQL> lb future-rollback-count-sql -count 1 -changelog-file controller.xml
-- Edit changelog file and add tagDatabase entries for versions
-- Generate SQL to roll undeployed changes back to version1
SQL> lb future-rollback-from-tag-sql -tag version1 -changelog-file controller.xml

3.4.14 future-rollback-sql

Generate the raw SQL needed to roll back future undeployed changes.

Syntax

Liquibase|lb future-rollback-sql|furs OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true, they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back undeployed changes.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Generate SQL to roll back 1 undeployed change
SQL> lb future-rollback-count-sql -count 1 -changelog-file controller.xml
-- Generate SQL to rollback all undeployed changes
SQL> lb future-rollback-sql -changelog-file controller.xml

3.4.15 generate-apex-object

Generates the changeset for an APEX object.

Syntax

Liquibase|lb generate-apex-object|geao OPTIONS

This command uses custom extension functionality.

XML files are generated defining the DDL for the APEX object.

Options

Option	Description	Default	Deprecated
-debug | -de	Enable debug output.	False	-
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-

Example

Generate APEX object:

-- Set default output path
SQL> cd <output-files-path>
-- Generate XML files
SQL> lb generate-apex-object

3.4.16 generate-changelog

Writes changelog XML to copy the current state of the database to standard output or a file (uses core Liquibase functionality).

Syntax

 Liquibase|lb generate-changelog|gec OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-data-output-directory | -daod	Directory to write data to.	-	--dataOutputDirectory
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-diff-types | -dit	Types of objects to compare	catalogs, tables, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints, data, storedprocedures, sequences{catalogs| tables| views| columns| indexes| foreignkeys| primarykeys| uniqueconstraints| data| storedprocedures| sequences}	--diffTypes
-exclude-objects | -exo	Objects to exclude from diff	-	--excludeObjects
-include-objects | -ino	Objects to include in diff	-	--includeObjects
-include-schema | -ins	If true, the schema is included in generated changeSets.	False	--includeSchema
-include-tablespace | -int	Include the tablespace attribute in the changelog	False	--includeTableSpace
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.		--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-overwrite-output-file | -ovof	Flag to enable overwriting of output changelog file.	False	--overwriteOutputFie
-schemas | -sc	Schemas to include in diff	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Examples

Generate XML files for a database.

-- Set default output path
SQL> cd <output-files-path>
-- Generate xml files
SQL> lb generate-changelog

Generate XML files for specific schemas in a database.

SQL> lb generate-changelog -schemas hr,sales

3.4.17 generate-control-file

Generates an empty control file that you can use to start a new changelog.

Syntax

Liquibase|lb generate-control-file OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-debug | -de	Enable debug output.	False	-
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-

Example

Generate control file:

--Set default output path
SQL> cd <output-files-path>
SQL> lb generate-control-file

3.4.18 generate-object

Writes change log XML to copy the current state of the database object to a file.

Syntax

lb generate-object OPTIONS

This command uses custom extension functionality. An XML file is generated defining the DDL for the object specified. The file is used to deploy using UPDATE commands.

Options

Option	Description	Default	Deprecated
-debug | -de	Enable debug output	False	-
-fail-on-error | -foe	Set failonError attribute to true in changelog.	False	-
-log | -lo	Enable logging	False	-
-object-name | -obn	Name of the object.	-	-
-object-type | -obt	Type of object.	-	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true, they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-replace | -re	Set replaceIfExists attribute to true in changelog.	False	-
-runalways | -ra	Set runAlways attribute to true in changelog	False	-
-runonchange | -rc	Set runOnChange attribute to true in changelog.	False	-

Example

Generate the XML file for a specific object.

-- Set default output path
SQL> cd <output-files-path>
-- Generate xml files
SQL> lb generate-object -object-type table -object-name employees

3.4.19 generate-ords-module

Generates the code necessary to reproduce a module and all children using the ORDS APIs.

Syntax

Liquibase|lb generate-ords-module|geom OPTIONS

This command uses custom extension functionality. The generated script includes roles and privileges associated with the selected module.

Options

Option	Description	Default	Deprecated
Required
-module-name | -mon	The module name for which to generate code.	-	-
Optional
-debug | -de	Enable debug output.	False	-
-include-enable-schema | -ines	Dictates whether the enable_schema call will be included in the export.	True	-
-include-privs | -inp	Dictates whether privileges will be included in the export.	True	-

Example

Generate ORDS module object:

-- Set default output path
SQL> cd <output-files-path>
-- Generate XML files
SQL> lb generate-ords-module

3.4.20 generate-ords-schema

Generates the code necessary to reproduce all modules and children using the ORDS APIs.

Syntax

Liquibase|lb generate-ords-schema|geos OPTIONS

The generated script includes all roles and privileges associated with the exported modules.

Options

Option	Description	Default	Deprecated
-debug | -de	Enable debug output.	False	-
-include-enable-schema | -ines	Dictates whether the enable_schema call is included in the export.	True	-
-include-privs | -inp	Dictates whether privileges will be included in the export.	True	-

3.4.21 generate-schema

Writes changelog XML to copy the current state of the database to files.

Syntax

Liquibase|lb generate-schema|ges OPTIONS

This command uses custom extension functionality. XML files are generated defining the DDL for each object in the database. A controller XML file is created or updated identifying the individual XML files generated. The files are used to deploy using UPDATE commands.

Options

Option	Description	Default	Deprecated
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history	DATABASECHANGELOG	--databaseChangelogTableName
-debug | -de	{true|false} Enable debug output.	False	-
-fail-on-error | -foe	{true|false} Set failonError attribute to true in changelog.	False	-
-filter | -fi	The filter value is the right-hand side of a SQL comparison, that is, a SQL comparison operator (=,!=, and so on) and the value compared against. The value must contain parentheses and quotation marks where appropriate. For example: "IN ('DEPT','EMP')" The filter value is combined with the object attribute corresponding to the object name to produce a WHERE condition in the query that fetches the objects. In the preceding example, objects named DEPT and EMP are retrieved. By default, all named objects of object_type are selected.	-	-
-grants | -gr	{true|false} Export Object, System and Role grants.	False	-
-labels | -la	Label expression to use for filtering which changes to operate on.	-	-
-log | -lo	{true|false} Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-replace | -re	{true|false} Set replaceIfExists attribute to true in changelog.	False	-
-runalways | -ra	{true|false} Set runAlways attribute to true in changelog.	False	-
-runonchange | -rc	{true|false} Set runOnChange attribute to true in changelog.	False
-split | -sp	{true|false} This makes the export split the files into directories based on the object types.	False	-
-sql | -sq	{true|false} Generate a SQL file along with the changelog showing the DDL for the object. This SQL is not intended for anything other than review.	False	-
-synonyms | -sy	{true|false} Export public synonymns.	False	-

Examples

Generate XML files into separate directories.

-- Set default output path
SQL> cd <output-files-path>
-- Generate xml files
SQL> lb generate-schema -split

Generate XML files using a filter to include specific object types.

SQL> lb generate-schema -filter "IN ('DEPARTMENTS','EMPLOYEES')"

3.4.22 history

List all deployed changesets and their deployment ID.

Syntax

Liquibase|lb history|hi OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
--log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Show the deployment history.

-- Connect to target
SQL> connect <db-connect-string>
SQL> lb history

3.4.23 list-locks

List the hostname, IP address, and timestamp of the Liquibase lock record.

Syntax

Liquibase|lb list-locks|lil OPTIONS

The DATABASECHANGELOGLOCK table is read to show lock details based on the connection.

Options

Option	Description	Default	Deprecated
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.24 mark-next-changeset-ran

Marks the next change you apply as executed in your database.

Syntax

Liquibase|lb mark-next-changeset-ran|mancr OPTIONS

The mark-next-changeset-ran command is used when deploy errors occur due to Liquibase not being synchronized with changes made outside of Liquibase.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.25 mark-next-changeset-ran-sql

Writes the SQL used to mark the next change you apply as executed in your database.

Syntax

Liquibase|lb mark-next-changeset-ran-sql|mancrs OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true, they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.26 release-locks

Remove the Liquibase lock record from the DATABASECHANGELOG table.

Syntax

Liquibase|lb release-locks|rel OPTIONS

Options

Option	Description	Default	Deprecated
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.27 rollback

Roll back changes made to the database based on the specified tag.

Syntax

Liquibase|lb rollback|rb OPTIONS

The rollback command is used to revert all changes that were made to the database after a defined tag. All deployed changes are rolled back until the tag row in the DATABASECHANGELOG table.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-rollback-script | -ros	Rollback script to execute.	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Roll back changes to tag.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update for versions adding new tag for each version
SQL> lb update -changelog-file controller.xml
SQL> lb tag -tag version1
SQL> lb update -changelog-file controller.xml
SQL> lb tag -tag version2
-- Roll back to version1
SQL> lb rollback-sql -tag version1 -changelog-file controller.xml

3.4.28 rollback-count

Roll back the specified number of changes made to the database.

Syntax

Liquibase|lb rollback-count|rbc OPTIONS

The rollback-count command is used to revert the specified number of changes to the database starting from the most recent changes.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-count | -cu	The number of changes to apply.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-rollback-script | -ros	Rollback script to execute.	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Roll back the specified number of changes.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update for versions adding new tag for each version
SQL> lb update -changelog-file controller.xml
SQL> lb update -changelog-file controller.xml
-- Roll back to version1
SQL> lb rollback-count -count 1 -changelog-file controller.xml

3.4.29 rollback-count-sql

Generate the SQL to roll back the specified number of changes.

Syntax

Liquibase|lb rollback-count-sql|rbcs OPTIONS

The rollback-count command is used to revert the specified number of changes to the database starting from the most recent changes.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-count | -cu	The number of changes to apply.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-rollback-script | -ros	Rollback script to execute.	-	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back the specified number of changes.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update for versions
SQL> lb update -changelog-file controller.xml
SQL> lb update -changelog-file controller.xml
-- Roll back to version1
SQL> lb rollback-count-sql -count1 -changelog-file controller.xml

3.4.30 rollback-sql

Generate the SQL to roll back changes made to the database after a defined tag.

Syntax

Liquibase|lb rollback-sql|rbs OPTIONS

The SQL will contain all deployed changes being rolled back until the tag row in the DATABASECHANGELOG table.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-rollback-script | -ros	Rollback script to execute.	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back the specified number of changes.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update for versions adding new tag for each version
SQL> lb update -changelog-file controller.xml
SQL> lb tag -tag version1
SQL> lb update -changelog-file controller.xml
SQL> lb tag -tag version2
-- Roll back to version1
SQL> lb rollback-sql -tag version1 -changelog-file controller.xml

3.4.31 rollback-to-date

Roll back changes made to the database back to the specified date and time.

Syntax

Liquibase|lb rollback-to-date|rbtd OPTIONS

The rollback-to-date command is used to revert changes from today's date to the specified date and time..

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-date | -da [	Date to use when determining what changes to apply. The date format is YYYY-MM-DD HH:MM:SS. If including time, enclose the entire date/time in double quotes.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-rollback-script | -ros	Rollback script to execute.	-	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Roll back to the specified date and time.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update update on 2022-09-01
SQL> lb update -changelog-file controller.xml
-- Apply update on 2022-09-02
SQL> lb update -changelog-file controller.xml
-- Apply update on 2022-09-03
SQL> lb update -changelog-file controller.xml
-- Roll back to version1
SQL> lb rollback-to-date -date 2022-09-02 -changelog-file controller.xml

3.4.32 rollback-to-date-sql

Generate SQL to roll back changes made to the database back to the specified date and time.

Syntax

Liquibase|lb rollback-to-date-sql|rbtds OPTIONS

The rollback-to-date-sql command is used to generate SQL to revert changes from today's date to the specified date and time.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-date | -da [	Date to use when determining what changes to apply. The date format is YYYY-MM-DD HH:MM:SS. If including time, enclose the entire date/time in double quotes.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-rollback-script | -ros	Rollback script to execute.	-	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Generate SQL to roll back the specified date.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Apply update on 2022-09-01
SQL> lb update -changelog-file controller.xml
-- Apply update on 2022-09-02
SQL> lb update -changelog-file controller.xml
-- Apply update on 2022-09-03
SQL> lb update -changelog-file controller.xml
-- Roll back to version1
SQL> lb rollback-to-date-sql -date 2022-09-02 -changelog-file controller.xml

3.4.33 snapshot

Capture the current state of a target database.

Syntax

Liquibase|lb snapshot|sn OPTIONS

The snapshot command is used to:

• Review and track changes in your target database
• Compare a previous database state to an online database
• Compare a previous database state to another snapshot

Options

Option	Description	Default	Deprecated
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-file | -ouf	The name of the file to write the output to.	-	--outputFile
-schemas | -sc	Schemas to include in diff.	-	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing
-snapshot-format | -snf	Output format to use (JSON, YAML, or TXT)	-	--snapshotFormat

Example

Generate a snapshot capturing the current state of a database.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Take a snapshot
SQL> lb snapshot -snapshot-format json -outputfile snaptgt20220901.json

3.4.34 snapshot-reference

Capture the current state of the reference source database.

Syntax

Liquibase|lb snapshot-reference|snr OPTIONS

The snapshot command is used to:

• Review and track changes in your source database
• Compare a previous database state to an online database
• Compare a previous database state to another snapshot

Options

Option	Description	Default	Deprecated
Required
-reference-password | -rep	The reference database password.	-	--referencePassword
-reference-url | -reur	The JDBC reference database connection URL.	-	--referenceUrl
-reference-username | -reu	The reference database username.	-	--referenceUsername
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-file | -ouf	The name of the file to write the output to.	-	--outputFile
-reference-default-schema-name | -redsn	The reference default schema name to use for the database connection.	-	--referenceDefaultSchemaName
-schemas | -sc	Schemas to include in diff.	-	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing
-snapshot-format | -snf	Output format to use (JSON, YAML, or TXT)	-	--snapshotFormat

Example

Generate a snapshot capturing the current state of a database.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target
SQL> connect <db-connect-string>
-- Take a snapshot
SQL> lb snapshot-reference -snapshot-format json -outputfile snaptgt20220901.json 
-reference-url <source-url> -reference-username <source-user> -reference-password <source-password>

3.4.35 status

Generate a list of pending changesets.

Syntax

Liquibase|lb status|st OPTIONS

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing
-verbose | -ve	Verbose flag	True	-

Example

Report the number of undeployed changesets and identify them.

-- Set default output path
SQL> cd <output-files-path>
-- Check the status
SQL> lb status -verbose -changelog-file controller.xml

3.4.36 tag

Mark the current database state with the specified tag to use for roll back.

Syntax

Liquibase|lb tag|ta OPTIONS

For example, you can use the tag to mark the current database state for version, release, and so on. The tag is added to the last row in the DATABASECHANGELOG table.

Options

Option	Description	Default	Deprecated
Required
-tag | -ta	Generic 'tag to apply action to'.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Create initial tag and version tags.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target and add base tag
SQL> connect <db-connect-string>
SQL> lb tag -tag baseversion1
-- Apply update for version adding and add new tag for each version
SQL> lb update -changelog-file controller.xml
SQL> lb tag -tag version1

3.4.37 tag-exists

Verify the existence of the specified tag.

Syntax

Liquibase|lb tag-exists|tae OPTIONS

Options

Option	Description	Default	Deprecated
Required
-tag | -ta	Generic 'tag to apply action to'.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.38 unexpected-changesets

Generate a list of changesets that have been executed but are not in the current changelog.

Syntax

Liquibase|lb unexpected-changesets|unc OPTIONS

This command detects and compares the changes between the DATABASECHANGELOG table and the current changelog and reports:

• Changesets in the DATABASECHANGELOG table that do not exist in the current changelog.
• Previously deployed changesets that were deleted from your current changelog.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing
-verbose | -ve	Verbose flag	True	-

Example

Report unexpected changesets.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target and run command
SQL> connect <db-connect-string>
SQL> lb unexpected-changesets -changelog-file controller.xml

3.4.39 update

Deploy any changes in the changelog file that have not been deployed.

Syntax

Liquibase|lb update|up OPTIONS

When you run the update command, the changesets in the changelog file are read sequentially. The unique identifiers (file::id::author) in the file are compared to those stored in the DATABASECHANGELOG table.

• If the unique identifiers do not exist, Liquibase will apply the changeset to the database.

• If the unique identifiers exist, the MD5Sum of the changeset is compared to the one in the database.

  If they are different, Liquibase will produce an error message that someone has changed it unexpectedly.

  If the status of the runOnChange or runAlways changeset attribute is set to TRUE, Liquibase will re-apply the changeset. Example: <changeSet id="2" author="bob" runAlways="true">

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.40 update-count

Deploy the specified number of changes from the changelog file.

Syntax

Liquibase|lb update-count|upc OPTIONS

The update-count command applies changes and updates changesets sequentially, starting from the top of the changelog file until the number specified is reached.

• If the unique identifiers do not exist, Liquibase will apply the changeset to the database.

• If the unique identifiers exist, the MD5Sum of the changeset is compared to the one in the database.

  If they are different, Liquibase will produce an error message that someone has changed it unexpectedly.

  If the status of the runOnChange or runAlways changeset attribute is set to TRUE, Liquibase will re-apply the changeset. Example: <changeSet id="2" author="bob" runAlways="true">

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-count | -cu	The number of changes to apply.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Update database with the specified number of changesets in the changelog file.

SQL> cd <lb-changes-directory>
SQL> lb update -changelog-file controller.xml -count 2

3.4.41 update-count-sql

Generate the SQL to deploy the specified number of changes for review before running the update command.

Syntax

Liquibase|lb update-count-sql|upcs OPTIONS

Liquibase uses the raw SQL to apply database changes you have added to the changelog file.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-count | -cu	The number of changes to apply.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.42 update-sql

Generate the SQL identified in the changelog for review before running the update command.

Syntax

Liquibase|lb update-sql|ups OPTIONS

Liquibase uses the raw SQL to apply database changes you have added to the changelog file.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.43 update-testing-rollback

Updates database, then rolls back changes before updating again. It provides testing of rollback funtionality.

Syntax

Liquibase|lb update-testing-rollback|uptr OPTIONS

Use only when all pending changelogs have been verified as ready to be deployed.

A multi-step operation is used and runs in sequential order:

update changeset1; update changeset2; update changeset3\
rollback changeset3; rollback changeset2; rollback changeset1
update changeset1; update changeset2; update changeset3

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

3.4.44 update-to-tag

Deploy changes sequentially from the newest changeset up to and including the changeset with the specified tag.

Syntax

Liquibase|lb update-to-tag|uptt OPTIONS

The update-to-tag command will deploy changes only when you have previously added a tag Database Change Type in your changelog file. You cannot use the update-to-tag command with the reference to a tag created in the DATABASECHANGELOG table using the tag command. An update-to-tag-sql should always be run to review the SQL before running update-to-tag.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Update database with the changesets up to and including the changeset with the specified database tag.

SQL> cd <lb-changes-directory>
-- Edit changelog file and add tagDatabase entries for versions
-- Execute an update-to-tag using a tag specified in tagDatabase in the changelog file
SQL> lb update-to-tag -tag version1 -changelog-file controller.xml

3.4.45 update-to-tag-sql

Generate the SQL from the newest changeset up to and including the changeset with the specified tag.

Syntax

Liquibase|lb update-to-tag-sql|uptts OPTIONS

An update-to-tag-sql should always be run to review the SQL before running update-to-tag The update-to-tag-sql command will generate SQL when you have previously added a tag Database Change Type in your changelog file. You cannot use the update-to-tag command with the reference to a tag created in the DATABASECHANGELOG table using the tag cde command.

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
-tag | -ta	Generic 'tag to apply action to'.	-	-
Optional
-change-exec-listener-class | -chelc	Fully-qualified class that specifies a ChangeExecListener.	-	--changeExecListenerPropertiesFile
-change-exec-listener-properties-file | -chelpf	Path to a properties file for the ChangeExecListenerClass.	-	--changeExecListenerPropertiesFile
-contexts | -co	Context string to use for filtering which changes to operate on.	-	-
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-labels | -la	Label expression to use for filtering the changes to operate on.	-	-
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-output-default-schema | -ouds	Control whether names of objects in the default schema are fully qualified or not. If true they are qualified. If false, only objects outside the default schema are fully qualified.	False	--outputDefaultSchema
-output-file | -ouf	The name of the file to write the output to.	-	--outputFie
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Update database with the changesets up to and including the changeset with the specified database tag.

SQL> cd <lb-changes-directory>
-- Edit changelog file and add tagDatabase entries for version1
SQL> lb update-to-tag-sql -tag version1 -changelog-file controller.xml

3.4.46 validate

Validate the changelog for errors that may cause an UPDATE to fail.

Syntax

Liquibase|lb validate|va OPTIONS

The following checks are done:

• XML, YAML, JSON, and SQL structure
• Referenced files exist
• Required or prohibited attributes are correct for your database
• Duplicated id, author, and file combinations
• Checksum errors

Options

Option	Description	Default	Deprecated
Required
-changelog-file | -chf	The root changelog file.	-	--changelogFile
Optional
-database-changelog-table-name | -dactn	Name of table to use for tracking change history.	-	--databaseChangelogTableName
-debug | -de	Enable debug output.	False	-
-default-schema-name | -desn	The default schema name to use for the database connection.	-	--defaultSchemaName
-liquibase-schema-name | -lbsn	Schema to use for Liquibase objects.	-	--liquibaseSchemaName
-liquibase-tablespace-name | -lbtn	Tablespace to use for Liquibase objects.	-	--liquibaseTablespaceName
-log | -lo	Enable logging. Standard logging is INFO level (no debug flag). Debug logging is FINEST level (both log and debug flag).	False	-
-search-path | -sep	Complete list of locations to search for files such as changelog files. You can specify multiple paths by separating them with commas.	-	--searchPath
-secure-parsing | -scp	If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support.	-	--secureParsing

Example

Validate before running UPDATE.

-- Set default output path
SQL> cd <output-files-path>
-- Connect to target and validate
SQL> connect <db-connect-string>
SQL> lb validate -changelog-file controller.xml

3.4.47 version

Display version information.

Syntax

Liquibase|lb version|ve

3.5 Using SQLcl Liquibase Functionality with Open-Source Liquibase

SQLcl Liquibase with Oracle Database provides extended functionality to the Liquibase experience compared to the vanilla Liquibase client. This includes dynamically altering tables using Liquibase and SQLcl-exclusive Liquibase commands, such as generating specialized snapshots for:

• A comprehensive schema (generate-schema)
• Oracle REST Data Service (ORDS) objects (generate-ords-module and generate-ords-schema)
• Oracle APEX objects (generate-apex)

By default, the Liquibase client does not include this enhanced functionality or enable you to read the specialized changelogs generated by SQLcl Liquibase.

You can add the functionality to read these specialized changelogs to the Liquibase client by copying certain jar files from SQLcl and updating your Liquibase properties file. The steps to do this are demonstrated using the following example:

1. Connect to your Oracle Database and start the SQLcl command-line interface. The example database has a few sample tables that you can view.

   SQL> select table_name from user_tables;
   
   TABLE_NAME
   _____________
   REGIONS
   LOCATIONS
   DEPARTMENTS
   JOBS
   EMPLOYEES
   JOB_HISTORY
   COUNTRIES
   
   7 rows selected.
   

2. Create a table Fruits. This will serve as an example table to track.

   SQL> create table fruits (id number(1,0), type varchar2(50), price number, constraint fruits_pk primary key (id));
   
   Table FRUITS created.

   The table is added to the list of sample tables.

   SQL> select table_name from user_tables;
   
   TABLE_NAME
   _____________
   REGIONS
   LOCATIONS
   DEPARTMENTS
   JOBS
   EMPLOYEES
   JOB_HISTORY
   FRUITS
   COUNTRIES
   
   8 rows selected.
   

3. Generate a changelog for the Fruits table.

   SQL> lb generate-object -object-type table -object-name fruits;
   
   --Starting Liquibase at 13:43:52 (version 4.15.0 #0 built at 2022-08-19 14:45+000)
   
   Changelog created and written to file fruits_table.xml
   
   Operation completed successfully.

4. Delete the Fruits table so that you can run some tests generating it with the changelog created.

   SQL> drop table fruits;
   
   SQL> select table_name from user_tables; 
   
   TABLE_NAME
   _____________
   REGIONS
   LOCATIONS
   DEPARTMENTS
   JOBS
   EMPLOYEES
   JOB_HISTORY
   COUNTRIES
   
   7 rows selected.
   

5. Switch to the vanilla Liquibase open-source command line client, which you can download from Liquibase’s website.

6. You must provide the credentials of the Oracle Database that you are connected to in SQLcl for the vanilla Liquibase client. Create a liquibase.properties file from a blank text file. In this example, the liquibase.properties file is created in C:\Users\ [username] folder on a Windows system. The following figure shows the properties file used in this example. For more information about providing database credentials for Liquibase, see Specifying Properties in a Connection Profile.

   

7. Copy and paste the fruits_table.xml changelog file created earlier from your SQLcl bin folder to the folder location of your liquibase.properties file (in this case, C:\Users\[username]). To keep your changelog files in a different location, specify the path to the file location in the changelog-file field of your liquibase.properties file.

8. Run the update command.

   >liquibase --changelog-file=fruits_table.xml update

   

   The update fails because the SQLcl specialized changelog is an unsupported format in the vanilla Liquibase client.

   The next step is to add the functionality to read the changelogs of your SQLcl Liquibase in your vanilla Liquibase client so that you can run the changelog.

9. Copy five jar files from the lib and lib/ext folders in your SQLcl folder and add them to the lib folder of your vanilla Liquibase client (liquibase/lib):

   • dbtools-liquibase.jar (sqlcl/lib/ext)
   • dbtools-apex.jar (sqlcl/lib/ext)
   • guava-with-lf.jar (sqlcl/lib)
   • xmlparserv2_sans_jaxp_services.jar (sqlcl/lib)
   • dbtools-common.jar (sqlcl/lib)

10. Update your liquibase.properties file by adding the following line:

    change-exec-listener-class: liquibase.changelog.visitor.OracleActionChangeListener

    

    You can now read SQLcl Liquibase changelogs in your vanilla Liquibase client.

11. Run the SQL Liquibase changelog in the vanilla Liquibase client.

    >liquibase update --changelog-file fruits_table.xml

    

    In another command-line window connected to SQL and your database, you can check if the table has been successfully created.

    SQL> select table_name from user_tables;
    
    TABLE_NAME
    _____________
    REGIONS
    LOCATIONS
    DEPARTMENTS
    JOBS
    EMPLOYEES
    JOB_HISTORY
    COUNTRIES
    DATABASECHANGELOGLOCK
    DATABASECHANGELOG
    FRUITS
    COUNTRIES
    
    10 rows selected.
    

    With this functionality added to your vanilla Liquibase client, you can also read changelogs for other changelog types such as Oracle Rest Data Services (ORDS), Oracle APEX, and full database schemas along with Oracle SQLcl scripts.

    

3.6 Dynamic Object Transformation with SQLcl Liquibase and DBMS_METADATA Oracle Database Package

Oracle supplies many packages, which are automatically installed with Oracle Database, that extend database functionality. One of these packages, DBMS_METADATA, provides a way to retrieve metadata from the database dictionary as XML or SQL Data Definition Language (DDL) statements, and to submit the XML to recreate the object.

Oracle SQLcl Liquibase uses the DBMS_METADATA package to generate and execute specialized changelogs that transform the state of the database objects dynamically. This is a feature not available in the Liquibase open-source client. By using SQLcl-exclusive Liquibase commands such as generate-schema, a complete Oracle database schema can be updated. This includes altering tables and other objects already present according to the changelog’s specification. In open-source Liquibase, if the update command is run for a changelog mentioning a table that is pre-existing, it will fail. This is because open-source Liquibase is incapable of altering pre-exising objects.

SQLcl Liquibase’s specialized changelogs generated from commands such as generate-schema and generate-object use the SXML data format from the DBMS_METADATA package to execute these dynamic updates. SXML is an XML representation of object metadata that looks like a direct translation of SQL DDL into XML.

This section demonstrates this concept using a few examples. For these example, a Windows-based operating system is used.

Example 1

Connect to an Oracle database with SQLcl release 22.3. The one featured in this example includes a few sample tables. The tables that are going to be used for this example are employees and departments.

SQL> select table_name from user_tables;

TABLE_NAME
_____________
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
HIRE_DATE
PERSON_COLLECTION
COUNTRIES

9 rows selected.

1. Generate a schema of the database state using SQLcl Liquibase.

   SQL> lb generate-schema

   

2. Switching over to another command-line window, use the open-source Liquibase client to generate a changelog of the database state. Because generate-schema is a SQLcl-exclusive command that uses extended functionality, the open-source Liquibase client must use the generate-changelog command.

   Note:

   You can download the open-source Liquibase client from the Liquibase website.

   >liquibase --changelog-file=sql_test.xml generate-changelog

   

   The database state is now captured in both SQLcl Liquibase and the open-source Liquibase client.

3. In the SQLcl command-line window, make a few changes to the database.

   SQL>alter table employees add height number;
   Table EMPLOYEES altered.
   
   SQL>alter table employees add mood varchar2(50);
   Table EMPLOYEES altered.
   
   SQL>alter table departments add happiness varchar2(50);
   Table DEPARTMENTS altered.

   View these column additions in the database.

   SQL> select * from departments;
   SQL> select * from employees;

   

4. You need to use the Liquibase update command to alter the database to the state of the changelogs. This is the database state where employees and departments tables do not have the extra columns added.

   To do that, in the command-line window not connected to SQLcl and that was used for running the open-source Liquibase client commands, enter the following command:

   >liquibase --changelog-file=sql_test.xml update

   

   The update command fails because Liquibase encounters objects already existing in the database such as the tables. This standard version of Liquibase does not handle these objects dynamically.

5. In the SQLcl command-line window, the same step is repeated with SQLcl Liquibase.

   First, examine the SQL that is used to complete the schema update using the update-sql command.

   SQL>lb update-sql -changelog-file controller.xml

   

   Examining the output, you see that departments and employees tables are recognized and that you need to drop the extra columns that you created to return to the state in the changelog.

   

   In actual use cases that are more complicated, there can often be risks involved with the SQL drop command. Examining the SQL code using the update-sql command is beneficial for review. Any manual changes can then be made to the changesets and checked as needed.

   When you run the update command, the schema is successfully updated and the columns are dropped.

   SQL> lb update -changelog-file controller.xml
   --Starting Liquibase at 68:23:55 (version xml 4.15.0 #0 built at 2022-08-19 14:45+0000)
   
   -- Loaded 38 change(s)

Example 2

This example also explains the same concept, this time adding columns and a table.

For this example, in the database, a table Activities has been previously created along with columns Head_Count and Retention in the departments table and Awards in the employees table.

Changelogs are generated and the Activities table and Head_count, Retention, and columns are deleted so as to showcase using Liquibase to create them.

1. Generate the schema in SQLcl.

   Note:

   Move the changelogs from the previous example to a separate folder to avoid issues.

   SQL> lb generate-schema
   --Starting Liquibase at 09:33:52 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)

2. Switching over to another command-line window to execute commands in the open-source Liquibase client, here also generate a changelog:

   >liquibase --changelog-file=sql_test2.xml generate-changelog

   

3. In the SQLcl command-line window, delete the Activities table and Head_count, Retention, and Awards columns, so that their creation can be demonstrated with the update command.

   SQL> drop table activities;
   Table ACTIVITIES dropped.
   
   SQL> alter table employees drop column awards;
   Table EMPLOYEES altered.
   
   SQL> alter table departments drop column head_count;
   Table DEPARTMENTS altered.
   
   SQL> alter table departments drop column retention;
   Table DEPARTMENTS altered.

4. In the open-source Liquibase command-line window, run the update command.

   > liquibase --changelog-file=sql_test2.xml update

   

   The update fails when Liquibase encounters pre-existing objects.

5. In the SQLcl command-line window, generate the SQL to examine, then run the update with SQLcl Liquibase .

   SQL>lb update-sql -changelog-file controller.xml

   

   Looking at the SQL output, you can see sections involving the creation of the Activities table and SQLcl Liquibase generating the SQL statements to alter the Departments and Employees table to add the columns.

   

6. Run the update to see that the table and columns are added.

   SQL> lb update -changelog-file controller.xml

   

   SQL>select table_name from user_tables;
   SQL>select * from employees;
   SQL>select * from departments;
   
   SQL> select table_name from user_tables;
   
   TABLE_NAME
   _____________
   REGIONS
   LOCATIONS
   DEPARTMENTS
   JOBS
   EMPLOYEES
   JOB_HISTORY
   DATABASECHANGELOG_ACTIONS
   DATABASECHANGELOG
   ACTIVITIES
   COUNTRIES
   
   11 rows selected.
   

   

3.7 Liquibase Open-Source Changesets with SQLcl Liquibase

You can use changesets generated in the open-source Liquibase solution with SQLcl Liquibase. This enables you to carry over previous change management projects to SQLcl to optimize them with your Oracle Database.

The following example demonstrates this using the H2 database available in the open-source download of Liquibase and SQLcl Liquibase:

1. Download the open-source Liquibase client available on the Liquibase website. In your command-line interface, create a project folder and example H2 database with the following command:

   >liquibase init project

   
2. Start your H2 database:

   >liquibase init start-h2

   

   This also launches the database console of the empty H2 database in your browser.

   

3. In the browser console, create a sample table using the following command and click Run:

   >create table pets (id int, name varchar(256), breed varchar(256), Primary Key (id));

   

4. Capture this schema consisting of the Pets table with a Liquibase changelog.

   Open a new command-line window and generate the changelog:

   >liquibase --changelog-file=examplepets.xml generate-changelog

   Name the changelog file as examplepets.xml.

   

   The output shows where the file is saved.

5. Connect to your Oracle Database from the SQLcl 22.3 bin location.

   You can view the tables in your database with the following command. The example database shows a few sample tables.

   SQL> select table_name from user_tables;
   
   TABLE_NAME
   ____________
   REGIONS
   LOCATIONS
   DEPARTMENTS
   JOBS
   EMPLOYEES
   JOB_HISTORY
   COUNTRIES
   
   7 rows selected.
   

6. Copy the examplepets.xml changelog file to the SQLcl 22.3 bin folder.

7. In the SQL command-line interface, run the changelog to add the Pets table to your database, and check to see that the table is now included in your list.

   SQL> lb update --changelog-file examplepets.xml
   SQL> select table_name from user_tables;

   

3.8 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.9 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.

3.10 Examples Using Liquibase

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

• Review SQL
• Capture and Deploy an Object
• Capture and Deploy a Schema
• How to Exclude Table Properties When Deploying Changelogs to a New Environment
• Execute Custom SQL with RunOracleScript

3.10.1 Review SQL

To review SQL before running maintenance commands:

1. Optionally, set up to save SQL updates.

    cd <lb-changes-directory>
   spool update.sql

2. Connect to HR and capture the object.

   connect <db-connect1-string>
   lb update-sql
   spool off

3.10.2 Capture and Deploy an Object

To deploy the EMPLOYEES table from HR to HR2:

1. Set default output path.

   cd <output-files-path>

2. Connect to HR and capture the object.

   connect <db-connect1-string>
   lb generate-object -object-type table -object-name employees

3. Connect to HR2 and ensure the object does not exist.

   connect <db-connect2-string>
   drop table employees

4. Create the object in HR2 and verify that it was created.

   lb update -changelog-file employees_table.xml
   desc employees

3.10.3 Capture and Deploy a Schema

To capture HR schema and reproduce it in HR2 schema:

1. Set default output path.

   cd <output-files-path>

2. Connect to HR and capture the schema.

   connect <db-connect1-string>
   lb generate-schema

3. Setup the HR2 user.

   connect <db-connect-dba-string>
   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;

4. Create the schema objects deployed from HR in HR2 and verify.

   lb update -changelog-file controller.xml
   tables

3.10.4 How to Exclude Table Properties When Deploying Changelogs to a New Environment

When capturing tables from one environment and deploying to another, there are certain table properties you may want to exclude from the deployment while creating the table. This may be due to these properties being unavailable or undesired in your target environment, such as:

• Partitioning
• Compression
• Tablespace

SQLcl uses the Data Definition Language (DDL) session settings in the target environment, where the database objects are created or altered in (and not the environment the changelog was generated from), to generate the DDL for the objects. By turning off the DDL settings, you can prevent the corresponding table properties from being included in the table creation.

1. Generate your Liquibase changelog file for the table you want to capture and deploy.

   liquibase generate-object -object-name [table name] -object-type table

   As an example, consider the following table:

   CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST" 
      (    "A" VARCHAR2(20) NOT NULL ENABLE, 
           "B" VARCHAR2(20), 
           "C" VARCHAR2(20) NOT NULL ENABLE, 
           CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A") 
     USING INDEX 
     PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
     TABLESPACE "USERS" ENABLE 
      ) PCTFREE 0 PCTUSED 40 INITRANS 1 COMPRESS BASIC 
     STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
     TABLESPACE "USERS" 
     PARTITION BY HASH ("C") 
    (PARTITION "SYS_P803" SEGMENT CREATION DEFERRED 
     COMPRESS BASIC 
     TABLESPACE "USERS");
   

   The following is an excerpt from the resulting changelog (the partition, compression and tablespace attributes are highlighted):

   <?xml version="1.0" encoding="UTF-8"?>
   <databaseChangeLog 
   xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
   …
         <TABLE_PROPERTIES>
            <HASH_PARTITIONING>
               <COL_LIST>
                  <COL_LIST_ITEM>
                     <NAME>C</NAME>
                  </COL_LIST_ITEM>
               </COL_LIST>
               <DEFAULT_PHYSICAL_PROPERTIES>
                  <HEAP_TABLE>
                     <SEGMENT_ATTRIBUTES>
                        <PCTFREE>0</PCTFREE>
                        <PCTUSED>40</PCTUSED>
                        <INITRANS>1</INITRANS>
                        <MAXTRANS>255</MAXTRANS>
                        <STORAGE>
                           <BUFFER_POOL>DEFAULT</BUFFER_POOL>
                           <FLASH_CACHE>DEFAULT</FLASH_CACHE>
                           <CELL_FLASH_CACHE>DEFAULT</CELL_FLASH_CACHE>
                        </STORAGE>
                        <TABLESPACE>USERS</TABLESPACE>
                     </SEGMENT_ATTRIBUTES>
                     <COMPRESS>BASIC</COMPRESS>
                  </HEAP_TABLE>
               </DEFAULT_PHYSICAL_PROPERTIES>
               <PARTITION_LIST>
                  <PARTITION_LIST_ITEM>
                     <NAME>SYS_P803</NAME>
                     <SEGMENT_ATTRIBUTES>
                        <SEGMENT_CREATION_DEFERRED></SEGMENT_CREATION_DEFERRED>
                        <TABLESPACE>USERS</TABLESPACE>
                     </SEGMENT_ATTRIBUTES>
                     <COMPRESS>BASIC</COMPRESS>
                  </PARTITION_LIST_ITEM>
               </PARTITION_LIST>
            </HASH_PARTITIONING>
         </TABLE_PROPERTIES>
      </RELATIONAL_TABLE>
   </TABLE>]]></n0:source>
   </n0:createSxmlObject>
   </changeSet>
   </databaseChangeLog>
   

2. In the environment where you apply the update and generate the table, turn off the following DDL settings:

   set ddl partitioning off
   set ddl segment_attributes off
   set ddl tablespace off

   Partitioning turns off the partitioning property, segment_attributes turns off the compression property, and tablespace turns off the tablespace property.

   SQL> show ddl
   Parameters
   ---------------------------------------------
   BODY                           : ON
   COLLATION_CLAUSE               : NEVER
   CONSTRAINTS                    : ON
   CONSTRAINTS_AS_ALTER           : ON
   EMIT_SCHEMA                    : ON
   FORCE                          : ON
   INHERIT                        : ON
   INSERT                         : ON
   OID                            : ON
   PARTITIONING                   : OFF
   PRETTY                         : ON
   REF_CONSTRAINTS                : ON
   SEGMENT_ATTRIBUTES             : OFF
   SIZE_BYTE_KEYWORD              : ON
   SPECIFICATION                  : ON
   SQLTERMINATOR                  : ON
   STORAGE                        : ON
   TABLESPACE                     : OFF
   ---------------------------------------------
   End Parameters
   

3. To confirm that these settings are not included in the table generation, use the liquibase update-sql command. This command shows you the exact SQL and PL/SQL code that will be run by the update command before you execute it.

   liquibase update-sql -changelog-file partition_compression_tablespace_test_table.xml

   When partitioning, compression, and tablespace settings are turned off, the SQL code looks like the following snippet:

   -- Changeset partition_compression_tablespace_test_table.xml::fb1d77405bcd3cf7d60e555337b80ec9555134d7::(HR)-Generated
   CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST"
      (    "A" VARCHAR2(20) NOT NULL ENABLE,
           "B" VARCHAR2(20),
           "C" VARCHAR2(20) NOT NULL ENABLE,
           CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A")
     USING INDEX ENABLE
      );
   

   The following is an example of the SQL code if partitioning, compression, and tablespace settings were turned on:

   -- Changeset partition_compression_tablespace_test_table.xml::fb1d77405bcd3cf7d60e555337b80ec9555134d7::(HR)-Generated
   CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST"
      (    "A" VARCHAR2(20) NOT NULL ENABLE,
           "B" VARCHAR2(20),
           "C" VARCHAR2(20) NOT NULL ENABLE,
           CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A")
     USING INDEX
     PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
     TABLESPACE "USERS"  ENABLE
      ) PCTFREE 0 PCTUSED 40 INITRANS 1 COMPRESS BASIC
     STORAGE(
     BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
     TABLESPACE "USERS"
     PARTITION BY HASH ("C")
   (PARTITION "SYS_P803"  SEGMENT CREATION DEFERRED
     COMPRESS BASIC
     TABLESPACE "USERS");
   

4. You can apply the update and create the table by running the update command in your target environment. This creates the table as unpartitioned, uncompressed, and uses the tablespace of the target environment (rather than attempting to use the tablespace of the environment the changelog was generated from).

   liquibase update -changelog-file partition_compression_tablespace_test_table.xml

3.10.5 Execute Custom SQL with RunOracleScript

Create a RunOracleScript changeset to create a table and use PL/SQL variables in the script.

<?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>