Oracle 导入本地dmp文件 详细操作步骤

 

以下操作均在命令行窗口中进行

/*连接数据库*/

C:\Users\hqbhonker>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 20 11:23:42 2013

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

Connected to:
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 zhaohang datafile 'd:\1_workspace\zhaohang.dbf' size 500m autoextend on next 100m extent management local;

/*创建用户*/
SQL> create user zhaohang identified by "1" default tablespace zhaohang;

/*赋予用户权限*/
SQL> grant resource,connect,dba to zhaohang;

/*测试用户是否创建成功*/
SQL> connect zhaohang/1

/*改变用户默认表空间，

导入本地dmp文件前，必须搞清楚原有的表空间，如果与上面新创建的表空间不一致，导入时，会报错，

这时，就需要重新创建一个同名表空间，然后，再改变上面新创建用户的默认表空间，如下*/
SQL> ALTER user zhaohang default tablespace YSSQDII;

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

/*导入本地dmp文件*/

C:\Users\hqbhonker>imp zhaohang/1 file='C:\Users\hqbhonker\Desktop\CMBCHINA20130606.dmp' full=y

以用户身份导入数据

（imp 用户名/密码@数据库实例名  file=需要导入的dmp文件的路径及文件名  log=指定导入日志路径及文件名  full=y ）

以DBA身份导入数据

（imp '用户名/密码@数据库实例名 as sysdba'  file=需要导入的dmp文件的路径及文件名  log=指定导入日志路径及文件名  full=y ）

　事例：imp 'sys/p2p@p2p_dev as sysdba' file='d:\P2PData.dmp' log='d:\P2PData.log' full=y

注意：此语句不是SQL语句，结尾没有分号。

下面是完整片段，可直接复制

 

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\hqbhonker>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 20 12:28:25 2013

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


Connected to:
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 QDII datafile 'd:\1_workspace\QDHuaiTai.dbf' size 2000m autoextend on next 100m extent management local;

Tablespace created.

SQL> create user huaitai identified by "1" default tablespace QDII;

User created.

SQL> grant resource,connect,dba to huaitai;

Grant succeeded.

SQL> connect huaitai/1
Connected.
SQL> exit
Disconnected from 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\hqbhonker>imp huaitai/1 file='C:\Users\hqbhonker\Desktop\qdiifull20130909.dmp' full=y