Oracle 把一个用户的数据导入另一个用户

1. 导出数据

[exp版本]

exp user/password file=d:temp.dmp owner=user consistent=y direct=y

[expdp版本]

使用expdp之前，需要现在Oracle里面建directory

create directory dump as ’/u01/dump’;

授予用户对directory的read/write权限

grant read,write on directory dump to [username];

用户需要resource或者dba的权限，才能expdp

grant resource to [username] ;

expdp user/password dumpfile=temp.dmp directory=dump

2。导入之前，清空目标用户所有的对象

最简单的办法，把目标用户删了再重建

drop user newuser cascade;

再创建用户

//创建临时表空间

create temporary tablespace [TempTableSpaceName] tempfile ’Filename.dbf’ size 32m autoextend on next 32m maxsize 2048mextent management local;

//创建数据表空间

create tablespace [TableSpaceName] logging datafile ’Filename.dbf’ size 32m autoextend on next 32m maxsize 2048mextent management local;

//创建用户

create user [username] identified by [password] default tablespace users temporary tablespace temp;

//授予权限

grant connect,dba to [username] ;

3。导入数据

[imp版本]

imp newuser/password file=temp.dmp ignore=y fromuser=olduser touser=newuser

[impdp版本]

同样，使用impdp之前，需要现在Oracle里面建directory

create directory dump as ’/u01/dump’;

授予用户对directory的read/write权限

grant read,write on directory dump to [username];

授予用户dba权限

grant dba to [username] ;

impdp user/password dumpfile=temp.dmp directory=dump remap_tablespace=[old_table_space]:[new_table_space] table_exists_action=replace;

//导入数据后取消DBA权限

revoke dba from [username]；

//List directories

SELECT * FROM dba_directories;