0

I've been using Oracle's SQLcl, specifically the lb command to use Liquibase, for the last day or so to use for versioning my development, staging and production databases, which are using Oracle's Autonomous Data Warehouse (version 19).

It works fine when doing the tutorial changes, like the ones listed by Jeff Smith in his video on it here, although the syntax of the commands has changed a bit.

I'm reaching a problem when I want to drop an index, if I just execute the SQL statement DROP INDEX EXAMPLE_IDX in my development environment and then run the lb genschema statement, it doesn't drop the index on my staging environment when I run lb update -changelog controller.xml.

I think this is because it's generating a fresh Liquibase changelog every time I run lb genschema which seems to overwrite the previous changelog.

I'm also having the same behaviour when trying to drop tables etc, and I'm not sure how I am supposed to propogate DROP statements on tables and indexes with the lb tool.

Here's what I'm doing (users/passwords etc all changed).

Open SQLcl to create the staging database's structure (v1)

set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

Open SQL Developer on the dev database and delete the index.

DROP INDEX INDEXNAME;

Open SQLcl to update the changelog and the staging database.

set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

After doing these steps, I'm still noticing that the tables and indexes are still there, which means my database behaves differently to what it should be doing!

If anyone has knowledge or experience of using the lb tool, SQLcl, or can let me know some best practices that would be amazing.

  • AFAIK - you call lb genschema once to get the initial schema design. (Like a level 0 backup). – Michael Kutz Jul 1 at 14:52
  • I see, and then add the extra changes additionally like other Liquibase implementations? I had previously thought it was a totally automated process. Thanks for your comment Michael! – Ash Oldershaw Jul 1 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.