How to specify the DDL of the index that is implicitly created when creating a materialized view log

materialized-vieworacle

When I create a materialized view log, Oracle implicitly creates an index on that log. For example, when creating a materialized view log on table foo, Oracle creates the view log table mlog$_foo and the index i_mlog$_foo. Oracle creates the index in the default tablespace, and I need it on a different tablespace. I can't change the default tablespace for the user. How can I specify the DDL (or at least the tablespace) for the index?

Best Answer

This is in fact a bug: Materialized View Log (Mlog$) Index Not Created in its Default Tablespace (Doc ID 1959658.1). Excerpt:

The index created when a materialized view log is created appears to be created in the user's default tablespace rather than the same tablespace as the MV log table.
Additionally, if the user has no quota on their default tablespace, the index is not created and an error is not reported, leading to possible performance issues.

There is no workaround stated though, and no indication that you can force the tablespace yourself (the create materialized view log statements has no such option).

The solution section of that article states that it will be fixed in 12.2 (currently scheduled for first half of 2016), but that you can request a backport from Oracle support. So the best course of action might well be to open an SR and request that backport or some other workaround.

The only possible workaround that I know would be to change the default tablespace for the user before the mview log creation, then change it back. This is obviously not good – who knows what other objects could be created in the mean time – but might be all you've got.

@a_horse_with_no_name has different suggestion: rebuild the index after the creation is done. This allows you to change the tablespace (and other storage options):

SQL> alter index "I_MLOG$_FOO" rebuild tablespace YOURTBS online ;
Related Question