Liquibase（Oracle SQLcl集成版）简明示例

  • 开发
  • 7

本文使用的是Oracle SQLcl中集成的Liquibase，而非开源版Liquibase。

Liquibase的快速入门可以参见Liquibase Core Concepts。需要了解一下概念：

  • Change log：基于文本的更改日志文件按顺序列出对数据库所做的所有更改
  • Change set：变更日志中的单个变更
  • Tracking tables
    • DATABASECHANGELOG ：跟踪已运行的变更集
    • DATABASECHANGELOGLOCK ：确保一次只运行一个 Liquibase 实例
    • DATABASECHANGELOGHISTORY ：记录对数据库所做的所有更改的历史记录

下面是典型的Liquibase工作流程，图来自这里：

在这里插入图片描述

Liquibase的文档参见这里。

下面来看几个例子，使用的Schema是Oracle官方的Sample schema: HR。部署的目标Schema是HR2，位于同一个数据库中。

这几个例子，也可以从帮助中看到：

SQL> help lb examples


Example: Review SQL.
--------------------

  To review SQL before running maintenance commands:
  -- Optionally setup to save sql
     cd <lb-changes-directory>
     spool update.sql
  -- Connect to HR and capture the object.
     connect <db-connect1-string>
     lb update-sql
     spool off

Example: Capture and Deploy an Object.
--------------------------------------

  To deploy the EMPLOYEES table from HR to HR2:
  -- Set default output path.
     cd <output-files-path>

  -- Connect to HR and capture the object.
     connect <db-connect1-string>
     lb generate-object -object-type table -object-name employees

  -- Connect to HR2 and ensure the object does not exist.
     connect <db-connect2-string>
     drop table employees

  -- Create the object in HR2 and verify that it was created.
     lb update -changelog-file employees_table.xml
     desc employees

Example: Capture and Deploy a Schema.
--------------------------------------

  To capture HR schema and reproduce it in HR2 schema:
  -- Set default output path.
     cd <output-files-path>
  -- Connect to HR and capture the schema.
     connect <db-connect1-string>
     lb generate-schema

  -- Setup the HR2 user.
     connect <db-connect-dba-string>
     drop user hr2 cascade;
     create user hr2 identified by hr2;
     grant connect,resource, create view to hr2;
     alter user hr2 quota unlimited on users;
     alter user hr2 quota unlimited on sysaux;

  -- Create the schema objects deployed from HR in HR2 and verify.
     lb update -changelog-file controller.xml
     tables

Example: Execute Custom SQL with RunOracleScript.
-------------------------------------------------

 Create a RunOracleScript changeset to create a table and use PL/SQL variables in the script.
 See Oracle SQLcl User's Guide for examples using files and urls.
#### 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>


See additional examples and details in Oracle SQLcl User's Guide, Examples Using Liquibase.

捕获并部署整个Schema

连接到 HR

SQL> connect hr@orclpdb1
Password? (**********?) ********
Connected.

为整个HR schema生成Change log：

SQL> pwd
/home/oracle/lb/
SQL> lb generate-schema
--Starting Liquibase at 2024-06-14T09:07:40.040925 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)

Export Flags Used:

Export Grants           false
Export Synonyms         false

[Method loadCaptureTable]:
        [Type - TYPE_SPEC]:                        544 ms
        [Type - TYPE_BODY]:                        165 ms
        [Type - SEQUENCE]:                         386 ms
        [Type - DIRECTORY]:                         53 ms
        [Type - CLUSTER]:                         3515 ms
        [Type - TABLE]:                          36869 ms
        [Type - MATERIALIZED_VIEW_LOG]:             58 ms
        [Type - MATERIALIZED_VIEW]:                 40 ms
        [Type - VIEW]:                            2822 ms
        [Type - REF_CONSTRAINT]:                   414 ms
        [Type - DIMENSION]:                         58 ms
        [Type - PACKAGE_SPEC]:                      94 ms
        [Type - FUNCTION]:                          97 ms
        [Type - PROCEDURE]:                        179 ms
        [Type - DB_LINK]:                           48 ms
        [Type - SYNONYM]:                           57 ms
        [Type - INDEX]:                           2494 ms
        [Type - TRIGGER]:                          390 ms
        [Type - PACKAGE_BODY]:                     127 ms
        [Type - JOB]:                               70 ms

