Unload and Load Vectors Using Oracle Data Pump

Starting with Oracle Database 23ai, Oracle Data Pump enables you to use multiple components to load and unload vectors to databases.

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Oracle Data Pump is made up of three distinct components: Command-line clients, expdp and impdp; the DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API); and the DBMS_METADATA PL/SQL package (also known as the Metadata API).

Unloading and Loading a table with vector datatype columns is supported in all modes (FULL, SCHEMA, TABLES) using all the available access methods (DIRECT_PATH, EXTERNAL_TABLE, AUTOMATIC, INSERT_AS_SELECT).

Examples Vector Export and Import Syntax

expdp <username>/<password>@<Database-instance-TNS-alias>  dumpfile=<dumpfile-name>.dmp directory=<directory-name> full=y metrics=y access_method=direct_path

expdp <username>/<password>@<Database-instance-TNS-alias>  dumpfile=<dumpfile-name>.dmp directory=<directory-name> schemas=<schema-name> metrics=y access_method=external_table

expdp <username>/<password>@<Database-instance-TNS-alias>  dumpfile=<dumpfile-name>.dmp directory=<directory-name> tables=<schema-name>.<table-name> metrics=y access_method=direct_path

impdp <username>/<password>@<Database-instance-TNS-alias>  dumpfile=<dumpfile-name>.dmp directory=<directory-name> metrics=y access_method=direct_path

Note:

  • TABLE_EXISTS_ACTION=APPEND | TRUNCATE can only be used with the EXTERNAL_TABLE access method.
  • TABLE_EXISTS_ACTION=APPEND | TRUNCATE can load VECTOR column data into a VARCHAR2 column if the conversion can fit into that VARCHAR2.
  • TABLE_EXISTS_ACTION=APPEND | TRUNCATE can only load a VECTOR column with the source VECTOR data dimasion that matches that loaded VECTOR column's dimension. If the dimension does not match, then an error is raised.
  • TABLE_EXISTS_ACTION=REPLACE supports any access method.
  • It is not possible to use a the transportable tablespace mode with vector indexes. However, this mode supports tables with the VECTOR datatype.

Related Topics

Parent topic: Store Vector Embeddings