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

本文使用的是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.

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/709789.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

BFD（简单配置实验）

BFD（简单配置实验）

实验拓扑 配置接口IP地址 正常互通 配置静态BFD 查看状态&#xff1a;为UP 与静态路由联动 查看静态路由状态为active 将交换机的接口down掉 BFD的状态为down 再次查看静态路由的状态为Inactive
阅读更多...
C++ | Leetcode C++题解之第151题反转字符串中的单词

C++ | Leetcode C++题解之第151题反转字符串中的单词

题目&#xff1a; 题解&#xff1a; class Solution { public:string reverseWords(string s) {int left 0, right s.size() - 1;// 去掉字符串开头的空白字符while (left < right && s[left] ) left;// 去掉字符串末尾的空白字符while (left < right &…
阅读更多...
中国首台！紧随美国，重磅发布100比特中性原子量子计算机

中国首台！紧随美国，重磅发布100比特中性原子量子计算机

2024年6月11日上午&#xff0c;“武汉量子论坛—2024”隆重开幕&#xff0c;国家自然科学基金委员会主任窦贤康院士&#xff0c;武汉大学校长张平文院士&#xff0c;以及叶朝辉、徐红星、祝世宁等院士出席大会。在会议上&#xff0c;中科酷原重磅发布国内首台原子量子计算机——…
阅读更多...
安川机器人MA1440减速机维修方法

安川机器人MA1440减速机维修方法

一、安川机械臂减速器维修方法 1. 齿轮磨损维修 对于轻微磨损的齿轮&#xff0c;可以通过重新调整啮合间隙来恢复性能。对于严重磨损的齿轮&#xff0c;需要更换新安川MA1440机械手齿轮箱齿轮。 2. 轴承损坏维修 对于损坏的轴承&#xff0c;需要更换新的轴承。在更换过程中&…
阅读更多...
Dev C++ 安装及使用方法教程-干活多超详细

Dev C++ 安装及使用方法教程-干活多超详细

Dev C 是一款非常好用&#xff0c;简约的C/C开发工具。可以减少很多创建工程的繁琐步骤&#xff0c;很快的进行开发。对于只用于来写代码的人来说&#xff0c;是比较轻量以及极速的。 Dev C 是一个windows下的c和c程序的集成开发环境。它使用mingw32/gcc编译器&#xff0c;遵循…
阅读更多...
计算机网络(8) Finite State Machines（有限状态机）

计算机网络(8) Finite State Machines（有限状态机）

一.建立连接&#xff08;三次握手&#xff09; 建立连接过程中的状态转换如下&#xff1a; 客户端&#xff1a; 发送SYN CLOSED >>>>>>>>>>>>>>SYN SENT(第一次握手) 接收SYNACK发送ACK …
阅读更多...
“论面向对象的建模及应用”必过范文，突击2024软考高项论文

“论面向对象的建模及应用”必过范文，突击2024软考高项论文

论文真题 软件系统建模是软件开发中的重要环节&#xff0c;通过构建软件系统模型可以帮助系统开发人员理解系统&#xff0c;抽取业务过程和管理系统的复杂性&#xff0c;也可以方便各类人员之间的交流。软件系统建模是在系统需求分析和系统实现之间架起的一座桥梁&#xff0c;…
阅读更多...
android studio 自定义类注释模版

android studio 自定义类注释模版

perferences>File and Code Templates>Class 填写&#xff1a; /*** ClassName: ${ClassName}* Description: ${Description}* Author: ${Author}* CreateDate: ${CreateDate}* UpdateUser: ${UpdateUser}* UpdateDate: ${UpdateDate}* UpdateRemark: ${UpdateRemark}* …
阅读更多...
重生之 SpringBoot3 入门保姆级学习（19、场景整合 CentOS7 Docker 的安装）

重生之 SpringBoot3 入门保姆级学习（19、场景整合 CentOS7 Docker 的安装）

重生之 SpringBoot3 入门保姆级学习&#xff08;19、场景整合 CentOS7 Docker 的安装&#xff09; 6、场景整合6.1 Docker 6、场景整合 6.1 Docker 官网 https://docs.docker.com/查看自己的 CentOS配置 cat /etc/os-releaseStep 1: 安装必要的一些系统工具 sudo yum insta…
阅读更多...
MySQL损坏，使用data恢复数据

MySQL损坏，使用data恢复数据

MySQL损坏&#xff0c;重装MySQL使用data文件恢复数据库 1.清空相关注册表(清空安装残留)2.下载合适MySQL版本(与损坏数据库版本相同)3.数据恢复4.Windows server MySQL备份bat5.设置Windows定时执行 # 初始化安装 mysqld -install# 查看数据初始化密码 mysqld --initialize --…
阅读更多...
Nginx 版本升级方案

