You’re using Liquibase to manage your Oracle application schemas, and you’re also using SQLcl to make that easier.

Now you want to get rid of a table. How can we do that? You’ve probably heard us talk about the automated changeLots and UPDATES/ROLLBACKs quite a bit, but the power and flexibility of Liquibase isn’t sacrificed just because you choose to code the changeSets for yourself!

While we could write a SQL script to do a DROP TABLE (IF EXISTS), and run that, it can be much easier for folks browsing logs and DIFF reports to identify what’s happened if you use single-task types of operations vs a ‘generic whatever SQL I want’ type of changeSet.

Let’s see what Liquibase gives us.

Choice 1: Use the dropTable changeSet (KILL -9 !!!)

This example uses XML as an example. Looking for JSON or YAML instead? You’ll need to use SQLcl version 22.4, and I have examples here.

I’m going to drop my table, ‘TABLE1’ from my ‘JEFE’ schema.

And I’m borrowing my changeSet directly from the Liquibase docs.

Let’s make the table go away, to the Good Place.

Here’s the actual XML I’m using.

<?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  author="thatjeffsmith-demos-XML-drop-table-liquibase-docs"  id="dropTable-example">  
    <dropTable  cascadeConstraints="true"  
            schemaName="JEFE"  
            tableName="TABLE1"/>  
</changeSet>
</databaseChangeLog>

Ok, I’m going to connect as JEFE to my database with SQLcl, and run the changeLog.

So if the operation was a success, my table should be gone, right?

If I browse my schema, I won’t see ‘TABLE1’ anymore, and I look at the DATABASECHANGELOG table Liquibase uses to maintain the schema state, I can see the changeLog ran my changeSet.

Our upcoming new SQL Developer desktop offering. We’re getting closer #tease

dropTable doesn’t use the PURGE keyword

This means that for awhile at least, your table should be available in the Recycle Bin.

Don’t rely on this, but it sure does come in handy sometimes!

Let’s look at a safer approach.

Is this the ‘best’ route to take What if we just renamed it?

I’m really hesitant about dropping objects or even columns out of tables. It’s destructive, and sometimes hard to recover from.

I like a softer approach.

Renaming objects, if only temporarily. If someone ‘breaks the build’, it’s much easier to rename the object back, then recreating it, especially if there’s data attached.

Liquibase also gives us a renameTable changeSet.

So let’s run a renameTable instead –

<?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  author="liquibase-docs"  id="renameTable-table1-table_one">  
    <renameTable  newTableName="TABLE_ONE"  
            oldTableName="TABLE1"  
            schemaName="JEFE"/>  
</changeSet>
</databaseChangeLog>

And here we go –

lb update -changelog-file lb-oracle-rename-table.xml

This approach doesn’t come for free either, unfortunately. You need to be careful about renaming columns and tables, especially when you actually mean to keep them around!

If you are using our automated changeLogs and updates, if you submit a changeSet where a table has had its column renamed, the database will see that you have an unused column, drop it, and add your new one. Bad, bad, bad!

The database doesn’t know that column XYZ used to be column ABC…so to correct this deficiency we’ll have a -force flag for updates later this year that will be required before SQLcl and the database allow for any destructive DDL to be performed on your schema.

Don’t guess what will happen…preview and test your changeLogs!

In the meantime, TEST your changeLogs! A great way to do this is via the update-sql command. You can see the SQL/DDL that will be performed based on the current state of the database and the provided changeLog.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment