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 OceanBase Database privileges, see Privilege classification in Oracle-compatible 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
| Parameter | Description |
|---|---|
| schema. | Optional. Specifies the schema where the base table of the materialized view log is located. If you omit schema., the base table is assumed to be in the schema of the current session. |
| table_name | Specifies the name of the base table corresponding to the materialized view log. |
| alter_mlog_action_list | A list of operations that can be performed to modify the materialized view log. You can specify multiple operations, separated by commas (,). For more information, see alter_mlog_action below. |
alter_mlog_action
parallel_clause: specifies the parallelism of the materialized view log table. Valid values:NOPARALLEL: parallelism is 1, which is the default value.PARALLEL integer: specifies the parallelism. The value ofintegermust be greater than or equal to 1.
Example:
ALTER MATERIALIZED VIEW LOG ON tbl1 PARALLEL 5;PURGE [[START WITH expr] [NEXT expr]]: specifies the time interval for the background purge task of the materialized view log.[START WITH expr]: optional. Specifies the initial purge time of the materialized view log.[NEXT expr]: optional. Specifies the next purge time of the materialized view log.
Notice
Make sure that the time expressions
START WITH exprandNEXT exprare set to future time points. Otherwise, an error will be returned.We recommend that you use
current_dateto specify the current time in the current time zone. Here are some examples of time expressions:Purge expired records of the materialized view log every 10 seconds starting from the current time (
current_date).START WITH current_date NEXT current_date + INTERVAL '10' SECONDPurge expired records of the materialized view log every 10 hours starting from the current time (
current_date).START WITH current_date NEXT current_date + INTERVAL '10' HOURPurge expired records of the materialized view log every 1 day starting from the current time (
current_date).START WITH current_date NEXT current_date + 1
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;