[Method loadCaptureTable]:                       48481 ms
[Method sortCaptureTable]:                          21 ms
[Method writeChangeLogs]:                          192 ms
Changelog created and written out to file controller.xml

Operation completed successfully.

可以看到在目录下生成的文件：

[oracle@oracle-19c-vagrant lb]$ ls -1l
total 208
-rw-r--r--. 1 oracle oinstall 1283 Jun 14 09:08 add_job_history_procedure.xml
-rw-r--r--. 1 oracle oinstall 2570 Jun 14 09:08 controller.xml
-rw-r--r--. 1 oracle oinstall 1133 Jun 14 09:08 countries_comment.xml
-rw-r--r--. 1 oracle oinstall 3032 Jun 14 09:08 countries_table.xml
-rw-r--r--. 1 oracle oinstall  899 Jun 14 09:08 countr_reg_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1073 Jun 14 09:08 dbtools$execution_history_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 5047 Jun 14 09:08 dbtools$execution_history_table.xml
-rw-r--r--. 1 oracle oinstall 1634 Jun 14 09:08 departments_comment.xml
-rw-r--r--. 1 oracle oinstall 1023 Jun 14 09:08 departments_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 4203 Jun 14 09:08 departments_table.xml
-rw-r--r--. 1 oracle oinstall 1811 Jun 14 09:08 dept_location_ix_index.xml
-rw-r--r--. 1 oracle oinstall  903 Jun 14 09:08 dept_loc_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall  902 Jun 14 09:08 dept_mgr_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1813 Jun 14 09:08 emp_department_ix_index.xml
-rw-r--r--. 1 oracle oinstall  907 Jun 14 09:08 emp_dept_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 2818 Jun 14 09:08 emp_details_view_view.xml
-rw-r--r--. 1 oracle oinstall 1816 Jun 14 09:08 emp_email_uk_index.xml
-rw-r--r--. 1 oracle oinstall  884 Jun 14 09:08 emp_job_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1792 Jun 14 09:08 emp_job_ix_index.xml
-rw-r--r--. 1 oracle oinstall 2327 Jun 14 09:08 employees_comment.xml
-rw-r--r--. 1 oracle oinstall 1042 Jun 14 09:08 employees_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 7318 Jun 14 09:08 employees_table.xml
-rw-r--r--. 1 oracle oinstall  906 Jun 14 09:08 emp_manager_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1804 Jun 14 09:08 emp_manager_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1884 Jun 14 09:08 emp_name_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1819 Jun 14 09:08 jhist_department_ix_index.xml
-rw-r--r--. 1 oracle oinstall  913 Jun 14 09:08 jhist_dept_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall  905 Jun 14 09:08 jhist_emp_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1813 Jun 14 09:08 jhist_employee_ix_index.xml
-rw-r--r--. 1 oracle oinstall  890 Jun 14 09:08 jhist_job_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1798 Jun 14 09:08 jhist_job_ix_index.xml
-rw-r--r--. 1 oracle oinstall 2099 Jun 14 09:08 job_history_comment.xml
-rw-r--r--. 1 oracle oinstall 4886 Jun 14 09:08 job_history_table.xml
-rw-r--r--. 1 oracle oinstall 1222 Jun 14 09:08 jobs_comment.xml
-rw-r--r--. 1 oracle oinstall 4191 Jun 14 09:08 jobs_table.xml
-rw-r--r--. 1 oracle oinstall 1903 Jun 14 09:08 locations_comment.xml
-rw-r--r--. 1 oracle oinstall 1021 Jun 14 09:08 locations_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 4667 Jun 14 09:08 locations_table.xml
-rw-r--r--. 1 oracle oinstall  899 Jun 14 09:08 loc_c_id_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1792 Jun 14 09:08 loc_city_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1804 Jun 14 09:08 loc_country_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1822 Jun 14 09:08 loc_state_province_ix_index.xml
-rw-r--r--. 1 oracle oinstall 3729 Jun 14 09:08 regions_table.xml
-rw-r--r--. 1 oracle oinstall 1050 Jun 14 09:08 secure_dml_procedure.xml
-rw-r--r--. 1 oracle oinstall  894 Jun 14 09:08 secure_employees_trigger.xml
-rw-r--r--. 1 oracle oinstall  999 Jun 14 09:08 update_job_history_trigger.xml

