3 Liquibase Support in SQLcl
This chapter covers the Liquibase feature in SQLcl. It has the following topics:
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
See this LiveLabs workshop on change management using SQLcl and Liquibase.
3.2 Requirements for Using Liquibase
The two important aspects for using the Liquibase functionality are capturing and deploying objects in an Oracle database.
Capture Objects
To capture an object or a schema, you must have SQLcl 19.2 or later installed.
In this release, you can only capture objects from the schema you are connected to in SQLcl. You also need write permission on the directory in which you save the files.
If you are capturing an entire schema, the user you are connected to must have the privilege to create a table. The DATABASECHANGELOG_EXPORT table is created internally to gather object details and sort them correctly. The created object is automatically excluded from the capture process and destroyed upon capture completion.
Deploy Objects
Liquibase uses the DATABASECHANGELOG table to track the changesets that have been run. The DATABASECHANGELOGLOCK table ensures that only one instance of Liquibase is running at a time. The DATABASECHANGELOG_ACTIONS table tracks the object state and the SQL statements executed during deployment.
-
SQLcl
Deploying changes to any database through SQLcl requires the 19.2 release or later and the privilege to create a table. You must have necessary permissions to create any object type through the change that you are deploying.
-
Liquibase
If you use Liquibase directly to deploy changesets, you need to have:
- the extension installed in your Liquibase environment. Add the
dbtools-liquibase.jar
file inliquibase/lib/ext
. - the privileges to create a table and a package.
- the extension installed in your Liquibase environment. Add the
3.3 Supported Types
DDL types use create or replace syntax. A snapshot of the object is taken before applying the change so automatic rollback to the last known state is supported.
SXML types support automatic alter generation with automatic rollback support.
DDL types have their own change type.
- CONSTRAINT
- DIMENSION
- DIRECTORY
- FUNCTION
- JOB
- OBJECT_GRANT
- PACKAGE_BODY
- PACKAGE_SPEC
- PROCEDURE
- PUBLIC_SYNONYM
- REF_CONSTRAINT
- SYNONYM
- TRIGGER
- TYPE BODY
- TYPE SPEC
SXML types share the SXML change type.
- AQ_QUEUE
- AQ_QUEUE_TABLE
- AQ_TRANSFORM
- ASSOCIATION
- AUDIT
- AUDIT_OBJ
- CLUSTER
- CONTEXT
- DB_LINK
- DEFAULT_ROLE
- FGA_POLICY
- JOB
- LIBRARY
- MATERIALIZED_VIEW
- MATERIALIZED_VIEW_LOG
- OPERATOR
- PROFILE
- PROXY
- REFRESH_GROUP
- RESOURCE_COST
- RLS_CONTEXT
- RLS_GROUP
- RMGR_CONSUMER_GROUP
- RMGR_INTITIAL_CONSUMER_GROUP
- RMGR_PLAN
- RMGR_PLAN_DIRECTIVE
- ROLE
- ROLLBACK_SEGMENT
- SEQUENCE
- TABLE
- TABLESPACE
- TRIGGER XS_ACL
- TRUSTED_DB_LINK
- USER
- VIEW
- XMLSCHEMA
- XS_ACL_PARAM INDEX
- XS_DATA_SECURITY
- XS_ROLE
- XS_ROLESET
- XS_ROLE_GRANT
- XS_SECURITY_CLASS
- XS_USER
3.4 Supported Liquibase Commands in SQLcl
You can invoke the Liquibase commands in SQLcl with
liquibase
or lb
.
To display a list of all available commands, execute liquibase
or
lb
with no arguments.
3.4.1 LB GENOBJECT
APEX
Syntax
lb genobject -type {APEX} -applicationid APPLICATIONID -workspaceid WORKSPACEID
[-instance] [-expWorkspace][-expMinimal] [-expFiles] [-skipExportDate] [-expPubReports] [-expSavedReports]
[-expIRNotif] [-expTranslations] [-expFeedback] [-expTeamdevdata] [-deploymentSystem DEPLOYMENTSYSTEM]
[-expFeedbackSince EXPFEEDBACKSINCE] [-expOriginalIds] [-expNoSubscriptions] [-expComments]
[-expSupportingObjects {Y,N,I}] [-expACLAssignments] [-dir DIR] [-list LIST]
[-changesSince CHANGESSINCE] [-changesBy CHANGESBY] [-nochecksum] [-split]
[-expComponents EXPCOMPONENTS] [-context CONTEXT] [-label LABEL]
[-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]
Generates a change log for APEX objects.
Named Parameters
Parameter | Description | Default |
---|---|---|
-type {APEX} |
Generate output for APEX objects. | - |
-applicationid
APPLICATIONID |
Specify ID for application to be exported. | - |
-workspaceid
WORKSPACEID |
Specify workspace ID for all applications to be exported or the workspace to be exported. | - |
-instance |
Export all applications. | False |
-expWorkspace |
Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified. | False |
-expMinimal |
Export only workspace definition, users, and groups. | False |
|
Export all workspace files identified by -workspaceid. | False |
-skipExportDate |
Exclude export date from application export files | False |
-expPubReports |
Export all user-saved public interactive reports. | False |
-expSavedReports |
Export all user-saved interactive reports. | False |
-expIRNotif |
Export all interactive report notifications. | False |
-expTranslations |
Export the translation mappings and all text from the translation repository. | False |
-expFeedback |
Export team development feedback for all workspaces or identified by -workspaceid. | False |
-expTeamdevdata
|
Export team development data for all workspaces or identified by -workspaceid. | False |
-deploymentSystem
DEPLOYMENTSYSTEM |
Specify deployment system for exported feedback. | - |
-expFeedbackSince
EXPFEEDBACKSINCE |
Export team development feedback since date in the format YYYYMMDD. | - |
-expOriginalIds |
Export emits ids as they were when the application was imported. | False |
-expNoSubscriptions |
Does not export references to subscribed components. | False |
-expComments
|
Export developer comments. | False |
-expSupportingObjects
{Y,N,I} |
Export supporting objects. Pass (Y)es, (N)o or (I)nstall to override the default (default: N). | N |
-expACLAssignments
|
Export ACL User Role Assignments. | False |
-dir DIR
|
Save all files in the given directory. For example, -dir some/sub/directory. | - |
-list LIST
|
List all changed applications in the workspace or components in the application. | - |
-changesSince
CHANGESSINCE
|
Expect date parameter (yyyy-mm-dd). Limit -list values to changes since the given date. | - |
-changesBy CHANGESBY
|
Expect string parameter. Limit -list values to changes by the given user. | - |
-nochecksum |
Overwrite existing files even if the contents have not changed. | False |
-split |
Split applications into multiple files. | False |
-expComponents
EXPCOMPONENTS
|
Export application components. All remaining parameters must be of form TYPE:ID. | - |
Common Generator Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label LABEL
|
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange
|
Set runOnChange value in changeset | False |
-runalways
|
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | False |
Example
SQL> lb genobject -type apex -applicationid 4900
Exporting Application 4900
Action successfully completed please review created file f4900.xml
ORDS RESTful Service Modules
Syntax
lb genobject -type {ORDS} -name NAME [-noenable] [-noprivs]
[-context CONTEXT] [-label LABEL] [-emit_schema] [-fail]
[-replace] [-runonchange] [-runalways] [-debug]
Generates a change log for an ORDS (Oracle REST Data Services) RESTful service module.
Named Parameters
Parameter | Description | Default |
---|---|---|
-type {ORDS} |
Generate output for ORDS module | - |
-name
NAME |
Module name | - |
-noenable |
Dictates whether the enable_schema call is included in the export | True |
-noprivs |
Dictates whether Privs is included in the export | True |
Common Generator Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label LABEL
|
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange
|
Set runOnChange value in changeset | False |
-runalways
|
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | False |
Database Object
Syntax
lb genobject -type TYPE -name NAME
[-context CONTEXT] [-label LABEL] [-emit_schema]
[-fail] [-replace] [-runonchange] [-runalways] [-debug]
Generates a change log for a specified database object.
Named Parameters
Parameter | Description |
---|---|
-type
TYPE |
Type of object in the database |
-name
NAME |
Object name |
Common Generator Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label LABEL
|
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange
|
Set runOnChange value in changeset | False |
-runalways
|
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | False |
Database Object Types Supported
Type | Type | Type |
---|---|---|
AQ_QUEUE | AQ_QUEUE_TABLE | AQ_TRANSFORM |
ASSOCIATION | AUDIT | AUDIT_OBJ |
CLUSTER | CONSTRAINT | CONTEXT |
DB_LINK | DEFAULT_ROLE | DIMENSION |
FGA_POLICY | FUNCTION | INDEX |
JOB | LIBRARY | MATERIALIZED_VIEW |
MATERIALIZED_VIEW_LOG | OBJECT_GRANT | OPERATOR |
PACKAGE_SPEC | PACKAGE_BODY | PROCEDURE |
PROFILE | PROXY | PUBLIC_SYNONYM |
REF_CONSTRAINT | REFRESH_GROUP | RESOURCE_COST |
RLS_CONTEXT | RLS_GROUP | RLS_POLICY |
RMGR_CONSUMER_GROUP | RMGR_PLAN | RMGR_PLAN_DIRECTIVE |
ROLE | ROLLBACK_SEGMENT | SEQUENCE |
SYNONYM | TABLE | TABLESPACE |
TRIGGER | TRUSTED_DB_LINK | TYPE |
TYPE_SPEC | TYPE_BODY | USER |
VIEW | XMLSCHEMA XS_USER | XS_ROLE |
XS_ROLESET | XS_ROLE_GRANT | XS_SECURITY_CLASS |
XS_DATA_SECURITY | XS_ACL | XS_ACL_PARAM |
XS_NAMESPACE | RMGR_INTITIAL_CONSUMER_GROUP |
Example
SQL> lb genobject -type table -name zipcodes
Action successfully completed please review created file zipcodes_table.xml
3.4.2 LB GENSCHEMA
Syntax
liquibase(lb) genschema [-noreport] [-synonyms] [-grants]
[-context CONTEXT] [-label LABEL] [-emit_schema]
[-fail] [-replace] [-runonchange] [-runalways] [-debug]
Captures the entire schema that the user is connected to in SQLcl. An XML file is created in the current working directory for each object in the schema, and a controller.xml file is created. The controller file is a change log that includes all files in the proper order to enable the schema to be deployed correctly.
Named Parameters
Parameter | Description | Default |
---|---|---|
-noreport |
Suppress screen output | True |
-synonyms |
Include public synonyms | False |
-grants |
Include grants | False |
Common Generator Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label
LABEL |
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange |
Set runOnChange value in changeset | False |
-runalways |
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | False |
Example
SQL> lb genschema
lb genschema
[Type - TYPE_SPEC]: 153 ms
[Type - TYPE_BODY]: 29 ms
[Type - SEQUENCE]: 48 ms
[Type - CLUSTER]: 27 ms
[Type - TABLE]: 36 ms
[Type - MATERIALIZED_VIEW_LOG]: 19 ms
[Type - MATERIALIZED_VIEW]: 6 ms
[Type - VIEW]: 148 ms
[Type - REF_CONSTRAINT]: 272 ms
[Type - DIMENSION]: 23 ms
[Type - FUNCTION]: 27 ms
[Type - PROCEDURE]: 64 ms
[Type - PACKAGE_SPEC]: 171 ms
[Type - DB_LINK]: 14 ms
[Type - SYNONYM]: 22 ms
[Type - INDEX]: 202 ms
[Type - TRIGGER]: 51 ms
[Type - PACKAGE_BODY]: 252 ms
[Method loadCaptureTable]: 1864 ms
[Method parseCaptureTableRecords]: 7342 ms
[Method sortCaptureTable]: 30 ms
[Method createExportChangeLogs]: 3 ms
Export Flags Used:
Export Grants false
Export Synonyms false
3.4.3 LB GENCONTROLFILE
Syntax
liquibase(lb) gencontrolfile
Creates an empty changelog, master.xml
, with a
placeholder to include files in the current working directory. You can use this
command when you are creating your own changelog with custom changeset
inclusions.
Example
SQL> lb gencontrolfile
Action successfully completed please review created file controller.xml
3.4.4 LB UPDATE
Syntax
liquibase(lb) update -changelog CHANGELOG [-context CONTEXT]
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]
Enables you to apply database changes that you and other developers have added to the change log file.
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Common Update Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label
LABEL |
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-log |
Generate a log of work performed | False |
-debug |
Generate and display additional debug output | False |
Example
SQL> lb update -changelog zipcodes_table.xml
ScriptRunner Executed:zipcodes_table.xml::3b128595dbfbc34086c0a18ff38e090529999::Generated
3.4.5 LB UPDATESQL
Syntax
liquibase(lb) updatesql -changelog CHANGELOG [-context CONTEXT]
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]
Generates and renders to the screen the SQL statements that would be applied for a specific change log.
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Common Generator Parameters
Parameter | Description | Default |
---|---|---|
-context CONTEXT
|
Specify changeset contexts to execute | - |
-label
LABEL |
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange |
Set runOnChange value in changeset | False |
-runalways |
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | False |
Example
SQL> lb updatesql -changelog zipcodes_tablev2.xml
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: zipcodes_tablev2.xml
-- Ran at: 6/3/20 12:49 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************
-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)',
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 12:49:05.779', 'YYYY-MM-DD HH24:MI:SS.FF') WHERE ID = 1 AND LOCKED = 0;
-- Changeset zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated
ALTER TABLE "ZIPCODES" ADD ("LON" NUMBER)
/
-- Logging Oracle Extension actions to the Database.
DECLARE
id varchar2(200) := '3595dbfbc34086449c0a18ff38e090529999';
rawAction clob;
rawSxml clob;
myrow varchar2(2000);
action clob := '';
sxml clob := '';
dep varchar2(200) := '1202945773';
author varchar2(200) := 'Generated';
filename varchar2(200) := 'zipcodes_tablev2.xml';
insertlog varchar2(200) := 'insert into DATABASECHANGELOG_ACTIONS (id,author,filename,sql,sxml,deployment_id)
values (:id,:author,:filename,:action,:sxml,:dep) returning rowid into :out';
updateaction varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sql = sql ||:action where rowid = :myrow ';
updatesxml varchar2(200) := 'update DATABASECHANGELOG_ACTIONS set sxml = sxml ||:sxml where rowid = :myrow ';
begin
action := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'
{QUxURVIgVEFCTEUgIlpJUENPREVTIiBBREQgKCJMT04iIE5VTUJFUikKLwogIC0tIE9SQS0zOTM0MTogQ
2Fubm90IGNvbnZlcnQgYW4gZXhpc3RpbmcgY29sdW1uIGludG8gYW4gaWRlbnRpdHkgY29sdW1uLgov}')));
sxml := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(q'
{PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+PFRBQkxFIHhtbG5zPSJodHRwOi8veG1sbnMub3Jh
Y2xlLmNvbS9rdSIgdmVyc2lvbj0iMS4wIj4KICAgPFNDSEVNQT5TS1VUWjwvU0NIRU1BPgogICA8TkFNRT5aSVBDT0RFUzwvTkFNRT4KICAgPFJFT
EFUSU9OQUxfVEFCTEU+CiAgICAgIDxDT0xfTElTVD4KICAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxOQU1FPklEPC9OQU1FPg
ogICAgICAgICAgICA8REFUQVRZUEU+TlVNQkVSPC9EQVRBVFlQRT4KICAgICAgICAgICAgPE5PVF9OVUxMLz4KICAgICAgICAgPC9DT0xfTElTVF9
JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+Q09ERTwvTkFNRT4KICAgICAgICAgICAgPERBVEFUWVBFPlZBU
kNIQVIyPC9EQVRBVFlQRT4KICAgICAgICAgICAgPExFTkdUSD42PC9MRU5HVEg+CiAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0N
PTVA8L0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BT
UU+Q0lUWTwvTkFNRT4KICAgICAgICAgICAgPERBVEFUWVBFPlZBUkNIQVIyPC9EQVRBVFlQRT4KICAgICAgICAgICAgPExFTkdUSD41MDwvTEVOR1R
IPgogICAgICAgICAgICA8Q09MTEFURV9OQU1FPlVTSU5HX05MU19DT01QPC9DT0xMQVRFX05BTUU+CiAgICAgICAgIDwvQ09MX0xJU1RfSVRFTT4KI
CAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxOQU1FPlNUQVRFPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+VkFSQ0hBUjI
8L0RBVEFUWVBFPgogICAgICAgICAgICA8TEVOR1RIPjUwPC9MRU5HVEg+CiAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0NPTVA8L
0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+Q09
VTlRZPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+VkFSQ0hBUjI8L0RBVEFUWVBFPgogICAgICAgICAgICA8TEVOR1RIPjUwPC9MRU5HVEg+C
iAgICAgICAgICAgIDxDT0xMQVRFX05BTUU+VVNJTkdfTkxTX0NPTVA8L0NPTExBVEVfTkFNRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICA
gICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+QVJFQV9DT0RFPC9OQU1FPgogICAgICAgICAgICA8REFUQVRZUEU+TlVNQkVSP
C9EQVRBVFlQRT4KICAgICAgICAgICAgPFBSRUNJU0lPTj4zPC9QUkVDSVNJT04+CiAgICAgICAgICAgIDxTQ0FMRT4wPC9TQ0FMRT4KICAgICAgICA
gPC9DT0xfTElTVF9JVEVNPgogICAgICAgICA8Q09MX0xJU1RfSVRFTT4KICAgICAgICAgICAgPE5BTUU+TEFUPC9OQU1FPgogICAgICAgICAgICA8R
EFUQVRZUEU+TlVNQkVSPC9EQVRBVFlQRT4KICAgICAgICAgPC9DT0xfTElTVF9JVEVNPgogICAgICA8L0NPTF9MSVNUPgogICAgICA8UFJJTUFSWV9
LRVlfQ09OU1RSQUlOVF9MSVNUPgogICAgICAgICA8UFJJTUFSWV9LRVlfQ09OU1RSQUlOVF9MSVNUX0lURU0+CiAgICAgICAgICAgIDxDT0xfTElTV
D4KICAgICAgICAgICAgICAgPENPTF9MSVNUX0lURU0+CiAgICAgICAgICAgICAgICAgIDxOQU1FPklEPC9OQU1FPgogICAgICAgICAgICAgICA8L0N
PTF9MSVNUX0lURU0+CiAgICAgICAgICAgIDwvQ09MX0xJU1Q+CiAgICAgICAgICAgIDxVU0lOR19JTkRFWD4KICAgICAgICAgICAgICAgPElOREVYX
0FUVFJJQlVURVM+CiAgICAgICAgICAgICAgICAgIDxQQ1RGUkVFPjEwPC9QQ1RGUkVFPgogICAgICAgICAgICAgICAgICA8SU5JVFJBTlM+MjwvSU5
JVFJBTlM+CiAgICAgICAgICAgICAgICAgIDxNQVhUUkFOUz4yNTU8L01BWFRSQU5TPgogICAgICAgICAgICAgICAgICA8VEFCTEVTUEFDRT5VU0VSU
zwvVEFCTEVTUEFDRT4KICAgICAgICAgICAgICAgICAgPExPR0dJTkc+WTwvTE9HR0lORz4KICAgICAgICAgICAgICAgPC9JTkRFWF9BVFRSSUJVVEV
TPgogICAgICAgICAgICA8L1VTSU5HX0lOREVYPgogICAgICAgICA8L1BSSU1BUllfS0VZX0NPTlNUUkFJTlRfTElTVF9JVEVNPgogICAgICA8L1BSS
U1BUllfS0VZX0NPTlNUUkFJTlRfTElTVD4KICAgICAgPERFRkFVTFRfQ09MTEFUSU9OPlVTSU5HX05MU19DT01QPC9ERUZBVUxUX0NPTExBVElPTj4
KICAgICAgPFBIWVNJQ0FMX1BST1BFUlRJRVM+CiAgICAgICAgIDxIRUFQX1RBQkxFPgogICAgICAgICAgICA8U0VHTUVOVF9BVFRSSUJVVEVTPgogI
CAgICAgICAgICAgICA8U0VHTUVOVF9DUkVBVElPTl9ERUZFUlJFRC8+CiAgICAgICAgICAgICAgIDxQQ1RGUkVFPjEwPC9QQ1RGUkVFPgogICAgICA
gICAgICAgICA8UENUVVNFRD40MDwvUENUVVNFRD4KICAgICAgICAgICAgICAgPElOSVRSQU5TPjE8L0lOSVRSQU5TPgogICAgICAgICAgICAgICA8T
UFYVFJBTlM+MjU1PC9NQVhUUkFOUz4KICAgICAgICAgICAgICAgPFRBQkxFU1BBQ0U+VVNFUlM8L1RBQkxFU1BBQ0U+CiAgICAgICAgICAgICAgIDx
MT0dHSU5HPlk8L0xPR0dJTkc+CiAgICAgICAgICAgIDwvU0VHTUVOVF9BVFRSSUJVVEVTPgogICAgICAgICAgICA8Q09NUFJFU1M+TjwvQ09NUFJFU
1M+CiAgICAgICAgIDwvSEVBUF9UQUJMRT4KICAgICAgPC9QSFlTSUNBTF9QUk9QRVJUSUVTPgogICA8L1JFTEFUSU9OQUxfVEFCTEU+CjwvVEFCTEU+}')));
execute immediate insertlog using id,author,filename,action,sxml,dep returning into myrow;
end;
/
--;
INSERT INTO JDOE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION,
COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3595dbfbc34086449c0a18ff38e090529999',
'Generated', 'zipcodes_tablev2.xml', SYSTIMESTAMP, 2, '8:5262bd1c3989655295b5641b82868158',
'createSxmlObject objectName=ZIPCODES, ownerName=JDOE, '', 'EXECUTED', NULL, NULL, '3.8.9', '1202945773');
-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
3.4.6 LB ROLLBACK
Syntax
liquibase(lb) rollback -changelog CHANGELOG [-context CONTEXT]
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-log]
[-debug] (-count COUNT | -date DATE)
Rolls back changes starting from the last change applied using the input change log. The count can be higher than the changes in the change log. 999, which is the maximum size, rolls back all the changes.
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Rollback Style Options
Select one of the following options:
Option | Description |
---|---|
-count
COUNT |
Number of changes to apply |
-date
DATE |
Date to roll back to |
Common Update Parameters
Parameter | Description | Default |
---|---|---|
-context
CONTEXT |
Specify changeset contexts to execute | - |
-label
LABEL |
Filter the changelog using labels | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange |
Set runOnChange value in changeset | False |
-runalways |
Set runAlways value in changeset | False |
-log |
Generate a log of work performed | False |
-debug |
Generate and display additional debug output | False |
Example
lb rollback -changelog zipcodes_tablev2.xml -count 100
ScriptRunner Executed:zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated
3.4.7 LB ROLLBACKSQL
Syntax
liquibase(lb) rollbacksql -changelog CHANGELOG [-context CONTEXT]
[-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]
(-count COUNT | -date DATE)
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Rollback Style Options
Select one of the following options:
Option | Description |
---|---|
-count
COUNT |
Number of changes to apply |
-date
DATE |
Date to roll back to |
Common Update Parameters
Parameter | Description | Default |
---|---|---|
-label
LABEL |
Filter the changelog using labels | - |
-context
CONTEXT |
Specify changeset contexts to execute | - |
-emit_schema |
Include the schema when generating DDL | False |
-fail |
Set failOnError value in changeset | False |
-replace |
Set replaceIfExists value in changeset | False |
-runonchange |
Set runOnChange value in changeset | False |
-runalways |
Set runAlways value in changeset | False |
-debug |
Generate and display additional debug output | - |
Example
lb rollbacksql -changelog zipcodes_tablev2.xml -count 100
-- *********************************************************************
-- Rollback 100 Change(s) Script
-- *********************************************************************
-- Change Log: zipcodes_tablev2.xml
-- Ran at: 6/3/20 12:55 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************
-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)',
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 12:55:19.175', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE ID = 1 AND LOCKED = 0;
-- Rolling Back ChangeSet:
zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated
ALTER TABLE "JDOE"."ZIPCODES" DROP ("LON");
delete from DATABASECHANGELOG_actions where id = '3595dbfbc34086449c0a18ff38e090529999' and
filename = 'zipcodes_tablev2.xml' and author ='Generated' and sequence = (select Max(sequence)
from DATABASECHANGELOG_actions where id = '3595dbfbc34086449c0a18ff38e090529999' and
filename = 'zipcodes_tablev2.xml' and author ='Generated');
DELETE FROM JDOE.DATABASECHANGELOG WHERE ID = '3595dbfbc34086449c0a18ff38e090529999' AND
AUTHOR = 'Generated' AND FILENAME = 'zipcodes_tablev2.xml';
-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
3.4.8 LB DIFF
Syntax
liquibase(lb) diff -url URL -user USER -pass PASS [-noreport]
Displays differences between the current connection and the specified database. When the report is true, the output will be in the form of a text report. When the report is false, which is the default, the output will be in the form of a change log.
The destination URL format is HOST:PORT:SID or HOST:PORT/SERVICE.
Named Parameters
Parameter | Description | Default |
---|---|---|
-url URL |
JDBC URL for destination database | - |
-user
USER |
Destination user | - |
-pass
PASS |
Destination password | - |
-noreport |
Results output as a changelog, not a text report | True |
Example
lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr
Action successfully completed please review created file diffResult.txt
!more diffResult.txt
Reference Database: JDOE @ jdbc:oracle:thin:@localhost:1521/ORCLPDB1 (Default Schema: JDOE)
Comparison Database: HR @ jdbc:oracle:thin:@localhost:1521/ORCLPDB1 (Default Schema: HR)
Compared Schemas: JDOE -> HR
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s):
JDOE.ZIPCODES.AREA_CODE
JDOE.DATABASECHANGELOG.AUTHOR
JDOE.DATABASECHANGELOG_ACTIONS.AUTHOR
JDOE.DATABASECHANGELOG_DETAILS.AUTHOR
JDOE.ZIPCODES.CITY
JDOE.ZIPCODES.CODE
JDOE.DATABASECHANGELOG.COMMENTS
JDOE.DATABASECHANGELOG_DETAILS.COMMENTS
JDOE.DATABASECHANGELOG.CONTEXTS
JDOE.DATABASECHANGELOG_DETAILS.CONTEXTS
JDOE.ZIPCODES.COUNTY
JDOE.DATABASECHANGELOG.DATEEXECUTED
JDOE.DATABASECHANGELOG_DETAILS.DATEEXECUTED
JDOE.DATABASECHANGELOG.DEPLOYMENT_ID
JDOE.DATABASECHANGELOG_ACTIONS.DEPLOYMENT_ID
JDOE.DATABASECHANGELOG_DETAILS.DEPLOYMENT_ID
JDOE.DATABASECHANGELOG.DESCRIPTION
JDOE.DATABASECHANGELOG_DETAILS.DESCRIPTION
JDOE.DATABASECHANGELOG.EXECTYPE
JDOE.DATABASECHANGELOG_DETAILS.EXECTYPE
JDOE.DATABASECHANGELOG.FILENAME
JDOE.DATABASECHANGELOG_ACTIONS.FILENAME
JDOE.DATABASECHANGELOG_DETAILS.FILENAME
JDOE.DATABASECHANGELOG.ID
JDOE.DATABASECHANGELOGLOCK.ID
JDOE.DATABASECHANGELOG_ACTIONS.ID
JDOE.DATABASECHANGELOG_DETAILS.ID
JDOE.ZIPCODES.ID
JDOE.DATABASECHANGELOG.LABELS
JDOE.DATABASECHANGELOG_DETAILS.LABELS
JDOE.ZIPCODES.LAT
JDOE.DATABASECHANGELOG.LIQUIBASE
JDOE.DATABASECHANGELOG_DETAILS.LIQUIBASE
JDOE.DATABASECHANGELOGLOCK.LOCKED
JDOE.DATABASECHANGELOGLOCK.LOCKEDBY
JDOE.DATABASECHANGELOGLOCK.LOCKGRANTED
JDOE.ZIPCODES.LON
JDOE.DATABASECHANGELOG.MD5SUM
JDOE.DATABASECHANGELOG_DETAILS.MD5SUM
JDOE.DATABASECHANGELOG.ORDEREXECUTED
JDOE.DATABASECHANGELOG_ACTIONS.SEQUENCE
JDOE.DATABASECHANGELOG_ACTIONS.SQL
JDOE.DATABASECHANGELOG_DETAILS.SQL
JDOE.ZIPCODES.STATE
JDOE.DATABASECHANGELOG_ACTIONS.SXML
JDOE.DATABASECHANGELOG_DETAILS.SXML
JDOE.DATABASECHANGELOG.TAG
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s):
PK_DATABASECHANGELOGLOCK UNIQUE ON JDOE.DATABASECHANGELOGLOCK(ID)
SYS_C00122620 UNIQUE ON JDOE.DATABASECHANGELOG_ACTIONS(ID, AUTHOR, FILENAME, SEQUENCE)
SYS_C00122637 UNIQUE ON JDOE.ZIPCODES(ID)
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s):
PK_DATABASECHANGELOGLOCK on JDOE.DATABASECHANGELOGLOCK(ID)
SYS_C00122620 on JDOE.DATABASECHANGELOG_ACTIONS(ID, AUTHOR, FILENAME, SEQUENCE)
SYS_C00122637 on JDOE.ZIPCODES(ID)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s):
DATABASECHANGELOG
DATABASECHANGELOGLOCK
DATABASECHANGELOG_ACTIONS
ZIPCODES
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s):
DATABASECHANGELOG_DETAILS
Unexpected View(s): NONE
Changed View(s): NONE
SQL> lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr -noreport
Action successfully completed please review created file diffResult.xml
diffResult.xml
!more diffResult.xml
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<changeSet author="jdoe (generated)" id="1591203948991-1">
<createTable tableName="DATABASECHANGELOG_ACTIONS">
<column name="ID" type="VARCHAR2(255 BYTE)">
<constraints nullable="false"/>
</column>
<column name="SEQUENCE" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="SQL" type="CLOB"/>
<column name="SXML" type="CLOB"/>
<column name="AUTHOR" type="VARCHAR2(255 BYTE)">
<constraints nullable="false"/>
</column>
<column name="FILENAME" type="VARCHAR2(255 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DEPLOYMENT_ID" type="VARCHAR2(10 BYTE)"/>
</createTable>
</changeSet>
<changeSet author="jdoe (generated)" id="1591203948991-2">
<createView fullDefinition="true" viewName="DATABASECHANGELOG_DETAILS">
CREATE OR REPLACE FORCE VIEW DATABASECHANGELOG_DETAILS (DEPLOYMENT_ID, ID, AUTHOR,
FILENAME, SQL, SXML, DATEEXECUTED, EXECTYPE, MD5SUM, DESCRIPTION, COMMENTS, LIQUIBASE,
CONTEXTS, LABELS) AS SELECT da.deployment_id, da.id, da.author,
da.filename, da.sql, da.sxml, d.dateexecuted, d.exectype, d.md5sum,
d.description, d.comments, d.liquibase, d.contexts, d.labels
FROM databasechangelog d LEFT JOIN databasechangelog_actions da ON d.id = da.id
AND d.author = da.author AND
d.filename = da.filename ORDER BY 1,7</createView>
</changeSet>
<changeSet author="jdoe (generated)" id="1591203948991-3">
<createTable tableName="ZIPCODES">
<column name="ID" type="NUMBER">
<constraints nullable="false" primaryKey="true" primaryKeyName="SYS_C00122637"/>
</column>
<column name="CODE" type="VARCHAR2(6 BYTE)"/>
<column name="CITY" type="VARCHAR2(50 BYTE)"/>
<column name="STATE" type="VARCHAR2(50 BYTE)"/>
<column name="COUNTY" type="VARCHAR2(50 BYTE)"/>
<column name="AREA_CODE" type="NUMBER(3, 0)"/>
<column name="LAT" type="NUMBER"/>
<column name="LON" type="NUMBER"/>
</createTable>
</changeSet>
<changeSet author="jdoe (generated)" id="1591203948991-4">
<createIndex indexName="SYS_C00122620" tableName="DATABASECHANGELOG_ACTIONS" unique="true">
<column name="ID"/>
<column name="AUTHOR"/>
<column name="FILENAME"/>
<column name="SEQUENCE"/>
</createIndex>
</changeSet>
<changeSet author="skutz (generated)" id="1591203948991-5">
<addPrimaryKey columnNames="ID, AUTHOR, FILENAME, SEQUENCE" constraintName="SYS_C00122620"
forIndexName="SYS_C00122620" tableName="DATABASECHANGELOG_ACTIONS"/>
</changeSet>
</databaseChangeLog>
3.4.9 LB DBDOC
Syntax
liquibase(lb) dbdoc -changelog CHANGELOG -dir DIR [-context CONTEXT] [-label LABEL]
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
-dir DIR |
Saves all files in the given directory, example, -dir some/sub/directory |
Common Update Parameters
Parameter | Description |
---|---|
-context
CONTEXT |
Specify the changeset contexts to execute |
-label LABEL
|
Filter the changelog using labels |
Example
SQL> lb dbdoc -changelog states_table.xml -dir ./doc
SQL> !ls -l ./doc
total 56
drwxr-xr-x 3 jdoe staff 96 Jun 3 13:25 authors
-rw-r--r-- 1 jdoe staff 449 Jun 3 13:25 authors.html
drwxr-xr-x 3 jdoe staff 96 Jun 3 13:25 changelogs
-rw-r--r-- 1 jdoe staff 474 Jun 3 13:25 changelogs.html
drwxr-xr-x 31 jdoe staff 992 Jun 3 13:25 columns
-rw-r--r-- 1 jdoe staff 555 Jun 3 13:25 currenttables.html
-rw-r--r-- 1 jdoe staff 899 Jun 3 13:25 globalnav.html
-rw-r--r-- 1 jdoe staff 769 Jun 3 13:25 index.html
-rw-r--r-- 1 jdoe staff 445 Jun 3 13:25 overview-summary.html
drwxr-xr-x 4 jdoe staff 128 Jun 3 13:25 pending
drwxr-xr-x 3 jdoe staff 96 Jun 3 13:25 recent
-rw-r--r-- 1 jdoe staff 1202 Jun 3 13:25 stylesheet.css
drwxr-xr-x 4 jdoe staff 128 Jun 3 13:25 tables
using-liquibase-sqlcl.html#GUID-1B4D592C-1E1F-4512-A3F4-5FDB256B5F32__FIG_YPV_KX2_DMB shows the index page that is created by the DBDOC command. It enables you to navigate the various details of the changelog used to produce the document.
3.4.10 LB CHANGELOGSYNC
Syntax
liquibase(lb) changelogsync -changelog CHANGELOG
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
SQL> lb changelogsync -changelog zipcodes_table.xml
-- *********************************************************************
-- SQL to add all changesets to database history table
-- *********************************************************************
-- Change Log: zipcodes_table.xml
-- Ran at: 6/3/20 1:28 PM
-- Against: JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
-- Liquibase version: 3.8.9
-- *********************************************************************
-- Lock Database
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = '192.168.1.175 (192.168.1.175)',
LOCKGRANTED = TO_TIMESTAMP('2020-06-03 13:28:24.686', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE ID = 1 AND LOCKED = 0;
INSERT INTO JDOE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED,
MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID)
VALUES ('e6568d24910039bf3d4241e7edff5ec82612c60f', 'Generated', 'zipcodes_table.xml',
SYSTIMESTAMP, 3, '8:e4fe731b3a40f479dec11e234e4a69ac', 'createSxmlObject objectName=ZIPCODES,
ownerName=JDOE', '', 'EXECUTED', NULL, NULL, '3.8.9', '1205304697');
-- Release Database Lock
UPDATE JDOE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
3.4.11 LB CLEARCHECKSUMS
Syntax
liquibase(lb) clearchecksums -changelog CHANGELOG
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
SQL> lb clearchecksums -changelog zipcodes_table.xml
3.4.12 LB LISTLOCKS
Syntax
liquibase(lb) listlocks -changelog CHANGELOG
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
SQL> lb listlocks -changelog zipcodes_table.xml
No locks currently in use
3.4.13 LB RELEASELOCKS
Releases all locks on the database change log.
Syntax
liquibase(lb) releaselocks -changelog CHANGELOG
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
SQL> lb releaselocks -changelog zipcodes_table.xml
3.4.14 LB STATUS
Syntax
liquibase(lb) status -changelog CHANGELOG
Checks the status of the change log using the current connection. This shows if the change log has been applied and the result of the change log.
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
SQL> lb status -changelog zipcodes_table.xml
1 change sets have not been applied to JDOE@jdbc:oracle:thin:@localhost:1521/ORCLPDB1
zipcodes_table.xml::e6568d24910039bf3d4241e7edff5ec82612c60f::Generated
3.4.15 LB VALIDATE
Syntax
liquibase(lb) validate -changelog CHANGELOG
Named Parameters
Parameter | Description |
---|---|
-changelog CHANGELOG |
Specify the changelog file to use |
Example
lb validate -changelog zipcodes_table.xml
No issues were found in file zipcodes _table.xml, validation passed
3.4.16 LB VERSION
Display product version information.
Syntax
liquibase(lb) version
Example
SQL> lb version
Liquibase version: 3.8.9
Extension Version: 2.0.0.0.0
3.4.17 LB HELP
Syntax
liquibase(lb) help command
Type a command with no options for help on a specific command.
Example
SQL> lb help
usage: lb COMMAND ...
Commands:
The following commands are available within the liquibase feature.
lb help COMMAND for command specific help
COMMAND
genobject Generate change log for a specific database object
genschema Generate changelogs and controller for connected schema
gencontrolfile Remove locks held by changelog
update Updates database to current version
updatesql Generates SQL to update database to current version
rollback Rolls back the state requested
rollbacksql Writes SQL to roll back the database to the state requested
diff Writes description of differences between two databases to standard out.
dbdoc Generates Javadoc-like documentation based on current database and change log.
changelogsync Mark all changes as executed in the database.
clearchecksums Removes current checksums from database.
listlocks Lists who currently has locks on the database changelog.
releaselocks Releases all locks on the database changelog.
status Outputs list of unrun change sets.
validate Checks the changelog for errors.
version Display product version information
3.5 DATABASECHANGELOG_DETAILS VIEW
DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables for easier reporting and troubleshooting.
This view enables a better understanding of the status and work performed by each change applied to the database. The SQL column shows the actual SQL that was run in the database. The SXML column shows the state of the object prior to the change being applied.
Example
SQL> desc DATABASECHANGELOG_DETAILS
Name Null? Type
________________ ___________ ________________
DEPLOYMENT_ID VARCHAR2(10)
ID VARCHAR2(255)
AUTHOR VARCHAR2(255)
FILENAME VARCHAR2(255)
SQL CLOB
SXML CLOB
DATEEXECUTED NOT NULL TIMESTAMP(6)
EXECTYPE NOT NULL VARCHAR2(10)
MD5SUM VARCHAR2(35)
DESCRIPTION VARCHAR2(255)
COMMENTS VARCHAR2(255)
LIQUIBASE VARCHAR2(20)
CONTEXTS VARCHAR2(255)
LABELS VARCHAR2(255)
3.6 ChangeSets in Liquibase
The following table lists the changeSets and provides a description for each of them. To learn more about changeSets, see <changeSet> tag.
ChangeSet | Description |
---|---|
CreateOracleConstraint | Creates a constraint from SQL. |
CreateOracleDirectory | Creates a directory from SQL. |
CreateOracleFunction | Creates a function from SQL. |
CreateOracleGrant | Creates a grant from SQL. |
CreateOraclePackageBody | Creates a package body from SQL. |
CreateOracleJob | Creates a DBMS_Scheduler job from SQL. |
CreateOraclePackageSpec | Creates a package specification from SQL. |
CreateOracleProcedure | Creates a procedure from SQL. |
CreateOraclePublicSynonym | Creates a public synonym from SQL. |
CreateOracleRefConstraint | Creates a referential constraint from SQL. |
CreateOracleSynonym | Creates a synonym from SQL. |
CreateOracleTrigger | Creates a trigger from SQL. |
CreateOracleTypeBody | Creates a type body from SQL. |
CreateOracleTypeSpec | Creates a type spec from SQL. |
CreateSxmlObject | Creates a function from SQL. |
DropOracleConstraint | Drops a constraint. |
DropOracleFunction | Drops a function. |
DropOracleGrant | Drops a grant. |
DropOraclePackageBody | Drops a package body. |
DropOraclePackageSpec | Drops a package specification. |
DropOracleProcedure | Drops a procedure. |
DropOracleRefConstraint | Drops a referential constraint. |
DropOracleTrigger | Drops a trigger. |
DropOracleTypeBody | Drops a type body. |
DropOracleTypeSpec | Drops a type specification. |
DropOracleSynonym | Drops a synonym. |
DropSxmlObject | Drops an SXML object. If the object was created through createSxmlObject, this rolls back the object to the last state. If not created, the object is just dropped. This is primarily used internally for SXML object handling. |
RunOracleScript | Executes a SQL script using the SQLcl engine and therefore supports all SQLcl commands. Supports script, file, and URL sources. See Execute Custom SQL with RunOracleScript |
3.7 Examples Using Liquibase
Some examples of using the Liquibase functionality for database change management tasks:
- Capture and Deploy an Object
- Capture and Deploy a Schema
- Generate the Master Control File
- Capture and Deploy a Schema and then Upgrade it and Redeploy
- Execute Custom SQL with RunOracleScript
See this LiveLabs workshop on change management using SQLcl and Liquibase.
3.7.1 Capture and Deploy an Object
To deploy the EMPLOYEES table from HR to HR2:
Note:
The syntax for the GENOBJECT command has changed. See LB GENOBJECT
3.7.2 Capture and Deploy a Schema
sql.exe hr/hr@pdb1
SQL> lb genschema
[Type - TYPE_SPEC]: 107 ms
[Type - TYPE_BODY]: 80 ms
[Type - SEQUENCE]: 26 ms
[Type - CLUSTER]: 25 ms
[Type - TABLE]: 721 ms
[Type - MATERIALIZED_VIEW_LOG]: 18 ms
[Type - MATERIALIZED_VIEW]: 9 ms
[Type - VIEW]: 81 ms
[Type - REF_CONSTRAINT]: 128 ms
[Type - DIMENSION]: 14 ms
[Type - FUNCTION]: 43 ms
[Type - PROCEDURE]: 47 ms
[Type - PACKAGE_SPEC]: 24 ms
[Type - DB_LINK]: 14 ms
[Type - SYNONYM]: 23 ms
[Type - INDEX]: 102 ms
[Type - TRIGGER]: 25 ms
[Type - PACKAGE_BODY]: 33 ms
[Type - JOB]: 14 ms
[Method loadCaptureTable]: 1534 ms
[Method parseCaptureTableRecords]: 5479 ms
[Method sortCaptureTable]: 37 ms
[Method createExportChangeLogs]: 0 ms
Export Flags Used:
Export Grants false
Export Synonyms false
SQL>
setup the hr2 user --
drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;
SQL> drop user hr2 cascade;
User HR2 dropped.
SQL> create user hr2 identified by hr2;
User HR2 created.
SQL> grant connect,resource, create view to hr2;
Grant succeeded.
SQL> alter user hr2 quota unlimited on users;
User HR2 altered.
SQL> alter user hr2 quota unlimited on sysaux;
User HR2 altered.
SQL> connect hr2/hr2
Connected.
SQL> lb update -changelog controller.xml
ScriptRunner Executed:person_typ_type_spec.xml::32492b89db4b0e2a115caa3e7e6c3d29c11fa1db::Generated
ScriptRunner Executed:emp_person_typ_type_spec.xml::cb314e652bc79126fa0a2b5261720d15728c7e04::Generated
ScriptRunner Executed:people_typ_type_spec.xml::cc5c2298290e3ce2dc1563da82ddd9ea33a768c8::Generated
SQL> tables
TABLES
___________________________
DATABASECHANGELOG
DATABASECHANGELOGLOCK
DATABASECHANGELOG_ACTIONS
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
10 rows selected.
3.7.3 Generate the Master Control File
SQL> lb gencontrolfile
Action successfully completed please review created file controller.xml
SQL> !more controller.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<include file="{filename.xml}"/>
</databaseChangeLog>
3.7.5 Execute Custom SQL with RunOracleScript
#### SCRIPT - STRING EXAMPLE ####
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet id="runScriptString" author="jdoe">
<n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="STRING">
<n0:source><![CDATA[DEFINE table_name = RUNNERSTRING;create table &&table_name (id number);]]></n0:source>
</n0:runOracleScript>
</changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> tables
no rows selected
SQL> lb update -changelog stringscript.xml
ScriptRunner Executed:stringscript.xml::runScriptString::jdoe
SQL> tables
TABLES
____________________________
DATABASECHANGELOGLOCK
DATABASECHANGELOG
DATABASECHANGELOG_ACTIONS
RUNNERSTRING
#### SCRIPT - FILE EXAMPLE ####
filescript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet id="RunScriptFile" author="jdoe">
<n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="FILE">
<n0:source><![CDATA[/Volumes/SSD/work/sqlcl/scriptFILE.sql]]></n0:source>
</n0:runOracleScript>
</changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------
scriptFILE.sql
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE RUNNERFILE (id number)
-------------------------------------------------------------------------------------------------------------------------------------------
SQL> tables
TABLES
____________________________
DATABASECHANGELOGLOCK
DATABASECHANGELOG
DATABASECHANGELOG_ACTIONS
RUNNERSTRING
SQL> lb update -changelog filescript.xml
ScriptRunner Executed:/Volumes/SSD/work/sqlcl/filescript.xml::RunScriptFile::jdoe
SQL> tables
TABLES
____________________________
DATABASECHANGELOGLOCK
DATABASECHANGELOG
DATABASECHANGELOG_ACTIONS
RUNNERSTRING
RUNNERFILE
#### SCRIPT - URL EXAMPLE ####
urlscript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet id="RunScriptUrl" author="jdoe">
<n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="URL">
<n0:source><![CDATA[file:///Volumes/SSD/work/sqlcl/scriptURL.sql]]></n0:source>
</n0:runOracleScript>
</changeSet>
</databaseChangeLog>
----------------------------------------------------------------------------------------------------------------------------------------------------
scriptURL.sql
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE RUNNERURL (id number)
-------------------------------------------------------------------------------------------------------------------------------------------
SQL> tables
TABLES
____________________________
DATABASECHANGELOGLOCK
DATABASECHANGELOG
DATABASECHANGELOG_ACTIONS
RUNNERSTRING
RUNNERFILE
SQL> lb update -changelog urlscript.xml
ScriptRunner Executed:/Volumes/SSD/work/sqlcl/urlscript.xml::RunScriptUrl::jdoe
SQL> tables
TABLES
____________________________
DATABASECHANGELOGLOCK
DATABASECHANGELOG
DATABASECHANGELOG_ACTIONS
RUNNERSTRING
RUNNERFILE
RUNNERURL