[Oracle Extensions] [CreateMaterializedView] Change values of REFRESH parameters ( REFRESH_MODE, REFRESH_METHOD )

General Discussion
1

Hi all,
I have been working with Materialized Views, and as far as I have seen, the MV refreshing parameters always have the same values:
REFRESH_MODE=DEMAND
REFRESH_METHOD=FORCE

I need to configure them differently:
REFRESH_MODE=COMMIT
REFRESH_METHOD=FAST

In addition, it is necessary to create a MATERIALIZED VIEW LOG to apply this configuration.
So, the question is,
Is it possible to do this with Oracle Extension?
Is it even possible to do it with Liquibase?

An example of a Materialized View I have created:

<?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:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.19.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.19.xsd ">
<changeSet author="liquibase" id="baseline_vm_grctx-1">
	<comment>Create VM GRCTX</comment>
	<ext:createMaterializedView 
		subquery="SELECT column-1,column-2,column-n FROM table-1"
		tableSpace="${tablespace_data}"
		columnAliases="alias-1, alias-2,alias-n"
		viewName="GRCTX"/>
</changeSet>

<changeSet author="liquibase" id="baseline_vm_grctx-2">
	<comment>Create unique constraint UNIQUE_COLUMN-1</comment>
	<addUniqueConstraint 
		columnNames="column-1"
		constraintName="UNIQUE_COLUMN-1"
		tableName="GRCTX"
		tablespace="${tablespace_index}"/>
</changeSet>
</databaseChangeLog>

Oracle 19c
ora19_vm_params_values

Any help would be appreciated!

2

Why not just use custom sql to create the MV with all of the parameters that you need?

<changeSet author="liquibase" id="baseline_vm_grctx-1">
	<comment>Create VM GRCTX</comment>
    <sql> 
		CREATE MV SQL HERE...
    </sql>
</changeSet>
3

You have made my day!

I read somewhere that Materialized Views were specific to Oracle. Therefore I thought the only way to create them was by using the Liquibase Oracle extension.

Thanks, life is much easy now.

1 Like