控制文件controller.xml，即Change log：

[oracle@oracle-19c-vagrant lb]$ cat 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"
        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-latest.xsd">
  <include file="dbtools$execution_history_seq_sequence.xml"/>
  <include file="departments_seq_sequence.xml"/>
  <include file="employees_seq_sequence.xml"/>
  <include file="locations_seq_sequence.xml"/>
  <include file="data_file_dir_directory.xml"/>
  <include file="log_file_dir_directory.xml"/>
  <include file="media_dir_directory.xml"/>
  <include file="ss_oe_xmldir_directory.xml"/>
  <include file="subdir_directory.xml"/>
  <include file="countries_table.xml"/>
  <include file="departments_table.xml"/>
  <include file="employees_table.xml"/>
  <include file="jobs_table.xml"/>
  <include file="job_history_table.xml"/>
  <include file="locations_table.xml"/>
  <include file="regions_table.xml"/>
  <include file="emp_details_view_view.xml"/>
  <include file="countr_reg_fk_ref_constraint.xml"/>
  <include file="dept_loc_fk_ref_constraint.xml"/>
  <include file="dept_mgr_fk_ref_constraint.xml"/>
  <include file="emp_dept_fk_ref_constraint.xml"/>
  <include file="emp_job_fk_ref_constraint.xml"/>
  <include file="emp_manager_fk_ref_constraint.xml"/>
  <include file="jhist_dept_fk_ref_constraint.xml"/>
  <include file="jhist_emp_fk_ref_constraint.xml"/>
  <include file="jhist_job_fk_ref_constraint.xml"/>
  <include file="loc_c_id_fk_ref_constraint.xml"/>
  <include file="add_job_history_procedure.xml"/>
  <include file="secure_dml_procedure.xml"/>
  <include file="dept_location_ix_index.xml"/>
  <include file="emp_department_ix_index.xml"/>
  <include file="emp_email_uk_index.xml"/>
  <include file="emp_job_ix_index.xml"/>
  <include file="emp_manager_ix_index.xml"/>
  <include file="emp_name_ix_index.xml"/>
  <include file="jhist_department_ix_index.xml"/>
  <include file="jhist_employee_ix_index.xml"/>
  <include file="jhist_job_ix_index.xml"/>
  <include file="loc_city_ix_index.xml"/>
  <include file="loc_country_ix_index.xml"/>
  <include file="loc_state_province_ix_index.xml"/>
  <include file="secure_employees_trigger.xml"/>
  <include file="update_job_history_trigger.xml"/>
  <include file="countries_comment.xml"/>
  <include file="departments_comment.xml"/>
  <include file="employees_comment.xml"/>
  <include file="jobs_comment.xml"/>
  <include file="job_history_comment.xml"/>
  <include file="locations_comment.xml"/>
</databaseChangeLog>

创建另一个用户hr2：

connect sys@orclpdb1 as sysdba
drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
grant create any directory, drop any directory to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;

以hr2登录，然后部署Change log：

