-- 源端数据库版本 11.2.0.4
迁移用户:SCOTT
-- 目标端数据库版本 12.2.0.1.0
一、查看源库信息
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT TBS1 TEMP
-- 查看表空间下有哪些表
select TABLE_NAME from tabs where TABLESPACE_NAME = 表空间名;
-- 索引数
select count(*) from dba_indexes where owner='SCOTT';
COUNT(*)
----------
2
-- 表数
select count(*) from dba_tables where owner='SCOTT';
二、源端导出 SCOTT 用户数据
-- 创建恢复目录
create directory dmp11g_dir as '/orcl/app/dmp_bk';
-- 导出数据
expdp scott/tiger@orcl schemas=scott dumpfile=scott_dp.dmp DIRECTORY=dmp11g_dir;
三、创建一个新的pdb
-- 建pdb
CREATE PLUGGABLE DATABASE SCOTT ADMIN USER pdbadmin IDENTIFIED BY qwer12345 ROLES=(CONNECT);
-- 创建表空间
create tablespace TBS1 datafile '/orcl/app/oracle/oradata/orcl/TBS1.dbf' size 50m AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
alter database tempfile 5 resize 1G
四、目标端数据导入
-- 创建恢复目录
mkdir /orcl/app/dmp_bk
-- 传包:
scp oracle@192.168.3.116:/orcl/app/dmp_bk/scott_dp.dmp /orcl/app/dmp_bk
-- 启动scott pdb
alter pluggable database SCOTT open;
-- 切换到scott pdb下
alter session set container = SCOTT;
-- 创建导入目录
create directory dmp11g_dir as '/orcl/app/dmp_bk';
五、传教恢复用户
create user impdp identified by impdp account unlock;
-- 授权恢复用户
grant read,write on directory dmp11g_dir to impdp;
grant dba to impdp;
六、测试登录
sqlplus impdp/impdp@192.168.3.10:1521/SCOTT
SQL> show user;
USER is "IMPDP"
七、数据导入
impdp impdp/impdp@192.168.3.10:1521/SCOTT DIRECTORY=dmp11g_dir logfile=scott_imp.log DUMPFILE=scott_dp.dmp SCHEMAS=scott;
-- 删除导入的用户
drop user impdp cascade;
数据泵迁移11g用户数据至12c
于 2021-05-19 13:20:32 首次发布