Export and import data using EXP / IMP programs.

With the EXP tool, you can export data from Oracle database into binary form. By using the IMP tool, you can import data from a previously exported binary into the database.

You can use these programs to export / import:

– the whole base

– selected schema

– selected tables

– selected table space

For export / import the following parameters should be provided:

-USERID (who we want to export, we give the user’s name broken into his password)

-FILE (the name of the data file to be created with the path. Note that the folder in which you want to create the export file must be created beforehand)

-OG (optional, the log file name to be created with the path)

-FULL / OWNER / TABLES / TABLESPACES (what we want to export)

To be able to export the entire database, any schema or any table must have the role EXP_FULL_DATABASE, while importing the IMP_FULL_DATABASE role is required.

Just after the user name and password, the monkey will give the database ip address, or its connection descriptor alias TNSNAMES.ORA.

Here are some examples of exports and imports:

Export the whole database

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ BAZA.DMP’ LOG = ‘D: \ EXP_IMP \ BAZA_EXP.LOG’ FULL = Y

 

Export schema / schematics

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR.DMP’ LOG = ‘D: \ EXP_IMP \ HR_EXP.LOG’ OWNER = HR

 

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR_RAFAL.DMP’ LOG = ‘D: \ EXP_IMP \ HR_RAFAL_EXP.LOG’ OWNER = HR, RAFAL

 

Export table / table

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR_EMP.DMP’ LOG = ‘D: \ EXP_IMP \ HR_EMP_EXP.LOG’ TABLES = HR.EMPLOYEES

 

EXP USER = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR_EMP_DEP.DMP’ LOG = ‘D: \ EXP_IMP \ HR_EMP_DEP_EXP.LOG’ TABLES = HR.EMPLOYEES, HR.DEPARTMENTS

 

Export table spaces

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ USERS.DMP’ LOG = ‘D: \ EXP_IMP \ USERS_EXP.LOG’ TABLESPACES = USERS

 

Import the whole database

IMP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ BAZA.DMP’ LOG = ‘D: \ EXP_IMP \ BAZA_IMP.LOG’ FULL = Y

 

Import schema

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR.DMP’ LOG = ‘D: \ EXP_IMP \ HR_IMP.LOG’ FROMUSER = HR TOUSER = HR

 

Import table

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ HR_EMP.DMP’ LOG = ‘D: \ EXP_IMP \ HR_EMP_IMP.LOG’ FROMUSER = HR TOUSER = HR TABLES = EMPLOYEES

 

Import table spaces

EXP USERID = SYSTEM / sys FILE: = ‘D: \ EXP_IMP \ USERS.DMP’ LOG = ‘D: \ EXP_IMP \ USERS_IMP.LOG’ FROMUSER = HR TOUSER = HR TABLESPACES = USERS

 

Export / import using a parameter file

Instead of listing all the parameters on the command line, you can create a parameter file and include any options you want to use.

USERID = SYSTEM / sys

FILE = “D: \ EXP_IMP \ EXP_BAZY.DMP ‘

LOG = “D: \ EXP_IMP \ EXP_BAZY.LOG ‘

FULL = Y

 

EXP PARFILE = ‘D: \ EXP_IMP \ SETTINGS.TXT’

 

Information about parameters that can be used during export

Parameter information that can be used when performing import

Export and import data using EXPDP / IMPDP programs.

We can also export / import data using the DATA PUMP mechanism. Unlike the original export / import, this mechanism acts as a server process rather than a client process.

Data Pump creates and reads data files and logs that must be placed in a separate directory. To create a pointer to an external directory, use the CREATE DIRECTORY command in Oracle. Users who want to use EXPDP or IMPDPD must have READ and WRITE permissions on this directory.

As with the original export and import methods, you must have EXP_FULL_DATABASE assigned to perform full export or export in portable tablespace mode, and IMP_FULL_DATABASE

 

Data Pump supports four modes of export/import:

– the whole base

– selected schema

– selected tables

– selected table space

When starting Data Pump we provide information such as:

we want to export/import

– The directory in which the export files or the files to be imported are to be created

-the names of these files

– what do we want to export/import

As with EXP / IMP, these actions can be done remotely, but keep in mind that data files must be physically available to the server on which we want to perform these operations.

Additional options are EXCLUDE and INCLUDE, which allow you to specify, for example, which tables should be exported and which should be excluded from export. Turning off an object also disables all objects that depend on it.

Here are some examples of export / import:

Export of entire database:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = BAZA.DMP LOGFILE = BAZA_EXPDP.LOG FULL = Y

 

Schema export / schema:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR.DMP LOGFILE = HR_EXPDP.LOG SCHEMAS = HR

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR.DMP LOGFILE = HR_RAFAL_EXPDP.LOG SCHEMAS = HR, RAFAL

Export table / table:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR_EMP.DMP LOGFILE = HR_EMP_EXPDP.LOG TABLES = HR.EMPLOYEES

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR_EMP_DEP.DMP LOGFILE = HR_EMP_DEP_EXPDP.LOG TABLES = HR.EMPLOYEES, HR.DEPARTMENTS

Export table spaces:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = USERS.DMP LOGFILE = USERS_EXPDP.LOG TABLESPACES = USERS

Export using the include option:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = USERS_INC.DMP LOGFILE = USERS_INC_EXPDP.LOG TABLESPACES = USERS INCLUDE = TABLE: IN (‘EMPLOYEES’)

Export using the exclude option:

EXPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = BAZA_EXC.DMP LOGFILE = BAZA_EXC_EXPDP.LOG FULL = Y EXCLUDE = SCHEMA: IN (‘HR’)

Import of the whole database:

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = BAZA.DMP LOGFILE = BAZA_IMPDP.LOG FULL = Y

 

Import schema / schemas:

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR.DMP LOGFILE = HR_IMPDP.LOG SCHEMAS = HR

 

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR_RAFAL.DMP LOGFILE = HR_RAFAL_IMPDP.LOG SCHEMAS = HR, RAFAL

 

Import table / tables:

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR_EMP.DMP LOGFILE = HR_EMP_IMPDP.LOG TABLES = HR.EMPLOYEES

 

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = HR_EMP_DEP.DMP LOGFILE = HR_EMP_DEP_IMPDP.LOG TABLES = HR.EMPLOYEES, HR.DEPARTMENTS

 

Import table spaces:

IMPDP SYSTEM / sys DIRECTORY = DTPUMP DUMPFILE = USERS.DMP LOGFILE = USERS_IMPDP.LOG TABLESPACES = USERS

 

Information about the parameters that can be used during export and import using data pump will be given by the commands:

-EXPDP HELP = Y

-IMPDP HELP = Y

Differences between EXP / IMP and EXPDP / IMPDP

Although both methods are used for the same purpose, ie for exporting and importing data, it is important to remember that there are two different tools and their difference is not just a different syntax or parameter.

The Data Pump, unlike the traditional method, acts as a server process, so even if the console where we run the EXPDP or IMPDP command hangs, or even shut down, the process continues.

Data Pump operates on file groups, while the traditional method on individual files.

Data Pump uses files directly on the server (via directory), while the traditional method is from the client computer or server, depending on where we run EXP / IMP.

Metadata in the traditional method is kept as a DDL statement, while the data pump is represented in the XML document format.

The data pump implements the export/import process by default, while the traditional method uses a single stream.

Data pump uses a block method, whereas the traditional method is based on bytes.

When you import a user schema with a data pump, you will be re-created, but with the traditional method you will need to create it first, then import its objects.

When using a data pump we can recreate user / tables / tablespaces / data files in general under other names / paths using the REMAP switch (eg REMAP_TABLE = EMPLOYEES: EMPLOYEES).