SQL> connect hr2@orclpdb1
Connected.
SQL> pwd
/home/oracle/lb/
SQL> show user
USER is "HR2"
SQL> lb update -changelog-file controller.xml
--Starting Liquibase at 2024-06-14T09:11:41.583359 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: dbtools$execution_history_seq_sequence.xml::814db839d56f18388eba43012512b4c29af368d4::(HR)-Generated
Sequence "DBTOOLS$EXECUTION_HISTORY_SEQ" created.
Running Changeset: departments_seq_sequence.xml::c2559d5eb079f42af8fc57b00c9812e6c8c2d2e0::(HR)-Generated
Sequence "DEPARTMENTS_SEQ" created.
Running Changeset: employees_seq_sequence.xml::3a376736162b5480bc023782459b2938821546fe::(HR)-Generated
Sequence "EMPLOYEES_SEQ" created.
Running Changeset: locations_seq_sequence.xml::d42e90d6488ef61a66ab85db1f851adae60bc8dd::(HR)-Generated
Sequence "LOCATIONS_SEQ" created.
Running Changeset: countries_table.xml::3b240552bee3c11f1818587a788cda621b6518ab::(HR)-Generated
Table "COUNTRIES" created.
Running Changeset: dbtools$execution_history_table.xml::e66e9a73fcd5013144987ac69ba38f689227e5ae::(HR)-Generated
Table "DBTOOLS$EXECUTION_HISTORY" created.
Running Changeset: departments_table.xml::93dac2aceee230b182ade406fcb7b69ca9643a30::(HR)-Generated
Table "DEPARTMENTS" created.
Running Changeset: employees_table.xml::19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b::(HR)-Generated
Table "EMPLOYEES" created.
Running Changeset: jobs_table.xml::2b683f16417957d9c74e454cbb547b905a5718d8::(HR)-Generated
Table "JOBS" created.
Running Changeset: job_history_table.xml::841721d5dd15086e36463a952e618e3bb9403700::(HR)-Generated
Table "JOB_HISTORY" created.
Running Changeset: locations_table.xml::d947b88fe783235b92608b81d588d2a7202b90b8::(HR)-Generated
Table "LOCATIONS" created.
Running Changeset: regions_table.xml::6544c454a28fe6c03a8cde376a08b590875e0bd1::(HR)-Generated
Table "REGIONS" created.
Running Changeset: emp_details_view_view.xml::c233b8bd8e55fbce0db20919556c600ba65e5849::(HR)-Generated
View "EMP_DETAILS_VIEW" created.
Running Changeset: countr_reg_fk_ref_constraint.xml::c0048786c0431718640f15fcc3b3bb092ed5b93e::(HR)-Generated
Table "COUNTRIES" altered.
Running Changeset: dept_loc_fk_ref_constraint.xml::c42fcba72f585a4bb59168d9165c99c2e2924dd8::(HR)-Generated
Table "DEPARTMENTS" altered.
Running Changeset: dept_mgr_fk_ref_constraint.xml::cf00968893cf4776ad3dbd1768c61a91b0745caa::(HR)-Generated
Table "DEPARTMENTS" altered.
Running Changeset: emp_dept_fk_ref_constraint.xml::51b234beb2e02aaa07df633a6cd7a0b635e6759d::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: emp_job_fk_ref_constraint.xml::f1b1bf677cf990caa0147f863ea18486cdf23815::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: emp_manager_fk_ref_constraint.xml::814e7d7ac4b4c64539619ab0cd3831258cf332e3::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: jhist_dept_fk_ref_constraint.xml::b3caf52e71d5935a8ef7582648f411545153719f::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: jhist_emp_fk_ref_constraint.xml::03b746867c321c670ead34253ecf27589a19e61a::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: jhist_job_fk_ref_constraint.xml::326c90769136eea29c73d734b3885efba66454d3::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: loc_c_id_fk_ref_constraint.xml::d1f8fa1c0b75582a1608d1da0fcea148c6586cc8::(HR)-Generated
Table "LOCATIONS" altered.
Running Changeset: add_job_history_procedure.xml::9093001d3dfcc15939a63e7a92538c6a6639d13e::(HR)-Generated
Procedure ADD_JOB_HISTORY compiled
Running Changeset: secure_dml_procedure.xml::8ae0ea62a872b93d0519fba9556af9d749de6aaf::(HR)-Generated
Procedure SECURE_DML compiled
Running Changeset: dept_location_ix_index.xml::e7c4b4679b0d80ff3e6605a7515b290c50667b9e::(HR)-Generated
Index "DEPT_LOCATION_IX" created.
Running Changeset: emp_department_ix_index.xml::573196c006c4e9499cff9445eddeb54cc7485dde::(HR)-Generated
Index "EMP_DEPARTMENT_IX" created.
Running Changeset: emp_email_uk_index.xml::d25fa5625174785655c8598dc36f2e853794e29e::(HR)-Generated
Statement executed successfully
Running Changeset: emp_job_ix_index.xml::cc00bac7181ed6105f30cb576ecb9f393929cbec::(HR)-Generated
Index "EMP_JOB_IX" created.
Running Changeset: emp_manager_ix_index.xml::ffdb74dd53e9811e7041a46a2aae8b181846b93f::(HR)-Generated
Index "EMP_MANAGER_IX" created.
Running Changeset: emp_name_ix_index.xml::05522ad046b07feb94a87c9e163bec1ebf169d34::(HR)-Generated
Index "EMP_NAME_IX" created.
Running Changeset: jhist_department_ix_index.xml::9017439312d03d5aea5fbb80b3a92065a42691e2::(HR)-Generated
Index "JHIST_DEPARTMENT_IX" created.
Running Changeset: jhist_employee_ix_index.xml::61ab9d9ae857d008a542d7115eee314347b5bc9c::(HR)-Generated
Index "JHIST_EMPLOYEE_IX" created.
Running Changeset: jhist_job_ix_index.xml::4d619ba4fc8851943d45d0ca45bc70d366d066eb::(HR)-Generated
Index "JHIST_JOB_IX" created.
Running Changeset: loc_city_ix_index.xml::f3bc4d402cbc571efce7d3bdbe3c349f3b196abc::(HR)-Generated
Index "LOC_CITY_IX" created.
Running Changeset: loc_country_ix_index.xml::155bbc5ff67237cbb1df9a7573b336a1199ab5ea::(HR)-Generated
Index "LOC_COUNTRY_IX" created.
Running Changeset: loc_state_province_ix_index.xml::8920e05d5b78961426f6f34ed459c15ea1c03245::(HR)-Generated
Index "LOC_STATE_PROVINCE_IX" created.
Running Changeset: secure_employees_trigger.xml::57907483a823d299399df6f738ce7d16cc68a4a8::(HR)-Generated
Trigger SECURE_EMPLOYEES compiled
Running Changeset: update_job_history_trigger.xml::cf43ba994dc6cafc54d74db26a89250d9e0ae9bb::(HR)-Generated
Trigger UPDATE_JOB_HISTORY compiled
Running Changeset: countries_comment.xml::6f4ef85df7284635afaec34851709f369cd1c9c5::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.
Running Changeset: departments_comment.xml::c6d474a20a01dd0163a25528a30e8eb4ccce33dc::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.


