ALTER MATERIALIZED VIEW LOG

Purpose

This statement is used to modify the properties of a materialized view log (MLOG).

Privilege requirements

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table. For more information about privileges in OceanBase Database, see Privilege classification 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: Used to modify the parallelism of the materialized view log table. Valid values:

  • NOPARALLEL: Specifies a parallelism of 1, which is the default value.
  • PARALLEL integer: Specifies the parallelism, where integer is an integer greater than or equal to 1.

  Example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 PARALLEL 5;
  

• PURGE [[START WITH expr] [NEXT expr]]: Used to modify the time interval for the background cleanup task of the materialized view log.

  • [START WITH expr]: Optional. Specifies the initial cleanup time for the materialized view log.
  • [NEXT expr]: Optional. Specifies the next cleanup time for the materialized view log.

  Notice

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

  It is recommended to use current_date to represent the current time in the current time zone. 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 1 day starting from the current time (current_date).

    START WITH current_date NEXT current_date + 1
    

  Examples:

  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