CMD命令实现dmp文件导入Oracle数据库

Created by winston on June 22, 2020.

---

CMD命令实现dmp文件导入Oracle数据库

强调说明：很多时候连接不上oracle数据库，用户可能是sysdba用户！！！【此处贼坑！！！】

1、连接本地Oracle DBA用户：

C:\Users\isLimf>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 18:15:28 2020

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

Enter user-name: sys
Enter password:

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>

（1）sqlplus

（2）输入用户名：sys

（3） 输入密码：root as sysdba [说明：as sysdba必须输入]

2、连接远程Oracle DBA用户：

D:\OracleNew\InstallData\product\11.2.0>sqlplus winston/winston@192.168.3.40:1521/orcl

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:02:11 2020

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: winston
Enter password:

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> 

（1）进入目录D:\OracleNew\InstallData\product\11.2.0 ——>执行cmd命令

sqlplus winston/winston@192.168.3.40:1521/orcl

（2）会报错，再次输入用户名：winston

（3）再次输入密码：winston as sysdba

3、dmp文件导入至远程数据库：

（1）创建表空间

create tablespace db_tablespace 
datafile 'D:\db_tablespace.DBF'
size 2000m 
autoextend on 
next 1000m 
maxsize unlimited;

create temporary tablespace temp_tablespace  
tempfile 'D:\temp_tablespace.DBF'
size 2000m 
autoextend on 
next 1000m 
maxsize unlimited;

（2）创建用户、授权

SQL> create user jack identified by "jack" default tablespace db_tablespace temporary tablespace temp_tablespace;
User created.
SQL> grant connect,resource,dba to jack;
Grant succeeded.
SQL> commit;

（3）新用户连接测试

SQL> conn jack
Enter password:
Connected.

（4）导入远程数据库

/**方式1**/
D:\OracleNew\InstallData\product\11.2.0>imp 'winston/winston@192.168.3.40:1521/orcl as sysdba' file=C:/Users/isLimf/Desktop/jack.dmp fromuser=jack touser=jack statistics=none

/**方式2**/
D:\OracleNew\InstallData\product\11.2.0>imp jack/jack@192.168.3.40:1521/orcl file=C:/Users/isLimf/Desktop/jack.dmp full=y

Import: Release 11.2.0.1.0 - Production on Mon Jun 22 17:55:44 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing BCCMS's objects into BCCMS
. . importing table                 "AT_WF_ACTION"          0 rows imported
. . importing table                   "AT_WF_INFO"          0 rows imported
. . importing table               "AT_WF_NODEINFO"          0 rows imported
. . importing table                "AT_WF_PROCESS"          0 rows imported
. . importing table                "BC_CMS_ATTACH"        603 rows imported
. . importing table               "BC_CMS_CHANNEL"         34 rows imported
. . importing table               "BC_CMS_COMMENT"          0 rows imported
. . importing table                  "BC_CMS_INFO"        621 rows imported
. . importing table                "BC_CMS_NOTICE"          0 rows imported
. . importing table            "BC_CMS_RELATEINFO"          0 rows imported
. . importing table                  "BC_CMS_SITE"          1 rows imported
. . importing table              "BC_CMS_TEMPLATE"          0 rows imported
. . importing table                  "BC_SYS_USER"          1 rows imported
Import terminated successfully without warnings.

4、命令补充

（1）Oracle数据库重启：

登录数据库后，使用如下命令操作：
shutdown immediate;	[等待执行结果，提示数据库关闭]
startup;	[然后执行该命令启动数据库]
-----------------------------------------------------------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size                  2180464 bytes
Variable Size            2080377488 bytes
Database Buffers         1291845632 bytes
Redo Buffers               16154624 bytes
Database mounted.
Database opened.

（2）用户占用

no.1 查询Oracle用户名
SQL>  select user#,name,password from user$ where name ='jack';
USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
81 jack
no.2 删除用户名
SQL> delete from user$ where name='jack';
SQL> commit;

no.3 创建同样的Oracle用户名
SQL> create user jack identified by "jack";
create user jack identified by "jack"
*
ERROR at line 1:
ORA-01920: user name 'jack' conflicts with another user or role name

这时系统会提示“用户名冲突”，运行下面的SQL语句
SQL> alter system checkpoint;    ----强制写入数据文件  
System altered.  
SQL> alter system flush shared_pool;  ----清楚缓存数据字典信息，
----强制oracle读实际数据（即更改后的数据）  
System altered.

no.4 在重新创建相同的用户名
SQL> create user jack identified by "jack";
User created.
SQL> grant connect,resource,dba to jack;
SQL> commit;

5、神坑记录

	sqlplus winston/winston@192.168.3.40:1521/orcl 使用该命令访问远程数据库后，使用该账户创建新用户，会把用户一直创建到本地oracle库中。
	解决方式：可使用远程已有并可以连接的sysdba角色账户用navicat连接，然后在navicat中新建查询，执行创建用户命令，用户授权命令，最后commit，神功告成。(此处创建用户会创建至本地，有了解的可以留言相互学习。)