Redefining a SODA Collection

A Redefining a SODA Collection

You can use online redefinition to change the metadata or other properties of an existing collection. In particular, after upgrading so that database initialization parameter compatible is at least 20, you can migrate a collection to reflect the new default metadata.

The default collection metadata for a database with compatible initialization parameter at least 20 has "JSON" as the value of metadata field contentColumn.sqlType. And it has "UUID" as the value of metadata field versionColumn.method. If your compatible setting is 20 or greater then Oracle recommends that you use online redefinition to change the metadata of an existing collection so that it uses these values.

Online redefinition for a SODA collection is similar to online redefinition for a database table. The PL/SQL procedures used (in package DBMS_SODA) for a collection are analogous to their counterparts for a table in package DMBS_REDEFINITION.

Starting with the collection to be redefined, you apply SODA online-redefinition procedures, one by one. At each step, you can use subprogram DBMS_SODA.abort_redef_collection to abort the migration process if an error is raised.

As an example, the steps presented here migrate collection MyCollection so that its metadata reflects that of the default metadata for a database with initialization parameter compatible 20 or greater.

The following code creates the initial collection to be migrated, which uses textual JSON data stored as BLOB content. The default metadata for a database with parameter compatible less than 20 is specified here explicitly, for illustration purposes. In particular, contentColumn.sqlType is "BLOB", and versionColumn.method is "SHA256".

v_original_collection := 'MyCollection';
v_original_metadata :=
  '{"keyColumn": {"name": "ID",
                  "sqlType": "VARCHAR2",
                  "maxLength": 255,
                  "assignmentMethod": "UUID"},
    "contentColumn": {"name": "JSON_DOCUMENT",
                      "sqlType": "BLOB",
                      "compress": "NONE",
                      "cache": true,
                      "encrypt": "NONE",
                      "validation": "STANDARD"},
    "versionColumn": {"name": "VERSION",
                      "method": "SHA256"},
    "lastModifiedColumn": {"name": "LAST_MODIFIED"},
    "creationTimeColumn": {"name": "CREATED_ON"},
    "readOnly": false}';

DBMS_SODA.create_collection(v_original_collection,
                            v_original_metadata);

The steps below change fields contentColumn.sqlType and versionColumn.method. The other metadata fields are left unchanged, except that fields that no longer apply have been removed: compress, cache, encrypt, and validation. (Those fields do not apply to document content stored as JSON data type.)

To perform online redefinition for a collection, you need the following database privileges:

Privilege EXECUTE for PL/SQL package DBMS_REDEFINITION
System privilege CREATE MATERIALIZED VIEW
System privilege CREATE TABLE or, if the collection is backed by a table in a database schema different from the current one, CREATE ANY TABLE

See Also:

Summary of SODA Online Redefinition Subprograms in Oracle Database PL/SQL Packages and Types Reference

Use subprogram can_redef_collection, to check whether the collection is eligible for online redefinition. An error is raised if it is not eligible.
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollection';
  DBMS_SODA.can_redef_collection(v_original_collection);
END;
```
Use subprogram create_interim_collection, to create an interim collection to which data is copied while the original collection continues to handle production workload of SODA operations.
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollection';
  v_interim_collection := 'MyCollection_int';
  v_metadata := '{"contentColumn": {"sqlType": "JSON"},
                  "versionColumn": {"method": "UUID"}}}';

  DBMS_SODA.create_interim_collection(v_original_collection,
                                      v_interim_collection,
                                      v_metadata);
END;
```
Argument v_metadata specifies the metadata to change. You need not specify any metadata that remains unchanged.

The metadata for the interim collection (argument v_metadata) can include a tableName value that differs from that of the original collection, to specify the name of the table to which the interim collection is mapped.

If this table already exists then a mapped interim collection will be created on top of it. In this case, the table must not have any dependents (indexes, constraints, or triggers), or else an error is raised. Such dependents are instead taken (copied) from the original collection, in Step 4
Use subprogram start_redef_collection, to start the process of collection redefinition.
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollection';
  v_interim_collection := 'MyCollection_int';

  DBMS_SODA.start_redef_collection(v_original_collection,
                                   v_interim_collection);
END;
```
If your original collection has Virtual Private Database (VPD) policies then copy them to the interim collection before using start_redef_collection. And in that case use start_redef_collection(v_original_collection, v_interim_collection, DBMS_REDEFINITION.cons_vpd_manual), to indicate that the VPD policies have been copied manually.
Use subprogram copy_collection_dependents, to copy everything that depends on the original collection to the interim collection. This includes all constraints and indexes (including indexes defined automatically by SODA).
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
  v_metadata                 VARCHAR2(2000);
  v_num_errors               NUMBER;
BEGIN
  v_original_collection := 'MyCollection';
  v_interim_collection := 'MyCollection_int';

  DBMS_SODA.copy_collection_dependents(v_original_collection,
                                       v_interim_collection,
                                       num_errors => v_num_errors);
END;
```
The value, v_num_errors, of output parameter num_errors indicates how many errors were raised.

Even if the collection to be modified has no user-defined dependents, such as indexes on JSON content, it necessarily has some internal SODA-defined dependents, which must be copied to the interim collection.
Use subprogram sync_interim_collection, to synchronize the data in the interim collection to that of the original collection, to minimize downtime during the last step (Step 7). Do this if a large number of DML operations are performed on the original collection while you are performing online redefinition with the interim collection.
Subprogram sync_interim_collection checks for all dependents required for a SODA collection. An error is raised if they are not all present.
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollection';
  v_interim_collection := 'MyCollection_int';

  DBMS_SODA.sync_interim_collection(v_original_collection,
                                    v_interim_collection);
END;
```

Optional:

Caution:

This step is important. The effect of Step 7 cannot be undone.

Check that the interim collection works as expected. If it does not, use subprogram DBMS_SODA.abort_redef_collection to revert the changes, as follows:

DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollectionName';
  v_interim_collection := 'MyCollectionName_int';

  DBMS_SODA.abort_redef_collection(v_original_collection,
                                   v_interim_collection);
END;

Use subprogram finish_redef_table to finish the redefinition process, swapping the names of the original collection and the interim collection.
The effect of this step cannot be undone (unless it raises an error instead of committing).

Both collections are locked for part of the duration of finish_redef_table. The interim collection is synchronized to the original collection during this step. This includes performing any DML that has taken place on the original collection since the last use of sync_interim_collection (or since start_redef_collection, if you have not used sync_interim_collection).

The subprogram checks for all dependents required for a SODA collection. An error is raised if they are not all present.
```
DECLARE
  v_original_collection_name NVARCHAR2(2000);
  v_interim_collection_name  NVARCHAR2(2000);
BEGIN
  v_original_collection := 'MyCollection';
  v_interim_collection := 'MyCollection_int';

  DBMS_SODA.finish_redef_collection(v_original_collection, 
                                    v_interim_collection;
END;
```