Oracle Database is one of the most famous commercial non-relational DBMS in the world. To import and export data and metadata from ADO-compliant sources (MS Access, MS SQL DB, etc.) into a special dump file format, Oracle offers a technology known as Oracle Data Pump.
Structure of Oracle Data Pump
Data Pump has a three-component structure, which includes:
● The DBMS_DATAPUMP package, which is responsible for loading and unloading the data dictionary metadata. The loading and unloading tasks are started with the help of procedures, which constitute the technological core of this package. Without it, the Data Pump Export and Data Pump Import utilities cannot work, which will be described in more detail below.
● The DBMS_METADATA package, introduced in Oracle 9i, allows users to retrieve and modify metadata of any objects, such as tables, indexes, procedures, etc., in a single invocation.
● Two utilities, Data Pump Export (expdp) and Data Pump Import (impdp), are clients that Oracle, starting with version 10g, suggests using to upload and download files. Both programs can be run in command line mode or interactive mode.
Data export to Oracle DBMS: Data Pump Export
Data Pump Export is a program that allows you to upload data to OS files, known as dumps files, in a format that can only be handled by the Data Pump Import utility.
There are five main modes of export jobs in Data Pump Export.
Full Mode (FULL Mode)
Using the FULL parameter allows you to perform a full database export in one session. It requires an account with the BECOME USER privilege to use EXPORT_FULL_DATABASE.
Schema Mode
The SCHEMAS parameter allows exporting data or objects of a specific user (schemas, in database terms).
Table Mode
Allows you to export specific tables using the TABLES parameter.
Tablespace Mode (Tablespace Mode)
Allows you to export tables, indexes, and other segments contained in tablespaces using the TABLESPACES parameter.
Transportable (Transferable) Tablespace Mode
The export of a transportable tablespace is specified using the TRANSPORT_TABLESPACES parameter. In portable tablespace mode, only metadata for tables (and their dependent objects) within a specified set of tablespaces is exported.
The export of a portable tablespace is specified using the TRANSPORT_TABLESPACES parameter. In portable tablespace mode, only metadata for tables (and their dependent objects) within the specified tablespace set is exported.
The tablespace data files are copied in a separate operation. The Portable Tablespace Import is then executed to import the dump file containing the metadata and specify the data files to be used. Portable tablespace mode requires that the specified tables be completely self-contained. That is, all storage segments of all tables (and their indexes) defined in a tablespace set must also be contained in that set.
If there are violations of self-sufficiency, the export identifies all problems without actually performing the export. Exporting a migrated tablespace cannot be restarted after stopping.
Importing data into Oracle DBMS: Data Pump Import
Data Pump Import is a program used to load data into the target database. There are two main modes in Data Pump Import.
Importing a circuit from a dump
The SCHEMAS parameter is used for its implementation. If the schema to be imported has already been created under the same name, you need to delete it by executing the command “DROP USER OracleUser CASCADE;” in SQL*Plus (command line interpreter utility for Oracle Database) or SQLDeveloper development environment.
Next, to import the schema, you will need to run the impdp utility with parameters such as “impdp system/Pa$$w0rd SCHEMAS=Oracle User directory=Export Import” and “dumpfile=DumpSCHEMAS.dmp logfile=ImportSCHEMAS.log”.
Importing tables from dump
If you want to import only a part of tables from the database – as well as in the case of export, use the TABLES parameter. If these tables already exist, drop them using the TABLE_EXISTS_ACTION parameter or manually.
An additional, and often used, import mode is loading data via dblink – NETWORK_LINK.
The NETWORK_LINK parameter initiates an import via a connection (dblink) to a remote database. This means that the system to which the impdp client is connected contacts the source database pointed to by source_database_link, retrieves data from it, and writes the data to the database on the connected instance. Dump files are not used.
Additional options for importing or exporting to Oracle
To learn more about import and export parameters in Oracle Data Pump, you can call help by running the expdp or impdp utility with the help=y parameter. You will see additional import and export options.
➡️ CONTENT – required for filtering the data placed in the dump during export.
➡️ EXCLUDE and INCLUDE – excludes or includes certain types of database objects during import/export.
➡️ REMAP_TABLE – allows renaming a table when transferring tablespaces.
➡️ REMAP_SCHEMA – needed for moving objects between schemes.
➡️ REMAP_TABLESPACE – needed to move an object between tablespaces.
➡️ REMAP_DATAFIE – leads to file name uniformity when moving databases between platforms with different name formats.
➡️ TRANSFORM – needed to exclude separate storage attributes and attributes of other types from import.
When do we use import/export in Oracle?
Oracle Data Pump technology is used to create, save, and restore copies of data in the Oracle database. Another purpose of import and export in Oracle is data migration between servers, databases, and schemas, which is performed via dumps or dblink network connection.
Oracle is a high-quality and well-scalable solution. We at DB-Service offer services for migration from Oracle to PostgreSQL with a preliminary audit and subsequent support.