Nginx 版本升级方案

因 nginx发现漏洞、需 Nginx 的版本进行更新&#xff0c;需要用到Nginx服务器提供的平滑升级功能。 一、Nginx安装 Linux服务器 离线安装 nginx_linux 离线安装nginx 依赖包 百度云-CSDN博客 二、查看已安装的 Nginx 版本信息&#xff0c;configure 配置信息 ## nginx 目录 /…
阅读更多...
C#——结构体详情

C#——结构体详情

结构体 结构体也被称为结构类型&#xff08;“structure type”或“struct type”&#xff09;&#xff0c;它是一种可封装数据和相关功能的值类型&#xff0c;在语法上结构体与类&#xff08;class&#xff09;非常相似&#xff0c;它们都可以用来封装数据&#xff0c;并且都…
阅读更多...
泛微开发修炼之旅--15后端开发连接外部数据源，实现在ecology系统中查询其他异构系统数据库得示例和源码

泛微开发修炼之旅--15后端开发连接外部数据源，实现在ecology系统中查询其他异构系统数据库得示例和源码

文章链接&#xff1a;15后端开发连接外部数据源&#xff0c;实现在ecology系统中查询其他异构系统数据库得示例和源码
阅读更多...
Stable Diffusion 3 Medium 模型

Stable Diffusion 3 Medium 模型

开源SD3&#xff0c;中型版本&#xff0c;20亿参数&#xff0c;Stable Diffusion 3 Medium&#xff0c;系统内存要求32G&#xff0c;显卡6G。 a female character with long, flowing hair that appears to be made of ethereal, swirling patterns resembling the Northern Li…
阅读更多...
使用超声波麦克风阵列预测数控机床刀具磨损

使用超声波麦克风阵列预测数控机床刀具磨损

预测性维护是使用传感器数据来推断机器状态&#xff0c;并从这些传感器数据中检测出在故障发生之前存在的缺陷或故障的过程。预测性维护在所有工业领域都是一种日益增长的趋势&#xff0c;包括轴承故障检测、齿轮磨损检测或往复式机器中的活塞磨损等许多其他例子。在预测性维护…
阅读更多...
相亲交友APP系统|婚恋交友社交软件|语音聊天平台定制开发

相亲交友APP系统|婚恋交友社交软件|语音聊天平台定制开发

在现代社会&#xff0c;婚恋交友已经成为了人们日常生活中的一项重要任务。为了方便用户进行相亲交友活动&#xff0c;各种相亲交友APP系统和婚恋交友社交软件应运而生。本文将介绍相亲交友APP系统、婚恋交友社交软件的开发以及语音聊天平台的定制开发的相关知识和指导。 一、…
阅读更多...
LeetCode | 168.Excel表列名称

LeetCode | 168.Excel表列名称

这道题一开始以为是简单的进制转换问题&#xff0c;用的以往的思路&#xff0c;对于一般性的进制转换题目&#xff0c;只需要不断地对 columnNumber 进行 % 运算取得最后一位&#xff0c;然后对 columnNumber 进行 / 运算&#xff0c;将已经取得的位数去掉&#xff0c;直到 col…
阅读更多...
命令模式：灯开灯关

命令模式：灯开灯关

文章目录 UML类图Command接口Invoker.javaLight.javaOnLightCommand.javaTest.java运行结果位置 UML类图 Command接口 这个你会&#xff0c;只有一个方法&#xff0c;并且接口里面是抽象方法 package mlms; /*- 这个你会&#xff0c;只有一个方法&#xff0c;并且接口里面是抽…
阅读更多...
2024.6.14 刷题总结

2024.6.14 刷题总结

2024.6.14 **每日一题** 2786.访问数组中的位置使分数最大&#xff0c;看到这题就想到动态规划的思路&#xff0c;遍历数组&#xff0c;每次选择移动该元素时能获得到的最大值&#xff0c;分别考虑最后一个的元素为奇数/偶数的最大值&#xff0c;用长度为2的数组来储存这两个值…
阅读更多...
一文读懂数字化转型三部曲：信息化-数字化-数智化

一文读懂数字化转型三部曲：信息化-数字化-数智化

言简意赅&#xff0c;数字化就是把物理实体、业务流程和信息数据转换为数字形式&#xff0c;比如原本公司的账都记在纸质账本上&#xff0c;堆在仓库里&#xff0c;通过“数字化”&#xff0c;这些账本就被存入了线上的仓库里。而数智化则更加注重对数据的分析和利用&#xff0…
阅读更多...
最新文章

Copyright @ 2022~2023