OracleEXP and IMP

ExpImp is a commonly used logical backup method in the scenario where ExpImp is used. 1. Data is transmitted between two databases, which can be of different database versions and different operating systems. 2,

Exp/Imp is a commonly used logical backup method in scenarios where Exp/Imp tools are used. 1. Data is transferred between two databases. Different database versions and different operating systems are supported. 2,

Exp/Imp is a commonly used logical backup method.

Use of Exp/Imp tools

1. data transmission between two databases. Different database versions and operating systems are supported.

2. database backup and recovery

3. transfer data from one database user to another database user

4. Upload from a tablespace to a tablespace

Set the length of the displayed data in sqlplus

View the data file name and the corresponding tablespace

Create a tablespace

Datafile 'C: \ APP \ DRAGON \ ORADATA \ ZLPXB \ bank_data02.dbf'

Create a user to use the tablespace

Use bankuser to log on to the database and create a table

(

);

Commit;

View tablespaces used by users

Test exp and imp

The most export commands used in actual work

Exp userid = sys/sys @ orcl BUFFER = 8192 FILE = x: \ *. dmp LOG = x: \ *. log full = y grants = y

Most import commands used in actual work

Imp userid = sys/sys @ orcl BUFFER = 81920 FILE = x: \ *. dmp LOG = x: \ *. log full = y grants = y ignore = y

Use expdp and impdp to migrate data

Expdp and impdp can be seen as upgraded versions of exp and imp. The main differences between the two are as follows:

1. emp and imp are client tool programs, but they can be used on both the client and server.

2. expdp and impdp are tool programs on the server and can only be used on the server. The migration speed is relatively fast.

Test expdp and impdp

Use expdp and impdp in Linux

Create or replace directory expdp_dir as '/home/oracle/expdp_dir ';

Mkdir expdp_dir

Chmod 777 expdp_dir

There are multiple data pump export methods. Here we only introduce two methods:

Export a single user Solution

Expdp [user name]/[Password] @ [host escape] schemas = [user name] dumpfile = X. dmp logfile = X. log directory = expdp_dir

Export all databases

Expdp [user name]/[Password] @ [host character seek] full = y dumpfile = X. dmp logfile = X. log directory = expdp_dir

2. Data Pump Import

Export as follows:

Ø single user solution Import

Impdp [user name]/[Password] @ [host character seek] schemas = [user name] dumpfile = X. dmp logfile = X. log directory = expdp_dir ignore = y

Ø full Database Import

Impdp [user name]/[Password] @ [host character seek] full = y dumpfile = X. dmp logfile = X. log directory = expdp_dir ignore = y