How to use different tablespaces for tables and indexes with Liquibase

Question

I am using Liquibase 4.0 and I am executing DB changes using SQL scripts. So I am pointing the SQL files path in the master change log to execute the changes via Liquibase. Now I need to segregate them since I have a requirement to have different tablespace for both tables and indexes. Hence I tried to find an option for parameterization in Liquibase, but could not find so. My Masterchangelog for the change looks like:-

<changeSet author="sanjib" id="01_PR_001">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/PR_001/PR_001.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="true"/>
      <rollback>
        <dropTable cascadeConstraints="true" tableName="PERSON"/>
      </rollback>
</changeSet>

The Liquibase.properties file looks like this:-

driver: oracle.jdbc.OracleDriver
classpath: ojdbc6.jar
url: jdbc:oracle:thin:@localhost:1521/xe
databaseChangeLogTablespaceName:DATA
username: sanjib

Now the main issue is that the table defined in the SQL script is not created under the user's tablespace. Instead, it is using the username from the properties file as tablespace name, hence facing the issue that Table cannot be created since tablespace is missing, which is an obvious error. Hence to create the DATABSAECHANGELOG & DATABASECHANGELOGLOCK table, I had to add the property 'databaseChangeLogTablespaceName' in the properties file, but the tables and indexes are not getting created.

So please help in fixing the 2 issues. How to point the installation via Liquibase to use the user's default tablespace instead of using the username as tablespace. Can we parameterize the Table's tablespace and index's tablespace using the above setup as defined in the XML file?

Answer 1

To use different tablespaces for tables and indexes, liquibase property substitution is the way to achieve it. You can declare 2 tablespaces name properties, one for tables and the other for indexes. These properties can be declared in liquibase.properties file or at the start of your changelog itself. You can then access it using the syntax ${property_name} and use it at the places to provide tablespace name.

From liquibase docs on this link, you can specify tablespace name at the time of creating table. Example for the same is as below: (Example with declaring a property in liquibase.properties file)

Add a property in your liquibase.properties file : tablespace: tablespaceQA

<changeSet id="2" author="liquibase">
   <createTable catalogName="department2"
          remarks="A String"
          schemaName="public"
          tableName="person"
          tablespace="${tablespace}">
      <column name="address" type="varchar(255)"/>
   </createTable>
</changeSet>

the property ${tablespace} gets substituted with it's value as configured in your liquibase.properties file or with the value you pass when you run liquibase update command (liquibase -Dtablespace='tablespaceQA' update)

(Example with declaring a property at start of your changelog)

For indexes, you can declare a property at the start of your changelog (or in liquibase.properties file).

<property name="index_tablespace" value="INDEX" dbms="oracle"/>

now, use the property in createIndex tag as folows:

<createIndex indexName="idx_firstname" tableName="person" tablespace="${index_tablespace}"/>

For more details, visit the comments on this post.

Cheers!!