SQLcl Liquibase: Physical Properties for Oracle tables

I have an Oracle table, it’s a very simple table, 3 columns.

But it has a couple of things that some may find, special.

When described by our Liquibase changeSet, as generated by SQLcl, it looks like this –

But, and it’s a big butt. What if I need to deploy this to an environment where partitioning and compression isn’t available, desired, or required?

SET DDL

Currently SQLcl uses the session settings for DDL transformations to generate the DDL on the update for all the changeSets in a changeLog.

So if I look at the proposed DDL for my changeSet (via the update-sql command), we can see what would be issued on an update for our changeLog.


-- Changeset fancy_pancy_table.xml::dca1214f6f09b4a30f2f478a17ed71554e108004::(HR)-Generated
CREATE TABLE "FANCY_PANCY"
   (    "A" VARCHAR2(20) NOT NULL ENABLE,
        "B" VARCHAR2(20),
        "C" VARCHAR2(20) NOT NULL ENABLE,
        CONSTRAINT "FANCY_PANCY_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");

Now let’s turn some things off

Now if we run the update-sql command again, we can see the new proposed ddl –

No partitions, no tablespace, no compression.

And let’s actually run it, then look at our resulting table again.

No partitions, no compression. And whatever the default tablespace is for this user was used.

We’re going to be making this more flexible going forward

In a future release, we’ll have the SET DDL apply to both:

generate-object and generate-schema for creating changeSets
update and updated-sql

So you can keep the storage properties COMPLETELY out of the changeSets and/or you’ll be able to ignore them when doing updates.

If you have strong opinions on this, this is my invitation to you to share those, here.

And, this will be documented in the SQLcl Docs, Liquibase chapter ASAP. Also, we have a major product underway to upgrade our ‘Quick Reference’ doc for SQLcl into a full, complete product doc set of books. This will take awhile, but I expect we’ll deliver those changes in chunks.

One of my goals is to have many more ‘how-to’ style examples for things like this, included WITH the docs.