ALTER MATERIALIZED VIEW LOG

Purpose

This statement is used to modify the attributes of a materialized view log (mlog).

Privileges

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table (ordinary table or materialized view). For more information about OceanBase Database privileges, see Privilege types in Oracle mode.

Syntax

ALTER MATERIALIZED VIEW LOG ON [schema.]table_name alter_mlog_action_list;

alter_mview_action_list:
    alter_mlog_action [, alter_mlog_action ...]

alter_mlog_action:
    parallel_clause
    | PURGE [[START WITH expr] [NEXT expr]]

parallel_clause:
    NOPARALLEL 
    | PARALLEL integer

Parameters

alter_mlog_action

• parallel_clause: specifies the degree of parallelism for the materialized view log table. Valid values:

  • NOPARALLEL: the degree of parallelism is 1. This is the default value.
  • PARALLEL integer: the specified degree of parallelism. integer must be an integer greater than or equal to 1.

  Here is an example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 PARALLEL 5;
  

• PURGE [[START WITH expr] [NEXT expr]]: specifies the time interval for the background cleanup task of materialized view logs. Valid values:

  • [START WITH expr]: optional. The time when the materialized view log file is cleared for the first time.
  • [NEXT expr]: optional. The time when the materialized view log file is cleared the next time. If this parameter is specified, the value of this parameter must be later than the value of the START WITH parameter.

  Notice

  Make sure that the START WITH expr and NEXT expr time expressions are set to future time points. Otherwise, an error will occur.

  We recommend that you use current_date to specify the local time. Here are some examples of time expressions:

  • Clean up expired materialized view log records every 10 seconds starting from the current time (current_date).

    START WITH current_date NEXT current_date + INTERVAL '10' SECOND
    

  • Clean up expired materialized view log records every 10 hours starting from the current time (current_date).

    START WITH current_date NEXT current_date + INTERVAL '10' HOUR
    

  • Clean up expired materialized view log records every day starting from the current time (current_date).

    START WITH current_date NEXT current_date + 1
    

  Here is an example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 PURGE START WITH current_date;
  

  ALTER MATERIALIZED VIEW LOG ON tbl1 PURGE NEXT current_date + 1;
  

  ALTER MATERIALIZED VIEW LOG ON tbl1 PURGE START WITH current_date NEXT current_date + 1;
  

References

Materialized view logs