Shulou(Shulou.com)06/01 Report--

The use of data pump EXPDP export tool and IMPDP import tool

Instructions for the use of EXPDP and IMPDP

Oracle Database 10g introduces the latest data pump (Data Dump) technology, data pump export and import (EXPDP and IMPDP)

1) realize logical backup and logical recovery.

2) move objects between database users.

3) move objects between databases.

4) realize the tablespace shift.

Second, the difference between data pump export import and traditional export import.

Before 10g, the traditional export and import use EXP tool and IMP tool respectively. Starting from 10g, it not only retains the original EXP and IMP tools, but also provides data pump export and import tools EXPDP and IMPDP. Things to pay attention to when using EXPDP and IMPDP:

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

2) EXPDP and IMPDP are server-side tools, they can only be used on the ORACLE server, not on the client.

3) IMP applies only to EXP export files, not EXPDP export files, and IMPDP only applies to EXPDP export files, not EXP export files.

4) data pump export includes four ways: export by table, export by user, export by tablespace, and export by database.

3. Description of EXPDP/IMPDP parameters

You can view it through expdp/impdp help=y:

$expdp help=y$ impdp help=y

IV. Actual combat cases of EXPDP

When using the EXPDP tool, the dump file can only be stored in the OS directory corresponding to the DIRECTORY object, but can not directly specify the OS directory where the dump file is located. Therefore, when using the EXPDP tool, you must first create a DIRECTORY object. And the database user needs to be granted permission to use the DIRECTORY object.

The steps are as follows:

A) Command line to open sqlplus

Sqlplus / nologconn / as sysdba

B) create a logical directory. This command does not create a real directory in the operating system (preferably manually), but preferably by an administrator such as administrator.

SQL > create directory dir as'd:\ dump';-- the dir name can be named at will. You need to create d:\ dump manually.

C) check the administrator directory (also check to see if it exists in the operating system, because Oracle does not care if the directory exists, and if not, an error occurs)

SQL > select * from dba_directories

D) give scott users the permission to operate in a specified directory, preferably by administrators such as sys,system.

SQL > grant read,write on directory dir to scott

E) pay attention to the read and write permissions of the directory directory under unix.

Change the permissions of the directory directory folder: chown-R oracle:dba / dump_dir.

1) Export the table by table

$expdp scott/tiger directory=dump_dir DUMPFILE=dept.dmp TABLES=dept

Multi-table exports are separated by commas:

$expdp scott/tiger tables=emp,dept dumpfile=expdp.dmp directory=dump_dir

2) Export by user

$expdp scott/tiger directory=dump_dir dumpfile=schema.dmp logfile=schema.log schemas=system

3) Export by tablespace

$expdp scott/tiger directory=dump_dir dumpfile=tb.dmp logfile=tb.log tablespaces=users

4) full library export

$expdp system/manager directory=dump_dir dumpfile=full.dmp full=Y;$ expdp scott/tiger directory=dump_dir dumpfile=full.dmp full=Y

Note: prompt that the scott user does not have the corresponding permissions, give the corresponding permissions to scott or use system to do the full library export.

SQL > grant exp_full_database to scott;Grant succeeded.

5) parallel process parallel export

$expdp scott/tiger@orcl directory=dump_dir dumpfile=scott3.dmp parallel=40 job_name=scott3

6) Guide according to query conditions

$expdp scott/tiger directory=dump_dir dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'

VI. Actual combat cases of IMPDP

1) Import by tabl

Delete the emp table of user scott and import emp into user scott in full.dmp

$impdp backup/backup1 directory=dump_dir dumpfile=full.dmp tables=scott.emp remap_schema=scott:scott

Import the scott.test table under the SYSTEM user

$impdp backup/backup1 directory=dump_dir dumpfile=full.dmp tables=scott.test remap_schema=scott:system

Note that if you want to import tables into other scenarios, you must specify the REMAP SCHEMA option

2) Import to the specified user

Import by user

Impdp scott/tiger@orcl DIRECTORY=dump_dir DUMPFILE=expdp.dmp SCHEMAS=scott logfile=impdp.log

Convert the object to which the scott user belongs to the system user on import

$impdp system/manager DIRECTORY=dump_dir DUMPFILE=schema.dmp SCHEMAS=scott REMAP_SCHEMA=scott:system

3) Import by tablespace

$impdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=user01

4) Import the whole library

$impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

5) the use of different expdp/impdp users and tablespaces

Assuming that user a's default tablespace is a, export all the data of user a:

SQL > conn / as sysdbaSQL > create directory dir as'/ home/oracle/';SQL > grant read,write on directory dir to system;$ expdp system/oracle directory=dir dumpfile=data.dmp schemas=a logfile=data.log

Impdp imports all the data from a user to b, and converts the tablespace a to b:

SQL > conn sys / as sysdbaSQL > create directory dir as'/ home/oracle/';SQL > grant read,write on directory dir to system;$ impdp system/oracle directory=dir dumpfile=data.dmp remap_tablespace=a:b remap_schema=a:b logfile=data.log

Explanation:

Remap_schema=a:b converts the schema of data from a to b

Remap_tablespace=a:b converts the tablespace of data from a to b

Note:

If the oracle is 10g, add the parameter EXCLUDE=TABLE_STATISTICS option to filter the table_statistics object. Otherwise, there will be a long wait for table_statistics in the data pump import.

Summary:

There is no need to create a user b when performing impdp, but will automatically create and rename user a to b (with all permissions of a, etc.), and automatically set the default tablespace to the converted tablespace b. If you have multiple tablespaces to convert, use multiple remap_tablespace= source tablespaces: target tablespaces. This method is limited to support versions above oracle10g.

6) Import a table into the database in the full library backup file

$impdp backup/backup1 directory=PUMP_DIR dumpfile=expdp.dmp include=table:\ "=\'T1\\" logfile=impdp.log table_exists_action=replace

7) Import a table in the full library backup file and convert tablespaces and owner

$impdp backup/backup1 directory=dump_dir dumpfile=expdp.dmp tables=t1 REMAP_TABLESPACE=DATA:USERS remap_schema=scott:system table_exists_action=replace

8) remote import via dblink

$impdp system/admin directory=mydir network_link=dblink16_zk schemas=lgb_zk remap_schema=lgb_zk:lgb_zk logfile=lgbzk1018.log PARALLEL=2

9) remote import through the specified parameter file through dblink

Parameter description

NETWORK_LINK=dblink

Schemas= source user

Remap_schema= source user: target user

PARALLEL=2

EXCLUDE=TABLE: a table excluded by "IN ('Achilles pommel, pachydermis, cinematorius, cinematorius, etc.)"

Remote import through parameter file

Vim mypar.parnetwork_link=dblink16schemas=zs2newremap_schema=zs2new:zs2newEXCLUDE=TABLE: "IN (" PARALLEL=2$ impdp system/admin directory=mydir parfile=h:\ dump\ mypar.par ")

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.