exp-imp实现oracle不同表空间的迁移



--1 在迁徙目标机上新建
create tablespace tzsb datafile 'I:\app\Administrator\oradata\orcl\tzsb01.dbf' size 500m
create user wgm_tzsb identified by pwd123 default tablespace tzsb temporary tablespace temp quota unlimited on tzsb account unlock;
grant create session to wgm_tzsb;
grant create table to wgm_tzsb;
grant create procedure to wgm_tzsb;
grant create sequence to wgm_tzsb;
grant create view to wgm_tzsb;

--2 在源机上修改表空间名称(账号：sys/oracle@orcl as SYSDBA 登录)

alter tablespace EXAMPLE rename to TZSB;

--2、导出用户

exp WGM_TZSB/PWE123@ORCL file=ORCL.dmp log=ORCL-exp.log

--此dmp文件表空间现在应该为TZSB

--3、将表空间名称修改回去(账号：sys/oracleas SYSDBA 登录)

alter tablespace TZSB rename to EXAMPLE;

--4、在目标机上导入到新创建用户WGM_TZSB（默认表空间为TZSB,临时表空间为TEMP）

imp  WGM_TZSB/PWD123@ORCL  file=c:\ORCL.dmp full=y

至此所有数据迁移至新的表空间TZSB。