oracle数据迁移之数据泵

oracle数据迁移 —— 数据泵的使用

导出数据库脚本

1. 登录数据库，输入命令：
   sqlplus sys as sysdba

2. 创建目录路径，输入命令：

   create directory data_dir as E:\orcl\data';
   --使用 select * from dba_directories; 可查询用户创建的目录

3. 为oracle用户授予访问目录的权限，输入命令：

   grant read,write on directory data_dir to pm52; --用户名：pm52

4. 导出数据库脚本（在cmd下执行）

   expdp pm52/sa@mapletr directory=data_dir dumpfile=pm52.dmp log=pm52.log

导入数据库脚本

1. 登录sysdba

   sqlplus sys@orcl/sa as sysdba

2. 创建表空间

   create tablespace MTR_DATA datafile 'C:\oradata\orcl\MTR_DATA.dbf' size 2000m autoextend on next 200m;
   create tablespace PM_DATA datafile 'C:\oradata\orcl\PM_DATA.dbf' size 2000m autoextend on next 200m;
   create tablespace MTR_LOB datafile 'C:\oradata\orcl\MTR_LOB.dbf' size 2000m autoextend on next 200m;
   create tablespace MTR_IDX datafile 'C:\oradata\orcl\MTR_IDX.dbf' size 2000m autoextend on next 200m;

3. 创建用户

   create user PM52 identified by "sa" default tablespace PM_DATA temporary
   tablespace TEMP;
   
   grant connect,resource,dba,ctxapp to PM52;
   
   grant select any table,delete any table,update any table,insert any table,create view to PM52;

4. 创建目录，并授权

   create directory dir as 'c:\db';
   grant read,write on directory dir to PM52;

5. 导入数据库

   impdp PM52/sa@orcl directory=dir remap_schema=PM52:PM52 dumpfile=PM52.DMP logfile=PM52.log;

6. 编译未通过的函数、视图、同义词

   var aa varchar2(500);
   begin 
   P_Compiledbobj(1,'PM52',:aa);
   end;
   /
   print aa;

7. 多个表空间的问题

   impdp PM52/sa@orcl directory=dir remap_schema=PM52:PM52 dumpfile=PM52.DMP logfile=PM52.log remap_tablespace='(PM_DATA:PM_DATA,PM_LOB:PM_LOB,PM_IDX:PM_IDX)';