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
andsqlcl/lib/ext
folders to theliquibase/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
- the extension installed in your Liquibase environment. Add
the following jar files from
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}
To see the help for Liquibase in SQLcl, type :
help Liquibase
To only view the examples in help, type:
help liquibase examples
<OPTIONS>: The list of options for each Liquibase command is provided in the following sections.
Database Diff Commands
New Command | Description |
---|---|
diff | Writes description of differences between two databases to standard out. |
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 |
---|---|
rollback | Rolls back the database to the state it was in when the tag was applied. |
rollback-sql | A helper command that produces the raw SQL Liquibase would run when using the rollbackByTag command. |
rollback-to-date | Rolls back the database to the state it was in at the given date/time. |
rollback-to-date-sql | A helper command that allows you to inspect the SQL Liquibase will run while using the rollback-to-date command. |
rollback-count | Rolls back the last <value> changesets. |
rollback-count-sql | Writes SQL to roll back the last <value> changesets to STDOUT. |
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 |
---|---|
data | Generate changelogs for the data. |
generate-apex-object | Generate apex objects from a database |
generate-controlfile | Generate an empty control file that you can use to start a new changelog. |
generate-db-object | Generate database objects from a database |
generate-ords-module, generate-ords-schema | Generate ords objects from a database |
generate-schema | Generate all supported objects in a schema and controller file. |
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. |
Database Update Commands
New Command | Description |
---|---|
update | Updates the database to the current version. |
update-sql | A helper command that enables you to inspect the SQL code that Liquibase will run when using the update command. |
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 |
---|---|
db-doc | Generates Javadoc-like documentation based on current database and changelog. |
Maintenance Commands
New Command | Description |
---|---|
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. |
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. |
drop-all | Drops all database objects owned by the user. |
list-locks | Lists who currently has locks on the database changelog. |
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. |
tag | "Tags" the current database state for future rollback. |
tag-exists | Checks whether the given tag already exists. |
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. |
3.4.1 calculate-checksum
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want to use.
Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
Syntax
Liquibase|lb changelog-sync-sql|chss {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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-output-default-schema|ouds |
Control whether names of objects in the default schema are fully qualified or not. 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. | - | - |
-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 |
-no-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
Syntax
Liquibase|lb changelog-sync-to-tag|chstt {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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
-tag|ta |
The tag to be used during the execution of command. | - | - |
Example
Mark changes as executed in the 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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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. | - | - |
-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 |
-no-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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Control whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | False | --outputDefaultSchema |
Example
Clear checksums:
-- Connect to target and execute command.
SQL> connect <db-connect-string>
SQL> clear-checksums
3.4.7 data
Syntax
Liquibase|lb data|da {OPTIONS}
Options
Option | Description | Default | Deprecated |
---|---|---|---|
-author|aut |
- | - | - |
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 |
-include-schema|ins |
If true, the schema is included in generated changesets. | - | - |
-include-tablespace|int |
Include the tablespace attribute in the changelog. | - | - |
-liquibase-schema-name|lbsn |
Schema to use for Liquibase objects. | - | - |
-liquibase-tablespace-name|lbtn |
Tablespace to use for Liquibase objects. | --liquibaseTablespaceName | |
-log|lo |
Enable logging. Standard logging is Debug logging is |
False | - |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
-output-file|ouf |
The name of the file to write the output to. | - | - |
-overwrite-output-file|ovof |
Controls overwriting behavior when <OUTPUT_FILE> is supplied. | - | - |
-schemas|sc |
Schemas to include in diff. | - | - |
3.4.8 db-doc
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
Syntax
Liquibase|lb diff|di {OPTIONS}
Options
Option | Description | Default | Deprecated |
---|---|---|---|
-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 |
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
-output-file|ouf |
The name of the file to write the output to. | - | - |
-reference-default-schema-name|redsn |
The reference default schema name to use for the database connection. | - | - |
-schemas|sc |
Schemas to include operation. | - | - |
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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
-output-file|ouf |
The name of the file to write the output to. | - | - |
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
Syntax
Liquibase|lb drop-all|dra {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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-schemas|sc |
Schemas to include operation. | - | - |
Example
To drop all database objects owned by the user, enter:
SQL> lb drop-all
3.4.12 future-rollback-count-sql
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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. | - | - |
-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 |
-no-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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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. | - | - |
-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 |
-no-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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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. | - | - |
-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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could 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
Syntax
Liquibase|lb generate-apex-object|geao {OPTIONS}
Options
Option | Description | Default |
---|---|---|
-applicationid|api |
ID for application to be exported. | - |
-changesby|chb |
Expects string parameter. Limit -list values to changes by the given user. | - |
-changessince|chs |
Expects date parameter (YYYY-MM-DD). Limit -list values to changes since the given date. | - |
-contexts|co |
Context string to use for filtering which changes to operate on. | - |
-debug|de |
{true|false} Enable debug output. |
False |
-deploymentsystem|desy |
Deployment system for exported feedback. | - |
-dir|di |
Save all files in the given directory. Example:
-dir some/sub/directory |
- |
-dontexporiginalids|dxoi |
Export will not emit ids as they were when the application was imported. | - |
-dontrunalways|ra |
{true|false} Set runAlways attribute to true in changelog. |
False |
-expaclassignments|exacl |
Export ACL user role assignments. | - |
-expcomments|exco |
Export developer comments. | - |
-expcomponents|exc |
- | - |
-expfeedback|exfe |
Export team development feedback for all workspaces or identified by -workspaceid. | - |
-expfeedbacksince|exfs |
Export team development feedback since date in the format YYYYMMDD. | - |
-expfiles|exfi |
Export all workspace files identified by -workspaceid. | - |
-expirnotif|exir |
Export all interactive report notifications. | - |
-expminimal|exmi |
Only export workspace definition, users, and groups. | - |
-expnosubscriptions|exns |
Do not export references to subscribed components. | - |
-exppubreports|exre |
Export all user saved public interactive reports. | - |
-expsavedreports|exsr |
Export all user saved interactive reports. | - |
-expsupportingobjects|exso |
Pass (Y)es, (N)o or (I)nstall to override the default. | - |
-expteamdevdata|exte |
Export team development data for all workspaces or identified by -workspaceid. | - |
-exptranslations|extr |
Export the translation mappings and all text from the translation repository | - |
-exptype|exty |
Comma delimited list of export types to perform:
|
|
-expworkspace|exwo |
Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified. | - |
-fail-on-error|foe |
{true|false} Set failonError attribute to true in changelog. |
False |
-instance|in |
Export all applications. | - |
-keepexportdate|ked |
Include export date in application export files. | - |
-labels|la |
Label expression to use for filtering which changes to operate on. | - |
-list|li |
Lists applications installed in the database. | - |
-log|lo |
{true|false} Enable logging. Standard logging is Debug logging is |
False |
-nochecksum|noch |
Overwrite existing files even if the contents have not changed. | - |
-split|sp |
{true|false} This makes the export split the files into directories based on the object types. |
False |
-workspaceid|woi |
Workspace ID to export all applications to or the workspace to be exported. | - |
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
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 Debug logging is |
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could 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
SQL> lb generate-changelog -schemas hr,sales
3.4.17 generate-controlfile
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 Debug logging is |
False | - |
Example
Generate control file:
--Set default output path
SQL> cd <output-files-path>
SQL> lb generate-control-file
3.4.18 generate-db-object
Syntax
lb generate-db-object|geo {OPTIONS}
Options
Option | Description | Default | Deprecated |
---|---|---|---|
-debug|de |
Enable debug output | False | - |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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. | - | - |
-sql|sq |
Generate a sql file along with the changelog showing the DDL for the object. This sql is not intended to be used for anything other than review. | - | - |
-replace|re |
Set replaceIfExists attribute to true in changelog. | False | - |
-dontrunalways|ra |
Set runAlways attribute to true in changelog | False | - |
-dontrunonchange|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
Syntax
Liquibase|lb generate-ords-module|geom {OPTIONS}
Options
Option | Description | Default | Deprecated |
---|---|---|---|
Required | |||
-module-name|mon |
The module name for which to generate code. | - | - |
Optional | |||
-debug|de |
Enable debug output. | False | - |
-exclude-enable-schema|ines |
Dictates whether the enable_schema call will be included in the export. | True | - |
-exclude-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
Syntax
Liquibase|lb generate-ords-schema|geos {OPTIONS}
Options
Option | Description | Default | Deprecated |
---|---|---|---|
-debug|de |
Enable debug output. | False | - |
-exclude-enable-schema|-ines |
Dictates whether the enable_schema call is included in the export. | True | - |
-exclude-privs|inp |
Dictates whether privileges will be included in the export. | True | - |
3.4.21 generate-schema
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 Debug logging is |
False | - |
|
Setting this flag will cause all functions, procedures and packages to undergo a complete parse to determine dependencies. This addistional processesing will cause the generation to take more time. This flag should only be used when there is a sorting issue in functions, procedures or packages. | - | - |
-replace|re |
{true|false} Set replaceIfExists attribute to true in changelog. |
False | - |
-dontrunalways|ra |
{true|false} Set runAlways attribute to true in changelog. |
False | - |
-dontrunonchange|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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|-scp |
If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
Example
Show the deployment history.
-- Connect to target
SQL> connect <db-connect-string>
SQL> lb history
3.4.23 list-locks
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
3.4.24 mark-next-changeset-ran
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
3.4.25 mark-next-changeset-ran-sql
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-file|ouf |
The name of the file to write the output to. |
3.4.26 release-locks
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
3.4.27 rollback
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'. | - | - |
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers, which can be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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. | - | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could 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
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could 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
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-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
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-output-file|ouf |
The name of the file to write the output to. | - | --outputFile |
-schemas|sc |
Schemas to include in diff. | - | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-snapshot-format|snf |
Output format to use (JSON, YAML, or TXT) | - | --snapshotFormat |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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 status
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-verbose|ve |
Verbose flag | True | - |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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.35 tag
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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.36 tag-exists
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
False | - |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
3.4.37 unexpected-changesets
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 Debug logging is |
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.38 update
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
-override-app-alias|ovaa |
Override value to be used for the APEX application alias. | - | - |
-override-app-id|ovai |
Override value to be used for the APEX application id. | - | - |
-override-app-schema|ovas |
Override value to be used for the APEX workspace schema. | - | - |
-override-app-workspace|ovaw |
Override value to be used for the APEX application workspace. | - | - |
3.4.39 update-count
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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.40 update-count-sql
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
3.4.41 update-sql
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-override-app-alias|ovaa |
Override value to be used for the APEX application alias. | - | - |
-override-app-id|ovai |
Override value to be used for the APEX application id. | - | - |
-override-app-schema|ovas |
Override value to be used for the APEX workspace schema. | - | - |
-override-app-workspace|ovaw |
Override value to be used for the APEX application workspace. | - | - |
3.4.42 update-testing-rollback
Syntax
Liquibase|lb update-testing-rollback|uptr {OPTIONS}
Use only when all pending changelogs have been verified as ready to be deployed.
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
3.4.43 update-to-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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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.44 update-to-tag-sql
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 | |||
-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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could 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.45 validate
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 |
-defaults-file|def |
Fully qualified path to the properties file you want
to use. Example:
-defaults-file/tmp/liquibase.properties |
- | - |
-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 Debug logging is |
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 |
-no-secure-parsing|scp |
If true, remove functionality from file parsers that could be used insecurely. An example is disabling remote XML entity support. |
- | --secureParsing |
-output-default-schema|ouds |
Controls whether names of objects in the default schema are fully qualified or not. If false, only objects outside the default schema are fully qualified. | - | - |
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.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
andgenerate-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:
-
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.
-
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.
-
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.
-
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.
-
Switch to the vanilla Liquibase open-source command line client, which you can download from Liquibase’s website.
-
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, theliquibase.properties
file is created inC:\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. -
Copy and paste the
fruits_table.xml
changelog file created earlier from your SQLcl bin folder to the folder location of yourliquibase.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 yourliquibase.properties
file. -
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.
-
Copy five jar files from the
lib
andlib/ext
folders in your SQLcl folder and add them to thelib
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
)
-
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.
-
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.
- Generate a schema of the database state using SQLcl
Liquibase.
SQL> lb generate-schema
-
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.
- In the SQLcl command-line window, make a few changes to the
database.
View these column additions in 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.
SQL> select * from departments; SQL> select * from employees;
-
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.
-
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 theupdate-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.
-
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)
-
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
-
In the SQLcl command-line window, delete the
Activities
table andHead_count
,Retention
, andAwards
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.
-
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.
-
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 theDepartments
andEmployees
table to add the columns. -
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:
- 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
- Start your
H2
database:>liquibase init start-h2
This also launches the database console of the empty
H2
database in your browser. -
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));
-
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.
-
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.
-
Copy the
examplepets.xml
changelog file to the SQLcl 22.3 bin folder. -
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:
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.
3.10.5 Execute Custom SQL with RunOracleScript
<?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>