Using Oracle SQLcl to maintain our Oracle Database application schemas can be made much easier by using our built-in and extended Liquibase support. Being able to confidently deploy updates to our tables, views, pl/sql programs, etc. via automated CI/CD systems is a next-level, game-changing proposition!
Today I was helping a colleague apply an update to their local database, and it failed!
Error report -
ORA-00959: tablespace 'DATA' does not exist
00959. 00000 - "tablespace '%s' does not exist"
*Cause:
*Action:
Rolling back changeset.The Problem: the changeSet mentioned a tablespace that didn’t exist in the target database.
Our SQLcl user had connected to an Autonomous Database where ‘DATA’ is the default tablespace to generate a changeLog for their schema.
When they went to apply the same changeLog to their local database, it failed as their default tablespace was ‘USERS.’
The Solution: regenerate the changeLog with the appropriate DDL settings.
SQLcl’s generate-object and generate-schema commands both use DBMS_METADATA to generate the XSXML used to create the changeSets. That package has an API that it used to dictate how exactly to generate the DDL.
SQLcl (and SQL Developer’s DEFAULT) looks like this:
So, before running the ‘generate’ commands, you need to use ‘SET DDL‘ to change these parameters as designed.
For example, let’s turn off a few things and generate a changeSet for the HR.REGIONS table:
Now let’s turn them back on and generate yet another changeSet for the table.
And if we take a gander at a side-by-side diff of the two XML files:
Be careful!
If you turn something, anything off…
set ddl partitioning offApply changeSets generated with this setting and you may have negated your carefully tuned schema! Hello, slow queries on your billion row tables.
Why? No partitions!
I suggest that generating changeSets NOT be done ad hoc.
Instead, have SCRIPTS created that leave nothing to chance. When automating ‘builds’, one or more scripts will run that have the exact type of DDL generated as rquired.
