Using Online Redefinition to Migrate to JSON Data Type

D.4 Using Online Redefinition to Migrate to JSON Data Type

If PL/SQL procedure DBMS_REDEFINITION.can_redef_table gives you the go-ahead, then you can use online redefinition to migrate a textual JSON column to a JSON-type column without significantly affecting the availability of the table data. It remains accessible to both queries and DML during much of the migration process.

With this approach, you copy the original table data to a new, interim table while the original table continues to handle its regular workload.

When migrating this way, the table available for regular workload is locked in the exclusive mode only during a small window of time, which is independent of the size of the table and the complexity of the redefinition. Online redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined.

To perform online redefinition you must have execute privilege on package DBMS_REDEFINITION, and you must have the privilege to create materialized views, CREATE MVIEW.

table_owner, here, is the database schema that owns the table.

Invoke DBMS_REDEFINITION.can_redef_table, to check whether the table can be modified through online redefinition. Proceed to step 2 if no error is raised.
```
EXEC DBMS_REDEFINITION.can_redef_table('table_owner', 'j_purchaseorder');
```
Create interim table j_purchaseorder_new, using code similar to that of Example 4-1, that is, with the same shape as the original table, j_purchaseorder, but with a JSON-type column instead of the textual JSON column.
```
CREATE TABLE j_purchaseorder_new (id VARCHAR2(32),
                                  date_loaded TIMESTAMP(6) WITH TIME ZONE,
                                  po_document JSON);
```
If there are any virtual columns in the original table (j_purchaseorder), then define identical columns in the interim table. For example, this code defines virtual columns vc_user and vc_costcenter, based on top-level fields User and CostCenter, respectively.
```
ALTER TABLE j_purchaseorder_new ADD (vc_user GENERATED ALWAYS AS
  (json_value(po_document, '$.User' RETURNING VARCHAR2(20))));
ALTER TABLE j_purchaseorder_new ADD (vc_costcenter GENERATED ALWAYS AS
  (json_value(po_document, '$.CostCenter' RETURNING VARCHAR2(6))));
```

Use procedure DBMS_REDEFINITION.start_redef_table, applying the JSON data type constructor to the textual JSON column.

BEGIN
  DBMS_REDEFINITION.start_redef_table('table_owner',
                                      'j_purchaseorder',
                                      'j_purchaseorder_new',
                                      'ID ID, DATE_LOADED DATE_LOADED, JSON(PO_DOCUMENT) PO_DOCUMENT',
                                      REFRESH_DEP_MVIEWS => 'Y',
                                      ENABLE_ROLLBACK => FALSE);
END;
/

If the original table contains dependents, such as constraints, indexes, and virtual private database (VPD) policies, then call procedure DBMS_REDEFINITION.copy_table_dependents to copy them to the interim table.

DECLARE
n_errors INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents('table_owner',
                                          'j_purchaseorder',
                                          'j_purchaseorder_new',
                                          NUM_ERRORS => n_errors,
                                          IGNORE_ERRORS => FALSE);
  DBMS_OUTPUT.put_line(n_errors);
END;
/

(Optional) If you issued a large number of DML operations on the original table during the previous steps, then consider using procedure DBMS_REDEFINITION.sync_interim_table to sync the data to the interim table, to minimize the downtime at the next step.

DBMS_REDEFINITION.sync_interim_table ('table_owner',
                                      'j_purchaseorder',
                                      'j_purchaseorder_new',
                                      PART_NAME => NULL,
                                      CONTINUE_AFTER_ERRORS => FALSE);

Check that the interim table works as you expect. Do whatever you feel is needed, to convince yourself that this is the case. The next step, step 8, which cannot be undone, swaps the names of the two tables, so what is now the interim table goes "live" with the original table name.

If your checking determines that the interim table is not working as expected, then you can use procedure DBMS_REDEFINITION.abort_redef_table to revert the changes made so far. It cleans up problems that may have recurred during redefinition and removes temporary objects, such as materialized view logs, that have been created so far.

You can use abort_redef_table to terminate the redefinition process any time after you invoke start_redef_table and before you invoke DBMS_REDEFINITION.finish_redef_table.
```
DBMS_REDEFINITION.abort_redef_table ('table_owner',
                                     'j_purchaseorder',
                                     'j_purchaseorder_new',
                                     PART_NAME => NULL);
```
Use procedure DBMS_REDEFINITION.finish_redef_table to finish online redefinition. This action cannot be undone. This swaps the names of the original and interim tables, which are both locked for a brief period. After the operation the table with the original name contains the redefined data, and the table with the interim name contains the original (old) data.
```
BEGIN
  DBMS_REDEFINITION.finish_redef_table('table_owner',
                                       'j_purchaseorder',
                                       'j_purchaseorder_new',
                                       DML_LOCK_TIMEOUT => 0);
END;
/
```
(Optional) Drop the interim table, which now contains the original (old) data, if you don't need it anymore. Make sure that you drop the interim table, and not the table with the original name. Specifying PURGE is optional; if you use PURGE then you cannot recover the dropped table.
```
DROP TABLE 'table_owner', 'j_purchaseorder_new' CASCADE CONSTRAINTS PURGE;
```