Oracle imports data from one user to another (data pump)

192.168.0.1--server1

1. Export data

[expdp version]

Before using expdp, you need to build directory in Oracle now.

1. Create directory dump as'/ u01CompDump`

Grant users read/write permissions on directory

2 、 grant read,write on directory dump to [wyb]

Users need the permission of resource or dba to expdp

3 、 grant resource to [wyb]

4 、 expdp wyb/123456 dumpfile= dump1.dmp logfile=expdp.log directory=dump1

192.168.0.1_server2

Clear all objects of the target user before import

The easiest way is to delete and rebuild the target user, or create a new one.

1. Drop user sam cascade; deletes tablespace data at the same time

Then create user / / create temporary tablespace / / grant permissions

2. CREATE TABLESPACE sam DATAFILE 'sam.dbf'

SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

3 、 CREATE USER sam IDENTIFIED BY 123456 DEFAULT TABLESPACE sam

4 、 grant connect,dba to [sam]

Import data

[impdp version]

Similarly, before using impdp, you need to build directory in Oracle now. If you have directory, you do not need to create

1. Create directory dump as'/ u01CompDump`

Grant users read/write permissions on directory

2 、 grant read,write on directory dump to [sam]

Grant user dba permission

Grant dba to [sam]

Impdp user/password dumpfile=temp.dmp directory=dump remap_tablespace= [old _ table_space]: [new_table_space]; old refers to the tablespace_name,new on the server1 machine refers to the server2

Impdp sam/123456 dumpfile=wyb-15032501.dmp directory=dump1 remap_tablespace=wyb:sam remap_schema=wyb:sam logfile=wyb-15032501.log

/ / cancel the DBA permission after importing the data

Revoke dba from [sam]

/ / List directories

SELECT * FROM dba_directories

If tablespace exists, delete the tablespace first

DROP TABLESPACE xxb0001 INCLUDING CONTENTS AND DATAFILES