1、导出Oracle 11g的数据库
Step 1
在硬盘创建存放的文件夹
C:\oracleBackup\HKHS
Step 2
在SQL Plus窗口输入用户名密码登录 后,运行SQL
create or replace directory dmp_dir as ‘C:\oracleBackup\HKHS’;
grant read,write on directory dmp_dir to public;
Step 3
在cmd窗口运行命令导出dmp文件
expdp HS-SIT-DMS/password@orcl schemas=HS-SIT-DMS directory=dmp_dir dumpfile=HS-SIT-DMS-20230712-1.dmp nologfile=Y
如下图所示:
导出成功如下图所示:
2、导入数据库到Oracle 19C
Step 1
打开Oracle 19C所在服务的SQL Plus窗口,输入用户名密码登录
Step 2
运行sql 创建一个表空间
create tablespace HS_P2_SIT_DATA datafile ‘C:\oracleTableSpace\HS_P2_SIT_DATA.DBF’ size 100m autoextend on next 100m maxsize unlimited extent management local;
Step 3
运行sql ,创建临时表空间
create temporary tablespace HS_P2_SIT_TEMP tempfile ‘C:\oracleTableSpace\HS_P2_SIT_TEMP.DBF’ size 50M autoextend ON next 50M maxsize unlimited;
Step 4
运行sql 创建一个用户,本实例中用户名为:C##HS_P2_SIT,密码为:password
create user C##HS_P2_SIT identified by password default tablespace HS_P2_SIT_DATA temporary tablespace HS_P2_SIT_TEMP profile DEFAULT;
Step 5
运行sql 赋予用户权限
grant dba to C##HS_P2_SIT with admin option;
Step 6
运行sql 创建目录
create or replace directory dmp_dir as ‘C:\oracleBackup\HKHS’;
Step 7
运行sql 授权
grant read,write on directory dmp_dir to public;
Step 8
打开cmd窗口运行命令进行导入
impdp C##HS_P2_SIT/password@orcl table_exists_action=replace directory=dmp_dir dumpfile=HS-SIT-DMS-20230712-1.DMP remap_schema=HS-SIT-DMS:C##HS_P2_SIT exclude=user
如下图所示:
Step 9
使用Step 4 创建的用户登录PL/SQL Developer,查看C##HS_P2_SIT数据库里面是否数据