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

hqbhonker 2021-08-03 原文

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

/*连接数据库*/

C:\Users\hqbhonker>sqlplus / as sysdba

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

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