Add or Remove an MR_COUNTER column

You can alter an existing multi-region table to add an MR_COUNTER column. The existing records in the table will have a value of zero for the newly added MR_COUNTER column. You can also remove an existing MR_COUNTER column from a multi-region table.

Adding an MR_COUNTER column to an existing multi-region table

Use ALTER TABLE to add an MR_COUNTER column to an existing multi-region table.

Create a multi-region table myTable without an MR_COUNTER column.

CREATE TABLE myTable ( id INTEGER,
                     name STRING,
                     team STRING,
                     PRIMARY KEY (id)) IN REGIONS DEN,LON;

Use ALTER TABLE to add an MR_COUNTER column to the table myTable. This is a PN counter.

ALTER TABLE myTable (ADD count INTEGER AS MR_COUNTER);

You can use ALTER TABLE to add a JSON MR_COUNTER field to a multi-region table.

ALTER TABLE demoJSONMR ADD (secondJSON
          JSON(new_counter AS NUMBER MR_COUNTER));

Dropping an MR_COUNTER Column

To remove an MR_COUNTER column from a multi-region table, use ALTER TABLE with a DROP id clause.

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON;

ALTER TABLE myTable (DROP count);

You can also drop a JSON MR_COUNTER field from a table using ALTER TABLE syntax.

ALTER TABLE demoJSONMR(DROP secondJSON);

Note:

Dropping a JSON MR_COUNTER field does not alter any existing JSON documents. It just removes the metadata pertaining to the JSON MR_COUNTER field which is being dropped.