Oracle Database migration tutorial in heterogeneous environments: Use of exp and imp, expdp and impdp, expdpimpdp

Oracle Database migration tutorial in heterogeneous environments: Use of exp and imp, expdp and impdp, expdpimpdp

Exp/imp can be used in the following scenarios

Transfer Data between two databases

1. versions of the same oracle database

2. Versions of different oracle databases

3. oracle databases between the same or different operating systems

Used for database backup and recovery

Transfer from one database user to another

Transfer from one tablespace to another

Create a tablespace

Create tablespace bank_tbs (Database tablespace) datafile '/oracle/test (data path) bank_data02.dbf (data file name) 'size 5 M autoextend on maxsize 10G;

Specify the tablespace when creating a user

Create user bankuser identified by bankpwd default tablespace bank_tbs;

Use export to export data

Exp bankuser/bankpwd file = '/backup/bankuser. dmp' log = '/backup/bankuserExp. log' format: exp user name/password file = export path and file name

Import data using import

Imp bankuser/bankpwd file =/backup/bankuser. dmp full = y pay attention to the following full = y, which means all imports, you can also specify the table name of a table

To achieve better results, we can first create a table with the bankuser user, then exp, and then go to sqlplus to delete the table. Finally, imp will find that the database has been restored.

If it is in a windows environment, it seems that you still need to add the @ Database Name and ORACLE_SID after the user name/Password

Use expdp to back up the database

Run the command in sqlplus and log on with sysdba because grant permission is involved.

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

Grant read, write on directory to dbauser;

Exit sqlplus and go to the oracle Home Directory

Create a folder

Mkdir expdp_dir

Modify permissions

Chmod 777 expdp_dir

Export data

Expdp dbauser/123456 directory = expdp_dir (absolute path no longer needed here) dumpfile = backuptest. dmp logfile = backuptestlog. log

Import Data

Impdp dbauser/123456 directory = expdp_dir (absolute path no longer needed here) dumpfile = backuptest. dmp logfile = backuptestlog. log