How To Export Data Into Dmp File In Oracle

Using Oracle Export and Import Utility Wizards

dbForge Studio for Oracle has Export Utility and Import Utility wizards for exporting and importing database objects. It allows to transfer data objects between Oracle databases, even if they reside on the platforms with dissimilar hardware and software configurations. dbForge Studio Export Utility Magician allows to excerpt the object definitions and table data from an Oracle database and stores them in an Oracle binary-format export dump file. Import Utility Wizard allows to insert extracted objects from consign dump files to Oracle database.

dbForge Studio uses Oracle consign and import utilities for consign and import. So these utilities are required for export and import operations. These utilities are installed with Oracle Customer software.

Note

Information technology is recommended to apply Oracle Client with version less or equal to the lowest version between source and target Oracle servers.

How to Export Data using Oracle Information Export Utility

To call the Export Utility Wizard, on the Database menu, select Export & Import, and and then click Consign Utility.

Oracle Export Utility

Note

To consign tables owned by another user, you must have enough privileges.

In that location are four modes of export: Table, User, Total, and Tablespace. You must connect as SYSDBA to use the Full and Tablespace modes.

Table mode

The Table mode allows to export particular tables. In the Table mode you tin can select tables and set additional queries to select a subset of rows from a set of tables for export.

To consign item tables:

Phone call the Consign Utility Magician.
Click the Next push if the Consign Manner folio is active.
Select tables to consign.
Click the Boosted query push button and enter a condition to select a subset of rows from a set of tables if necessary. Condition must be the WHERE clause for the SELECT argument applicable for all selected tables.
Click the Next push.
Select boosted objects and settings to export and click the Next button.
Enter or select export dump and configuration files location and names. Click the Export button.
Wait for consign finish, select the bank check box if you desire to view the export log, and click the End push button.

User way

The User manner allows extracting information objects of i ore more than (for SYSDBA) users. It tin can be useful for users who want to back up their data or who want to move objects from 1 possessor to some other.

To export all information of one or more users:

Call the Export Utility Sorcerer.
Click the Dorsum button if the Consign Tables folio is active.
Select the User mode and click the Next push button.
Select the users whose information y'all want to consign, and click Side by side. Only SYSDBA can export another user data.
Select additional objects and settings to export. Click Next.
Enter or select export dump and configuration files location and names. Click Export.
Expect for export cease, select the cheque box if you desire to view the export log. Click Finish.

Total way

In the Full mode an entire database can be exported to the file with all GRANTS and all data.

To consign a whole database:

Call the Export Utility Sorcerer.
Click Back if the Export Tables page is active.
Select the Full mode. Click Adjacent. If the Full choice is not available, you lot accept non enough privileges to apply the Full mode. You must connect as SYSDBA to use this fashion.
Select additional objects and settings to consign. Click Adjacent.
Enter or select export dump and configuration file locations and names. Click Consign.
Wait for the consign finish, select the bank check box if you lot want to view the export log. Click Cease.

Tablespace manner

To motility or copy a set of tablespaces you must copy the datafiles of these tablespaces, and export their metadata using Export Wizard in the Tablespace manner. The Tablespace mode allows to move a gear up of tablespaces from 1 Oracle database to another. Tablespaces to export must be ONLINE and READONLY. dbForge Studio can temporarily brand exporting tablespaces ONLINE and READONLY and restore their state subsequently export.

To consign tablespaces metadata:

Telephone call the Export Utility Sorcerer.
Click Dorsum if the Consign Tables page is active.
Select the Tablespace style and click Next. If the Tablespace pick is non available, y'all have not enough privileges to use the Tablespace mode. Yous must connect every bit SYSDBA to use this mode.
Select tablespaces to export. Click Adjacent.
Select additional objects and settings to consign. Click Consign.
Enter or select export dump and configuration files location and names. Click Next.
Wait for export terminate, select the check box if y'all want to view the export log. Click Cease.

How to Import Data using Oracle Data Consign Utility

There are four modes of import: Table, User, Total and Tablespace. You must connect as SYSDBA to utilize the Tablespace fashion. To call Import Utility Wizard, choose Export & Import, and then click Import Utility on the Database menu .

Oracle Import Utility

Table manner

This manner allows you to import specific tables. You can import either all tables from dump file or select particular tables.

To import specific tables:

Phone call the Import Utility Magician.
Select the export dump file and the configuration file location. Click Adjacent. You can optionally select the index file for indexes import.
Select the Tabular array fashion. Click Next.
Select what tables to import, source and target schemas. You tin can import all tables from the file, enter table names manually or select table names from existing tables. Click Next.
If y'all have chosen the Select from existing pick, select tables from the existing tables list. Only tabular array names but not owners will exist selected. Table owners have been selected at the previous step.
Select boosted objects to import and import settings on this page. Click Import.
Look for import finish, select the check box if you desire to view the import log. Click Finish.

User mode

This mode allows you to import all objects that belong to you lot (such equally tables, grants, indexes, and procedures). If you are continued every bit SYSDBA, you can import all the objects in the schemas of a specified set of users to the specified schemas.

To import information in the User manner:

Call Import Utility Wizard.
Select export dump file and configuration file location. Click Adjacent. You can optionally select index file for indexes import.
Select the User style. Click Next.
Select source and destination schemas. Click Next.
Select additional objects to import and import settings on this page. Click Import.
Wait for import end, select the check box if y'all desire to view the import log. Click Stop.

Full way

This mode allows you lot to import all data objects from the dump file. It likewise allows to utilise incremental import. Incremental import can be used for database restore.

To import the whole dump file:

Call the Import Utility Magician.
Select the export dump file and the configuration file location. Click Next. Y'all can optionally select the index file for indexes import.
Select the Full way. Click Adjacent.
Select the destination schema and incremental import blazon. Click Next.
Select additional objects to import and import settings on this page. Click Import.
Await for import end, select the check box if you desire to view the import log. Click Finish.

Tablespace fashion

This fashion allows to move a set up of tablespaces from one Oracle database to some other.

To import tablespaces:

Call the Import Utility Magician.
Select the export dump file and configuration file location. Click Next. You lot tin optionally select the index file for indexes import.
Select the Tablespace manner. Click Adjacent. If the Tablespace option is not available, you lot take non plenty privileges to use the Tablespace style. You must connect equally SYSDBA to utilize this mode.
Select tablespaces' datafiles, owners and tablespaces' names to import. Click Next. You tin can select tablespaces' owners from existing ones.
If yous have selected the Select from existing option, select tablespaces' source and target owners from existing users. Click Import.
Wait for import terminate, select the cheque box if you want to view the import log. Click Finish.

You can download a trial version of dbForge Studio for Oracle every bit well as larn more nearly its feature-rich Information Export/Import utilities on the Devart website.