Oracleexpdp/impdp example

Note when using EXPDP and IMPDP: EXP and IMP are client tool programs that can be used either on the client or on the server. EXPDP and IMPDP are tool programs on the server. They can only be used on the ORACLE server, but not on the client. IMP only applies to EXP exported files, not EXPDP exported files; IMPDP

Note when using EXPDP and IMPDP: EXP and IMP are client tool programs that can be used either on the client or on the server. EXPDP and IMPDP are tool programs on the server. They can only be used on the ORACLE server, but not on the client. IMP only applies to EXP exported files, not EXPDP exported files; IMPDP

Precautions when using EXPDP and IMPDP:

EXP and IMP are client tool programs that can be used either on the client or on the server.

EXPDP and IMPDP are tool programs on the server. They can only be used on the ORACLE server, but not on the client.

IMP only applies to EXP exported files, not EXPDP exported files; IMPDP only applies to EXPDP exported files, not EXP exported files.

When you run the expdp or impdp command, you can leave the username/password @ Instance name as the identity, and then enter it as prompted, such:

Expdp schemas = scott dumpfile = expdp. dmp DIRECTORY = dpdata1;

1. Create a logical directory. This command does not create a real directory in the operating system. It is best to create a directory as an administrator such as system.

Create or replace directory dpdata1 as 'd: \ test \ dump ';

2. Check the Administrator directory (check whether the operating system exists at the same time because Oracle does not care whether the directory exists. If the directory does not exist, an error occurs)

Select * from dba_directories;

3. Grant scott the operation permission on the specified directory. It is best to grant permissions to the system administrator.

Grant read, write on directory dpdata1 to scott;

Iv. Export data

1) by User Guide

Expdp system/managerdirectory = dumpdir dumpfile = expdp. dmps chemas = scott;

2) parallel process parallel

Expdp system/manager directory = dumpdir dumpfile = para_export % u. dmp parallel = 2 job_name = scott3

3) import by table name

Expdp system/manager directory = dumpdir dumpfile = expdp. dmp DIRECTORY = dpdata1;

4) export by query Conditions

Expdp system/manager directory = dumpdir dumpfile = expdp. dmp Tables = emp query = 'where deptno = 20 ';

5) export by tablespace

Expdp system/manager directory = dumpdirdumpfile = tablespace. dmp TABLESPACES = temp, example;

6) import the entire database

Expdp system/manager directory = dumpdirdumpfile = full. dmp FULL = y;

7) Calculate the space required for the export job. The blocks method is used by default.

Expdp system/system directory = dumpdir full = y estimate_only = y estimate = [statistics | blocks]

8) only export the table structure

Expdp system/manager directory = dumpdir dumpfile = expdp. dmp schemas = hruser content = metadata_only

5. Import Data

1) Export to a specified user

Impdp system/manager directory = dumpdir dumpfile = expdp. dmp SCHEMAS = scott;

2) Change the table owner.

Impdp system/manager directory = dumpdir dumpfile = expdp. dmp TABLES = scott. dept REMAP_SCHEMA = scott: viin REMAP_TABLESPACE = users: pay

3) Import tablespace

Impdp system/manager directory = dumpdir dumpfile = expdp. dmp TABLESPACES = example;

4) import the database

Impdb system/manager directory = dumpdir dumpfile = expdp. dmp FULL = y;

5) append data

Impdp system/manager directory = dumpdir dumpfile = expdp. dmp SCHEMAS = system TABLE_EXISTS_ACTION = append

About TABLE_EXISTS_ACTION usage, see http://blog.csdn.net/chunhua_love/article/details/12001533

Vi. Transfer tablespace

1) verify whether the tablespace is self-contained

Execute sys. dbms_tts.transport_set_check ('users', true)

2) Place the tablespace in read-only status

Alter tablespace users read only

3) export the directory metadata of the tablespace to be migrated, and copy the exported file and the data file corresponding to the tablespace to the specified directory of the target database.

Expdp system/manager directory = dumpdir dumpfile = expdp. dmp transport_tablespaces = users

4) import and migrate tablespaces in the target database

Impdp system/manager directory = dumpdir dumpfile = expdp. dmp transport_datafiles = users01.dbf

Note:

1. The client Character Set NLS_LANG = language_territory.charset should be the same as the database character set. At least the client and target database character set should be the strict superset of the source database character set. However, Character Set conversion may occur in this case.

2. to migrate data from the 11g version to the 10g version, you must specify the parameter version = 10.2 during expdp.

3. During export and import, you can specify the job_name and logfile parameters to query the view dba_datapump_jobs and track Export and Import. After the export, check whether the log has failed or warned.

4. oracle provides the following two methods to change the owner of an object and the tablespace of the object:

Fromuser touser of exp/imp

Remap_schema remap_tablespace of expdp/impdp