oracle导入expdp、导出impdp数据库用户

仅限oracle服务器上执行；把172.16.251.136:1521/orcl的AMI4_2用户导入到192.168.2.30:1521/orclss中的AMI1用户；

 关于导入导出更详细的见文章《数据备份与恢复》

导出：

创建对应的文件夹用于放dmp文件（或者借用别的文件夹用一下，直接赋予用户文件夹权限即可）；

-- 执行该命令之前，要首先创D:\xinsushedmp\对应的文件夹

create directory DUMP_DIR_D as 'D:\xinsushedmp\'

-- 查询数据库目录，新创建的文件夹应该要在里面（需要使用管理员用户才能看到）

select * from dba_directories; 

-- 使用sys用户给要导出的用户文件夹修改和读取权限

grant read, write on directory DUMP_DIR_D to AMI4_2;

-- 要导出的用户和密码：AMI4_2/AMI4_2

-- 服务器地址：端口/实例名

-- 导出文件夹目录：DUMP_DIR_D 

-- 导出的dmp文件，也就是整个用户的数据：laosushe.dmp

-- 日志文件：laosushe.log

expdp  AMI4_2/AMI4_2@172.16.251.136:1521/orcl  directory=DUMP_DIR_D dumpfile=laosushe.dmp logfile=laosushe.log

 

导入：

跟导出同样赋予文件夹权限，将导出的.dmp文件放入该文件夹下；

impdp AMI1/AMI1@192.168.2.30:1521/orclss dumpfile=LAOSUSHE.DMP REMAP_SCHEMA=AMI4_2:AMI1 directory=DATA_PUMP_DIR  full=y

 

如果AMI1用户没有新建表空间的话，需要单独建立表空间。

我是使用sys用户登录192.168.2.30:1521/orclss执行的建表空间语句

建表空间参考语句：

create tablespace AMI logging datafile 'D:\oradata\orclss\AMI.dbf' size 2048m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_IDX logging datafile 'D:\oradata\orclss\AMI_IDX.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_LOG logging datafile 'D:\oradata\orclss\AMI_LOG.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_LOG_IDX logging datafile 'D:\oradata\orclss\AMI_LOG_IDX.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_SB logging datafile 'D:\oradata\orclss\AMI_SB.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_SB_IDX logging datafile 'D:\oradata\orclss\AMI_SB_IDX.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_TEMP logging datafile 'D:\oradata\orclss\AMI_TEMP.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_TEMP_IDX logging datafile 'D:\oradata\orclss\AMI_TEMP_IDX.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_TJ logging datafile 'D:\oradata\orclss\AMI_TJ.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
 create tablespace AMI_TJ_IDX logging datafile 'D:\oradata\orclss\AMI_TJ_IDX.dbf' size 1024m  autoextend on next 32m maxsize unlimited extent management local;
-- Create the user
--create user AMI1
 -- identified by AMI1
 
alter user AMI1
  default tablespace AMI
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on ami
  quota unlimited on ami_idx
  quota unlimited on ami_log
  quota unlimited on ami_log_idx
  quota unlimited on ami_sb
  quota unlimited on ami_sb_idx
  quota unlimited on ami_temp
  quota unlimited on ami_temp_idx
  quota unlimited on ami_tj
  quota unlimited on ami_tj_idx
  quota unlimited on users;
-- Grant/Revoke role privileges
grant execute on DBMS_LOCK to AMI1;
grant connect to AMI1;
grant resource to AMI1;
-- Grant/Revoke system privileges
grant create any table to AMI1;
grant create view to AMI1;
grant debug any procedure to AMI1;
grant debug connect session to AMI1;
grant drop any table to AMI1;
grant execute any procedure to AMI1;
grant select any dictionary to AMI1;
grant create any job to AMI1;
grant unlimited tablespace to AMI1;
grant select any table to AMI1;