Oracle 本地 数据泵导入导出数据

删除用户 : drop user scnoa cascade;
cmd中连接oracle : sqlplus / as sysdba;

导出

1.创建目录

create directory scnoa as 'd:/dump';

2.给用户授权目录

grant read,write on directory scnoa to scnoa;

3.exit退出后，导出数据

expdp scnoa/scnoa directory=scnoa dumpfile=scnoaxxxx.dmp logfile=scnoaxxxx.log

导入

①创建临时表空间

SQL> create temporary tablespace SCNOA_DATA_temp tempfile'D:\app\HYUY\oradata\orcl\SCNOA_DATA_temp.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local;

②创建数据表空间

SQL> create tablespace SCNOA_DATA logging datafile'D:\app\HYUY\oradata\orcl\SCNOA_DATA01.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local;

③创建数据库用户并指定表空间

SQL> create user scnoa identified by scnoa default tablespace SCNOA_DATA temporary tablespace SCNOA_DATA_temp;

④赋予用户权限

grant dba,imp_full_database,exp_full_database,connect,resource,create session to scnoa;

⑤ 给路径赋予权限

create directory scnoa_dir as 'D:/zhanghaoUser/ruaho';
grant read,write on directory scnoa_dir to scnoa;

⑥ 导入

impdp scnoa/scnoa directory=scnoa_dir dumpfile=SCNOA20190924.dmp logfile=SCNOA20190924.log;

借鉴：https://www.iteye.com/blog/yuwenlin-2213736