Oracle expdp/impdp Export Import command and database backup)

Precautions when using expdp and impdp:

Exp and IMP are client toolsProgramThey 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 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 Scott/tiger @ orcl schemas = Scott dumpfile = expdp. dmp directory = dpdata1;

2) parallel process parallel

Expdp Scott/tiger @ orcl directory = dpdata1 dumpfile = scott3.dmp parallel = 40 job_name = scott3

3) import by table name

Expdp Scott/tiger @ orcl tables = EMP, DEPT dumpfile = expdp. dmp directory = dpdata1;

4) export by query Conditions

Expdp Scott/tiger @ orcl directory = dpdata1 dumpfile = expdp. dmp tables = EMP query = 'where deptno = 20 ';

5) export by tablespace

Expdp system/manager directory = dpdata1 dumpfile = tablespace. dmp tablespaces = temp, example;

6) import the entire database

Expdp system/manager directory = dpdata1 dumpfile = full. dmp full = y;

V. Restore data

1) Export to a specified user

Impdp Scott/tiger directory = dpdata1 dumpfile = expdp. dmp schemas = Scott;

2) Change the table owner.

Impdp system/manager directory = dpdata1 dumpfile = expdp. dmp tables = Scott. Dept remap_schema = SCOTT: system;

3) Import tablespace

Impdp system/manager directory = dpdata1 dumpfile = tablespace. dmp tablespaces = example;

4) import the database

Impdb system/manager directory = dump_dir dumpfile = full. dmp full = y;

5) append data

Impdp system/manager directory = dpdata1 dumpfile = expdp. dmp schemas = system table_exists_actIon

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 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 Scott/tiger @ orcl schemas = Scott dumpfile = expdp. dmp directory = dpdata1;

2) parallel process parallel

Expdp Scott/tiger @ orcl directory = dpdata1 dumpfile = scott3.dmp parallel = 40 job_name = scott3

3) import by table name

Expdp Scott/tiger @ orcl tables = EMP, DEPT dumpfile = expdp. dmp directory = dpdata1;

4) export by query Conditions

Expdp Scott/tiger @ orcl directory = dpdata1 dumpfile = expdp. dmp tables = EMP query = 'where deptno = 20 ';

5) export by tablespace

Expdp system/manager directory = dpdata1 dumpfile = tablespace. dmp tablespaces = temp, example;

6) import the entire database

Expdp system/manager directory = dpdata1 dumpfile = full. dmp full = y;

V. Restore data

1) Export to a specified user

Impdp Scott/tiger directory = dpdata1 dumpfile = expdp. dmp schemas = Scott;

2) Change the table owner.

Impdp system/manager directory = dpdata1 dumpfile = expdp. dmp tables = Scott. Dept remap_schema = SCOTT: system;

3) Import tablespace

Impdp system/manager directory = dpdata1 dumpfile = tablespace. dmp tablespaces = example;

4) import the database

Impdb system/manager directory = dump_dir dumpfile = full. dmp full = y;

5) append data

Impdp system/manager directory = dpdata1 dumpfile = expdp. dmp schemas = system table_exists_actIon