Comment created.
Running Changeset: employees_comment.xml::c1d0d2d54cec45336ffa66cda58f117411d49b93::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.
Running Changeset: jobs_comment.xml::2e08c5b3c0348b1bd28f22742f09a1d8bc6c496e::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.


Comment created.
Running Changeset: job_history_comment.xml::24e849b5ecbe23f580568579ba5c85a5ca5f305e::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.
Running Changeset: locations_comment.xml::10610e892c98d4f6d3484894f38587ab22684503::(HR)-Generated
Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.

UPDATE SUMMARY
Run:                         45
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:           45

Liquibase: Update has been successful. Rows affected: 45


Operation completed successfully.

然后在hr2中就可以看到表和其他对象了：

SQL> tables

TABLES
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
COUNTRIES
DBTOOLS$EXECUTION_HISTORY
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

11 rows selected.

以DATABASECHANGELOG开头的表是Liquidbase建立的内部表。

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)

还有很重要的一点，Liquibase只跟踪schema变化，而非数据变化：

SQL> select count(*) from employees;

   COUNT(*)
___________
          0

捕获并部署单个对象

捕获的工作在上一个例子中已经做过了，例如employees表：

$ cat employees_table.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-latest.xsd">
        <changeSet id="19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b" author="(HR)-Generated" failOnError="false"   runOnChange="false" runAlways="false"  >
                <n0:createSxmlObject objectName="EMPLOYEES" objectType="TABLE" ownerName="HR"  replaceIfExists="false" >
                        <n0:source><![CDATA[
  <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <SCHEMA>%USER_NAME%</SCHEMA>
   <NAME>EMPLOYEES</NAME>
   <RELATIONAL_TABLE>
      <COL_LIST>
         <COL_LIST_ITEM>
            <NAME>EMPLOYEE_ID</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>6</PRECISION>
            <SCALE>0</SCALE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>FIRST_NAME</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH>20</LENGTH>
            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>
         </COL_LIST_ITEM>
...

先回退之前所做的整个schema的部署：

SQL> lb rollback-count -changelog-file controller.xml -count 10000
--Starting Liquibase at 2024-06-14T09:27:50.803289 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Rolling Back Changeset: locations_comment.xml::10610e892c98d4f6d3484894f38587ab22684503::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: job_history_comment.xml::24e849b5ecbe23f580568579ba5c85a5ca5f305e::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: jobs_comment.xml::2e08c5b3c0348b1bd28f22742f09a1d8bc6c496e::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: employees_comment.xml::c1d0d2d54cec45336ffa66cda58f117411d49b93::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: departments_comment.xml::c6d474a20a01dd0163a25528a30e8eb4ccce33dc::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: countries_comment.xml::6f4ef85df7284635afaec34851709f369cd1c9c5::(HR)-Generated
Comment dropped.


Comment dropped.


Comment dropped.


Comment dropped.
Rolling Back Changeset: update_job_history_trigger.xml::cf43ba994dc6cafc54d74db26a89250d9e0ae9bb::(HR)-Generated
Trigger "UPDATE_JOB_HISTORY" dropped.
Rolling Back Changeset: secure_employees_trigger.xml::57907483a823d299399df6f738ce7d16cc68a4a8::(HR)-Generated
Trigger "SECURE_EMPLOYEES" dropped.
Rolling Back Changeset: loc_state_province_ix_index.xml::8920e05d5b78961426f6f34ed459c15ea1c03245::(HR)-Generated
Index "LOC_STATE_PROVINCE_IX" dropped.
Rolling Back Changeset: loc_country_ix_index.xml::155bbc5ff67237cbb1df9a7573b336a1199ab5ea::(HR)-Generated
Index "LOC_COUNTRY_IX" dropped.
Rolling Back Changeset: loc_city_ix_index.xml::f3bc4d402cbc571efce7d3bdbe3c349f3b196abc::(HR)-Generated
Index "LOC_CITY_IX" dropped.
Rolling Back Changeset: jhist_job_ix_index.xml::4d619ba4fc8851943d45d0ca45bc70d366d066eb::(HR)-Generated
Index "JHIST_JOB_IX" dropped.
Rolling Back Changeset: jhist_employee_ix_index.xml::61ab9d9ae857d008a542d7115eee314347b5bc9c::(HR)-Generated
Index "JHIST_EMPLOYEE_IX" dropped.
Rolling Back Changeset: jhist_department_ix_index.xml::9017439312d03d5aea5fbb80b3a92065a42691e2::(HR)-Generated
Index "JHIST_DEPARTMENT_IX" dropped.
Rolling Back Changeset: emp_name_ix_index.xml::05522ad046b07feb94a87c9e163bec1ebf169d34::(HR)-Generated
Index "EMP_NAME_IX" dropped.
Rolling Back Changeset: emp_manager_ix_index.xml::ffdb74dd53e9811e7041a46a2aae8b181846b93f::(HR)-Generated
Index "EMP_MANAGER_IX" dropped.
Rolling Back Changeset: emp_job_ix_index.xml::cc00bac7181ed6105f30cb576ecb9f393929cbec::(HR)-Generated
Index "EMP_JOB_IX" dropped.
Rolling Back Changeset: emp_email_uk_index.xml::d25fa5625174785655c8598dc36f2e853794e29e::(HR)-Generated
Statement executed successfully
Rolling Back Changeset: emp_department_ix_index.xml::573196c006c4e9499cff9445eddeb54cc7485dde::(HR)-Generated
Index "EMP_DEPARTMENT_IX" dropped.
Rolling Back Changeset: dept_location_ix_index.xml::e7c4b4679b0d80ff3e6605a7515b290c50667b9e::(HR)-Generated
Index "DEPT_LOCATION_IX" dropped.
Rolling Back Changeset: secure_dml_procedure.xml::8ae0ea62a872b93d0519fba9556af9d749de6aaf::(HR)-Generated
Procedure SECURE_DML compiled
Rolling Back Changeset: add_job_history_procedure.xml::9093001d3dfcc15939a63e7a92538c6a6639d13e::(HR)-Generated
Procedure ADD_JOB_HISTORY compiled
Rolling Back Changeset: loc_c_id_fk_ref_constraint.xml::d1f8fa1c0b75582a1608d1da0fcea148c6586cc8::(HR)-Generated
Table "LOCATIONS" altered.
Rolling Back Changeset: jhist_job_fk_ref_constraint.xml::326c90769136eea29c73d734b3885efba66454d3::(HR)-Generated
Table "JOB_HISTORY" altered.
Rolling Back Changeset: jhist_emp_fk_ref_constraint.xml::03b746867c321c670ead34253ecf27589a19e61a::(HR)-Generated
Table "JOB_HISTORY" altered.
Rolling Back Changeset: jhist_dept_fk_ref_constraint.xml::b3caf52e71d5935a8ef7582648f411545153719f::(HR)-Generated
Table "JOB_HISTORY" altered.
Rolling Back Changeset: emp_manager_fk_ref_constraint.xml::814e7d7ac4b4c64539619ab0cd3831258cf332e3::(HR)-Generated
Table "EMPLOYEES" altered.
Rolling Back Changeset: emp_job_fk_ref_constraint.xml::f1b1bf677cf990caa0147f863ea18486cdf23815::(HR)-Generated
Table "EMPLOYEES" altered.
Rolling Back Changeset: emp_dept_fk_ref_constraint.xml::51b234beb2e02aaa07df633a6cd7a0b635e6759d::(HR)-Generated
Table "EMPLOYEES" altered.
Rolling Back Changeset: dept_mgr_fk_ref_constraint.xml::cf00968893cf4776ad3dbd1768c61a91b0745caa::(HR)-Generated
Table "DEPARTMENTS" altered.
Rolling Back Changeset: dept_loc_fk_ref_constraint.xml::c42fcba72f585a4bb59168d9165c99c2e2924dd8::(HR)-Generated
Table "DEPARTMENTS" altered.
Rolling Back Changeset: countr_reg_fk_ref_constraint.xml::c0048786c0431718640f15fcc3b3bb092ed5b93e::(HR)-Generated
Table "COUNTRIES" altered.
Rolling Back Changeset: emp_details_view_view.xml::c233b8bd8e55fbce0db20919556c600ba65e5849::(HR)-Generated
View "EMP_DETAILS_VIEW" dropped.
Rolling Back Changeset: regions_table.xml::6544c454a28fe6c03a8cde376a08b590875e0bd1::(HR)-Generated
Table "REGIONS" dropped.
Rolling Back Changeset: locations_table.xml::d947b88fe783235b92608b81d588d2a7202b90b8::(HR)-Generated
Table "LOCATIONS" dropped.
Rolling Back Changeset: job_history_table.xml::841721d5dd15086e36463a952e618e3bb9403700::(HR)-Generated
Table "JOB_HISTORY" dropped.
Rolling Back Changeset: jobs_table.xml::2b683f16417957d9c74e454cbb547b905a5718d8::(HR)-Generated
Table "JOBS" dropped.
Rolling Back Changeset: employees_table.xml::19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b::(HR)-Generated
Table "EMPLOYEES" dropped.
Rolling Back Changeset: departments_table.xml::93dac2aceee230b182ade406fcb7b69ca9643a30::(HR)-Generated
Table "DEPARTMENTS" dropped.
Rolling Back Changeset: dbtools$execution_history_table.xml::e66e9a73fcd5013144987ac69ba38f689227e5ae::(HR)-Generated
Table "DBTOOLS$EXECUTION_HISTORY" dropped.
Rolling Back Changeset: countries_table.xml::3b240552bee3c11f1818587a788cda621b6518ab::(HR)-Generated
Table "COUNTRIES" dropped.
Rolling Back Changeset: locations_seq_sequence.xml::d42e90d6488ef61a66ab85db1f851adae60bc8dd::(HR)-Generated
Sequence "LOCATIONS_SEQ" dropped.
Rolling Back Changeset: employees_seq_sequence.xml::3a376736162b5480bc023782459b2938821546fe::(HR)-Generated
Sequence "EMPLOYEES_SEQ" dropped.
Rolling Back Changeset: departments_seq_sequence.xml::c2559d5eb079f42af8fc57b00c9812e6c8c2d2e0::(HR)-Generated
Sequence "DEPARTMENTS_SEQ" dropped.
Rolling Back Changeset: dbtools$execution_history_seq_sequence.xml::814db839d56f18388eba43012512b4c29af368d4::(HR)-Generated
Sequence "DBTOOLS$EXECUTION_HISTORY_SEQ" dropped.


Operation completed successfully.

现在所有用户表都没了：

SQL> tables

TABLES
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK

单独部署一个表，成功了：

SQL> lb update -changelog-file employees_table.xml
--Starting Liquibase at 2024-06-14T09:29:47.664045 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: employees_table.xml::19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b::(HR)-Generated
Table "EMPLOYEES" created.

UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1

Liquibase: Update has been successful. Rows affected: 1


Operation completed successfully.

SQL> tables

TABLES
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
EMPLOYEES

有个遗留问题，若不慎删除了employees表，如何把他补上：
以hr2用户登录，先删除employees表：

SQL> drop table employees purge;

Error starting at line : 1 in command -
drop table employees purge
Error report -
ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"
*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.
*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

SQL> drop table employees cascade constraints;

Table EMPLOYEES dropped.
阅读全部

相关推荐

  2. springboot 集成 @Cacheable简单示例

    2024-06-18 10:26:01       37 阅读

  3. Spring Boot集成Redis简单示例

    2024-06-18 10:26:01       35 阅读

  4. 记录分享Spring Boot集成MQTT（配有简单实现示例）

    2024-06-18 10:26:01       36 阅读

  5. Oracle Flashback示例集锦

    2024-06-18 10:26:01       34 阅读

  6. RabbitMQ与Spring Boot集成示例

    2024-06-18 10:26:01       28 阅读

  7. Kafka 简单生产者示例

    2024-06-18 10:26:01       17 阅读

最近更新

  4. TCP协议是安全的吗?

    2024-06-18 10:26:01       16 阅读

  12. 阿里云服务器执行yum，一直下载docker-ce-stable失败

    2024-06-18 10:26:01       16 阅读

  13. 【Python教程】压缩PDF文件大小

    2024-06-18 10:26:01       15 阅读

  18. 通过文章id递归查询所有评论（xml）

    2024-06-18 10:26:01       18 阅读

热门阅读

  1. 请用树莓派自带的USB-GPIO模块制作一个 temperature作品

    2024-06-18 10:26:01       8 阅读

  2. PHP框架有哪些，以及具体对比优缺点

    2024-06-18 10:26:01       6 阅读

  5. Python武器库开发-武器库篇之Redis未授权漏洞一键Getshell(五十八)

    2024-06-18 10:26:01       7 阅读

  11. 汉中药材元宇宙：古老医学与现代科技的融合之旅

    2024-06-18 10:26:01       9 阅读

  16. 数据仓库之Kappa架构

    2024-06-18 10:26:01       10 阅读

  20. iommu深度剖析虚拟化技术的隐形守护者

    2024-06-18 10:26:01       8 阅读

  25. Unity3D MMORPG加载背包配置表详解

    2024-06-18 10:26:01       7 阅读

  26. 单元的测试

    2024-06-18 10:26:01       6 阅读

  27. SQL server 内连接 左连接 右连接 全连接 语句

    2024-06-18 10:26:01       7 阅读

  28. 静态成员...

    2024-06-18 10:26:01       7 阅读