Liquibase The filename, directory name, or volume label syntax is incorrect
3.1 About Liquibase in SQLclLiquibase is an open-source database-independent library for tracking, managing and applying database schema changes. Show 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:
3.2 Requirements for Using LiquibaseThe 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.
3.3 Supported TypesDDL 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.
SXML types share the SXML change type.
3.4 Supported Liquibase Commands in SQLclYou can invoke the Liquibase commands in SQLcl with liquibase or lb. 3.4.1 LB GENOBJECTGenerates change logs for APEX, ORDS RESTful Service Modules, or database objects. APEX Syntax lb genobject -type {APEX} -applicationid APPLICATIONID -workspaceid WORKSPACEID [-instance] [-expWorkspace][-expMinimal] [-expFiles] [-skipExportDate] [-expPubReports] [-expSavedReports] [-expIRNotif] [-expTranslations] [-expFeedback] [-expTeamdevdata] [-deploymentSystem DEPLOYMENTSYSTEM] [-expFeedbackSince EXPFEEDBACKSINCE] [-expOriginalIds] [-expNoSubscriptions] [-expComments] [-expSupportingObjects {Y,N,I}] [-expACLAssignments] [-dir DIR] [-list LIST] [-changesSince CHANGESSINCE] [-changesBy CHANGESBY] [-nochecksum] [-split] [-expComponents EXPCOMPONENTS] [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]Generates a change log for APEX objects. Named Parameters
Common Generator Parameters
Example SQL> lb genobject -type apex -applicationid 4900 Exporting Application 4900 Action successfully completed please review created file f4900.xmlORDS 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
Common Generator Parameters
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
Common Generator Parameters
Database Object Types Supported
Example SQL> lb genobject -type table -name zipcodes Action successfully completed please review created file zipcodes_table.xml3.4.2 LB GENSCHEMAGenerates changelogs and controller file for the connected schema. Syntax liquibase(lb) genschema [-noreport] [-synonyms] [-grants] [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]Captures the entire schema that the user is connected to in SQLcl. An XML file is created in the current working directory for each object in the schema, and a controller.xml file is created. The controller file is a change log that includes all files in the proper order to enable the schema to be deployed correctly. Named Parameters
Common Generator Parameters
Example 3.4.3 LB GENCONTROLFILEGenerates a blank controller.xml as a sample. Syntax liquibase(lb) gencontrolfileCreates 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.xml3.4.4 LB UPDATEApplies the specified change log using the current connection. Syntax liquibase(lb) update -changelog CHANGELOG [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]Enables you to apply database changes that you and other developers have added to the change log file. Named Parameters
Common Update Parameters
Example SQL> lb update -changelog zipcodes_table.xml ScriptRunner Executed:zipcodes_table.xml::3b128595dbfbc34086c0a18ff38e090529999::Generated3.4.5 LB UPDATESQLGenerates SQL to update the database to the current version. Syntax liquibase(lb) updatesql -changelog CHANGELOG [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug]Generates and renders to the screen the SQL statements that would be applied for a specific change log. Named Parameters
Common Generator Parameters
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 ROLLBACKRolls back the state requested. Syntax liquibase(lb) rollback -changelog CHANGELOG [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-log] [-debug] (-count COUNT | -date DATE)Rolls back changes starting from the last change applied using the input change log. The count can be higher than the changes in the change log. 999, which is the maximum size, rolls back all the changes. Named Parameters
Rollback Style Options Select one of the following options:
Common Update Parameters
Example lb rollback -changelog zipcodes_tablev2.xml -count 100 ScriptRunner Executed:zipcodes_tablev2.xml::3595dbfbc34086449c0a18ff38e090529999::Generated3.4.7 LB ROLLBACKSQLWrites SQL to roll back the database to the state requested. Syntax liquibase(lb) rollbacksql -changelog CHANGELOG [-context CONTEXT] [-label LABEL] [-emit_schema] [-fail] [-replace] [-runonchange] [-runalways] [-debug] (-count COUNT | -date DATE)Named Parameters
Rollback Style Options Select one of the following options:
Common Update Parameters
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 DIFFWrites a description of differences between two databases to standard output. Syntax 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
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.xml3.4.9 LB DBDOCGenerates Javadoc-like documentation based on the current database and change log. Syntax liquibase(lb) dbdoc -changelog CHANGELOG -dir DIR [-context CONTEXT] [-label LABEL]
Named Parameters
Common Update Parameters
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 tablesFigure 3-1 shows the index page that is created by the DBDOC command. It enables you to navigate the various details of the changelog used to produce the document. 3.4.10 LB CHANGELOGSYNCWrites the SQL statements to mark all changes in the change log as executed in the database to a file. Syntax liquibase(lb) changelogsync -changelog CHANGELOGNamed Parameters
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 CLEARCHECKSUMSRemoves current checksums from the database. For the next update, changesets that have already been deployed will have their checksums recomputed, and changesets that have not been deployed will be deployed. Syntax liquibase(lb) clearchecksums -changelog CHANGELOGNamed Parameters
Example SQL> lb clearchecksums -changelog zipcodes_table.xml3.4.12 LB LISTLOCKSLists who currently has locks on the database change log. Syntax liquibase(lb) listlocks -changelog CHANGELOGNamed Parameters
Example SQL> lb listlocks -changelog zipcodes_table.xml No locks currently in use3.4.13 LB RELEASELOCKSReleases all locks on the database change log. Syntax liquibase(lb) releaselocks -changelog CHANGELOGNamed Parameters
Example SQL> lb releaselocks -changelog zipcodes_table.xml3.4.14 LB STATUSOutputs a list of change sets that have not been applied. Syntax liquibase(lb) status -changelog CHANGELOGChecks 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
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::Generated3.4.15 LB VALIDATEChecks the changelog for errors. Syntax liquibase(lb) validate -changelog CHANGELOGNamed Parameters
Example lb validate -changelog zipcodes_table.xml No issues were found in file zipcodes _table.xml, validation passed3.4.16 LB VERSIONDisplay product version information. Syntax liquibase(lb) versionExample SQL> lb version Liquibase version: 3.8.9 Extension Version: 2.0.0.0.03.4.17 LB HELPLists the available Liquibase commands. Syntax liquibase(lb) help commandType 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 information3.5 DATABASECHANGELOG_DETAILS VIEWDATABASECHANGELOG_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 LiquibaseThe following table lists the
changeSets and provides a description for each of them. To learn more about changeSets, see
3.7 Examples Using Liquibase3.7.1 Capture and Deploy an ObjectTo 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 SchemaTo capture the HR schema and reproduce it in the HR2 schema: sql.exe hr/hr@pdb1 SQL> lb genschema [Type - TYPE_SPEC]: 107 ms [Type - TYPE_BODY]: 80 ms [Type - SEQUENCE]: 26 ms [Type - CLUSTER]: 25 ms [Type - TABLE]: 721 ms [Type - MATERIALIZED_VIEW_LOG]: 18 ms [Type - MATERIALIZED_VIEW]: 9 ms [Type - VIEW]: 81 ms [Type - REF_CONSTRAINT]: 128 ms [Type - DIMENSION]: 14 ms [Type - FUNCTION]: 43 ms [Type - PROCEDURE]: 47 ms [Type - PACKAGE_SPEC]: 24 ms [Type - DB_LINK]: 14 ms [Type - SYNONYM]: 23 ms [Type - INDEX]: 102 ms [Type - TRIGGER]: 25 ms [Type - PACKAGE_BODY]: 33 ms [Type - JOB]: 14 ms [Method loadCaptureTable]: 1534 ms [Method parseCaptureTableRecords]: 5479 ms [Method sortCaptureTable]: 37 ms [Method createExportChangeLogs]: 0 ms Export Flags Used: Export Grants false Export Synonyms false SQL> setup the hr2 user -- drop user hr2 cascade; create user hr2 identified by hr2; grant connect,resource, create view to hr2; alter user hr2 quota unlimited on users; alter user hr2 quota unlimited on sysaux; SQL> drop user hr2 cascade; User HR2 dropped. SQL> create user hr2 identified by hr2; User HR2 created. SQL> grant connect,resource, create view to hr2; Grant succeeded. SQL> alter user hr2 quota unlimited on users; User HR2 altered. SQL> alter user hr2 quota unlimited on sysaux; User HR2 altered. SQL> connect hr2/hr2 Connected. SQL> lb update -changelog controller.xml ScriptRunner Executed:person_typ_type_spec.xml::32492b89db4b0e2a115caa3e7e6c3d29c11fa1db::Generated ScriptRunner Executed:emp_person_typ_type_spec.xml::cb314e652bc79126fa0a2b5261720d15728c7e04::Generated ScriptRunner Executed:people_typ_type_spec.xml::cc5c2298290e3ce2dc1563da82ddd9ea33a768c8::Generated SQL> tables TABLES ___________________________ DATABASECHANGELOG DATABASECHANGELOGLOCK DATABASECHANGELOG_ACTIONS COUNTRIES REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY 10 rows selected.3.7.3 Generate the Master Control FileSQL> lb gencontrolfile
Action successfully completed please review created file controller.xml
SQL> !more controller.xml
3.7.4 Capture and Deploy a Schema and then Upgrade it and RedeployThe steps are:
3.7.5 Execute Custom SQL with RunOracleScriptCreate a RunOracleScript changeset where the source can be a script, a link to a file, or a URL. The changeset in this example shows how to create a table and use PL/SQL variables in the script. #### SCRIPT - STRING EXAMPLE ####
#### SCRIPT - FILE EXAMPLE ####
filescript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
#### SCRIPT - URL EXAMPLE ####
urlscript.xml
----------------------------------------------------------------------------------------------------------------------------------------------------
|