Oracle导入数据库impdp/imp命令使用详细过程

登录

以oracle DBA身份登录，登录操作参考：sqlplus命令行登录oracle数据库的N种方法盘点；

创建表空间表空间

## 数据表空间
create tablespace JW_TABLESPACE
datafile 'D:\Programs\oracle\oracle11g\oradata\orcl\JW_TABLESPACE_DATA.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

## 临时表空间

create temporary tablespace JW_TABLESPACE_TEMP
tempfile 'D:\Programs\oracle\oracle11g\oradata\orcl\JW_TABLESPACE_TEMP.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

创建用户并指定表空间

CREATE USER lzda IDENTIFIED BY "lzda"
DEFAULT TABLESPACE JW_TABLESPACE
TEMPORARY TABLESPACE JW_TABLESPACE_TEMP;

给用户赋权

GRANT CREATE USER,DROP USER,ALTER USER,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO  lzda; 

创建目录

创建目录、查看目录等操作，请参考：Oracle创建目录、授权、查看所有目录；

进行导入

impdp lzda/[email protected] directory=TEMP_DIR dumpfile=lzda_test.dmp schemas=lzda

或

imp lzda/[email protected] file=D:\dir_oracle\lzda_test.dmp fromuser=LZDA_TEST touser=lzda

注： 如果使用imp命令导入报错IMP-00032，需要在后面加上buffer=819200参数，即imp lzda/[email protected] file=D:\dir_oracle\lzda_test.dmp fromuser=LZDA_TEST touser=lzda buffer=819200。

完整示例：

C:\Users\4spaces.org> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 5月 23 09:12:28 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace JW_TABLESPACE
  2  datafile 'D:\Programs\oracle\oracle11g\oradata\orcl\JW_TABLESPACE_DATA.dbf'
  3  size 50m
  4  autoextend on
  5  next 50m maxsize 20480m
  6  extent management local;

表空间已创建。

SQL> create temporary tablespace JW_TABLESPACE_TEMP
  2  tempfile 'D:\Programs\oracle\oracle11g\oradata\orcl\JW_TABLESPACE_TEMP.dbf'
  3  size 50m
  4  autoextend on
  5  next 50m maxsize 20480m
  6  extent management local;

表空间已创建。

SQL> CREATE USER lzda IDENTIFIED BY "lzda"
  2  DEFAULT TABLESPACE JW_TABLESPACE
  3  TEMPORARY TABLESPACE JW_TABLESPACE_TEMP;

用户已创建。

SQL> GRANT CREATE USER,DROP USER,ALTER USER,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO  lzda;

授权成功。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\4spaces.org>impdp lzda/[email protected] directory=TEMP_DIR dumpfile=lzda_test.dmp schemas=lzda