D.3 Using Oracle Data Pump to Migrate to JSON Data Type
With this migration approach you use Oracle Data Pump, in data-only mode, to
load the data from the original table that has a textual JSON column into a new table of the
same shape, but with a JSON
-type column.
Follow these steps:
-
Create the new 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 aJSON
-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);
-
Use command-line client
expdp
, to export the data from the original table,j_purchaseorder
, to a dump file,purchase_ord_txt.dmp
. (table_owner
, here, is the database schema that owns the table, andpassword
is its password.)expdp table_owner/password tables=j_purchaseorder directory=mydir dumpfile=purchase_ord_txt.dmp logfile=expdp_po.log
-
Use command-line client
impdp
, to import the dumped data into the new table, using data-only mode.In this example,
table_owner
, is a placeholder for the database schema that owns the table;password
is a placeholder for its password; anddumpfile_dir
is a placeholder for the directory that contains the dump file.impdp table_owner/password tables=j_purchaseorder directory=dumpfile_dir dumpfile=purchase_ord_txt.dmp logfile=impdp_po.log remap_table=j_purchaseorder:j_purchaseorder_new content=data_only
-
Drop the original table, or rename it to some third name, and then rename the new table to the original table name. This is shown in Example D-4.
See Also:
Parent topic: Migrating Textual JSON Data to JSON Data Type