3.10 Tips for Liquibase Settings

This section covers the following:

3.10.1 How to Exclude Table Properties When Deploying Changelogs to a New Environment

When capturing tables from one environment and deploying to another, there are certain table properties you may want to exclude from the deployment while creating the table. This may be due to these properties being unavailable or undesired in your target environment, such as:

SQLcl uses the Data Definition Language (DDL) session settings in the target environment, where the database objects are created or altered in (and not the environment the changelog was generated from), to generate the DDL for the objects. By turning off the DDL settings, you can prevent the corresponding table properties from being included in the table creation.

  1. Generate your Liquibase changelog file for the table you want to capture and deploy.
    liquibase generate-object -object-name [table name] -object-type table

    As an example, consider the following table:

    CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST" 
   (    "A" VARCHAR2(20) NOT NULL ENABLE, 
        "B" VARCHAR2(20), 
        "C" VARCHAR2(20) NOT NULL ENABLE, 
        CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A") 
  USING INDEX 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  TABLESPACE "USERS" ENABLE 
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 COMPRESS BASIC 
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS" 
  PARTITION BY HASH ("C") 
 (PARTITION "SYS_P803" SEGMENT CREATION DEFERRED 
  COMPRESS BASIC 
  TABLESPACE "USERS");

    The following is an excerpt from the resulting changelog (the partition, compression and tablespace attributes are highlighted):

    <?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
…
      <TABLE_PROPERTIES>
         <HASH_PARTITIONING>
            <COL_LIST>
               <COL_LIST_ITEM>
                  <NAME>C</NAME>
               </COL_LIST_ITEM>
            </COL_LIST>
            <DEFAULT_PHYSICAL_PROPERTIES>
               <HEAP_TABLE>
                  <SEGMENT_ATTRIBUTES>
                     <PCTFREE>0</PCTFREE>
                     <PCTUSED>40</PCTUSED>
                     <INITRANS>1</INITRANS>
                     <MAXTRANS>255</MAXTRANS>
                     <STORAGE>
                        <BUFFER_POOL>DEFAULT</BUFFER_POOL>
                        <FLASH_CACHE>DEFAULT</FLASH_CACHE>
                        <CELL_FLASH_CACHE>DEFAULT</CELL_FLASH_CACHE>
                     </STORAGE>
                     <TABLESPACE>USERS</TABLESPACE>
                  </SEGMENT_ATTRIBUTES>
                  <COMPRESS>BASIC</COMPRESS>
               </HEAP_TABLE>
            </DEFAULT_PHYSICAL_PROPERTIES>
            <PARTITION_LIST>
               <PARTITION_LIST_ITEM>
                  <NAME>SYS_P803</NAME>
                  <SEGMENT_ATTRIBUTES>
                     <SEGMENT_CREATION_DEFERRED></SEGMENT_CREATION_DEFERRED>
                     <TABLESPACE>USERS</TABLESPACE>
                  </SEGMENT_ATTRIBUTES>
                  <COMPRESS>BASIC</COMPRESS>
               </PARTITION_LIST_ITEM>
            </PARTITION_LIST>
         </HASH_PARTITIONING>
      </TABLE_PROPERTIES>
   </RELATIONAL_TABLE>
</TABLE>]]></n0:source>
</n0:createSxmlObject>
</changeSet>
</databaseChangeLog>
  2. In the environment where you apply the update and generate the table, turn off the following DDL settings:
    set ddl partitioning off
set ddl segment_attributes off
set ddl tablespace off

    Partitioning turns off the partitioning property, segment_attributes turns off the compression property, and tablespace turns off the tablespace property.

    SQL> show ddl
Parameters
---------------------------------------------
BODY                           : ON
COLLATION_CLAUSE               : NEVER
CONSTRAINTS                    : ON
CONSTRAINTS_AS_ALTER           : ON
EMIT_SCHEMA                    : ON
FORCE                          : ON
INHERIT                        : ON
INSERT                         : ON
OID                            : ON
PARTITIONING                   : OFF
PRETTY                         : ON
REF_CONSTRAINTS                : ON
SEGMENT_ATTRIBUTES             : OFF
SIZE_BYTE_KEYWORD              : ON
SPECIFICATION                  : ON
SQLTERMINATOR                  : ON
STORAGE                        : ON
TABLESPACE                     : OFF
---------------------------------------------
End Parameters
  3. To confirm that these settings are not included in the table generation, use the liquibase update-sql command. This command shows you the exact SQL and PL/SQL code that will be run by the update command before you execute it.
    liquibase update-sql -changelog-file partition_compression_tablespace_test_table.xml

    When partitioning, compression, and tablespace settings are turned off, the SQL code looks like the following snippet:

    -- Changeset partition_compression_tablespace_test_table.xml::fb1d77405bcd3cf7d60e555337b80ec9555134d7::(HR)-Generated
CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST"
   (    "A" VARCHAR2(20) NOT NULL ENABLE,
        "B" VARCHAR2(20),
        "C" VARCHAR2(20) NOT NULL ENABLE,
        CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A")
  USING INDEX ENABLE
   );

    The following is an example of the SQL code if partitioning, compression, and tablespace settings were turned on:

    -- Changeset partition_compression_tablespace_test_table.xml::fb1d77405bcd3cf7d60e555337b80ec9555134d7::(HR)-Generated
CREATE TABLE "PARTITION_COMPRESSION_TABLESPACE_TEST"
   (    "A" VARCHAR2(20) NOT NULL ENABLE,
        "B" VARCHAR2(20),
        "C" VARCHAR2(20) NOT NULL ENABLE,
        CONSTRAINT "PARTITION_COMPRESSION_TABLESPACE_TEST_PK" PRIMARY KEY ("A")
  USING INDEX
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 COMPRESS BASIC
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY HASH ("C")
(PARTITION "SYS_P803"  SEGMENT CREATION DEFERRED
  COMPRESS BASIC
  TABLESPACE "USERS");
  4. You can apply the update and create the table by running the update command in your target environment. This creates the table as unpartitioned, uncompressed, and uses the tablespace of the target environment (rather than attempting to use the tablespace of the environment the changelog was generated from).
    liquibase update -changelog-file partition_compression_tablespace_test_table.xml

3.10.2 Create Database Objects in a Schema Different From the Connected Schema

With SQLcl Liquibase, you can make deployments to a different database user schema than the one you are connected to. However, you must have the required permissions for the target user schema.

There are three important parameters related to this for the liquibase update command:

  • -output-default-schema|-ouds

    Controls if the schema is output or not when running the data definition language (DDL) SQL to create or alter your database object.

    • If -output-default-schema true is set, the schema is included. 

      create table storefront.merchandise …

    • If -output-default-schema false is set or -output-default-schema is not set at all, the schema is not included. 

      create table merchandise …

    To deploy database objects to a different schema than the connected schema, you must set -output-default-schema true so that you can specify the schema you want to create the objects in using -default-schema-name.

  • -default-schema-name|-desn

    If -output-default-schema true is set, the -desn parameter controls which schema is output.

    Use this parameter to specify the schema in which to create your database objects. For example, if you are connected to test_user and you want to create your table in storefront, your command would be:

    liquibase update -changelog-file merchandise_table.xml -output-default-schema true -default-schema-name storefront

    If -output-default-schema true is set and -default-schema-name is not set, the schema output is based on the ownerName attribute for your changeset in your changelog file. 

    <n0:createSxmlObject objectName="Merchandise" objectType="TABLE" ownerName="LBUSER" replaceIfExists="false" >

    If ownerName is not set, the schema you are connected to is used.

  • -liquibase-schema-name|-lbsn

    One of the big advantages of the Liquibase functionality in SQLcl is the ability to track and manage your changes. This is mainly done with the databasechangelog tracking tables automatically created and updated in your schema when you run Liquibase.

    -liquibase-schema-name allows you to control in which schema you update Liquibase tracking information through the databasechangelog tables.

    This allows you to have use cases such as having a control schema or user that tracks all your Liquibase change information, while applying the database objects themselves to other schemas.

    For example, you are connected to the user or schema storefront and want to create your merchandise table using your merchandise_table.xml changelog file. However, you do not want Liquibase to create your databasechangelog tables in this schema. You want the tracking information to be stored in the pre-existing databasechangelog tables in your schema control_user.

    The following command accomplishes this:

    liquibase update -changelog-file merchandise_table.xml -liquibase-schema-name control_user

    Note:

    Storefront needs to have the permission to write on the databasechangelog, databasechangeloglock, and databasechangelog_actions tables in control_user.
    GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOG TO STOREFRONT;
GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOGLOCK TO STOREFRONT;
GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOG_ACTIONS TO STOREFRONT;