sqlplus不能连接本地Oracle数据库

╰半橙微兮° 2022-07-12 06:20 327阅读 0赞

最近本地环境上安装了timesten，设置了一些环境变量，直接导致本地的sqlplus连不上数据库了，做了一些测试：

**缺失环境变量: **

** oracle\_sid ora11   
 NS\_ADMIN  F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN**  
C:\\Users\\Administrator>**sqlplus test/test**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:05:58 2017  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
ERROR:  
ORA-12560: TNS:protocol adapter error  
Enter user-name:  
  
C:\\Users\\Administrator>**sqlplus test/test@ora112**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:06:34 2017  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
ERROR:  
ORA-12154: TNS:could not resolve the connect identifier specified  
Enter user-name:  
  
  
**只缺失环境变量：   
TNS\_ADMIN  F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN**  
C:\\Users\\Administrator>**sqlplus test/test**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:19:42 2017  
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>  
  
C:\\Users\\Administrator>**sqlplus test/test@ora112**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:03:16 2017  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
ERROR:  
ORA-12154: TNS:could not resolve the connect identifier specified  
Enter user-name:  
  
  
**只缺失环境变量:   
 oracle\_sid ora11 **  
C:\\Users\\Administrator>**sqlplus test/test@ora112**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:17:36 2017  
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>  
  
C:\\Users\\Administrator>**sqlplus test/test**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:11:54 2017  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
ERROR:  
ORA-12560: TNS:protocol adapter error  
Enter user-name:   
  
  
**listener.ora中：**  
SID\_LIST\_LISTENER =  
  (SID\_LIST =  
    (SID\_DESC =  
      (SID\_NAME = CLRExtProc)  
      (ORACLE\_HOME = F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)  
    )  
  )  
    
C:\\Users\\Administrator>**sqlplus test/test**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:23:04 2017  
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>   
  
C:\\Users\\Administrator>**sqlplus test/test@ora112**  
SQL\*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 15:23:08 2017  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
ERROR:  
ORA-12514: TNS:listener does not currently know of service requested in connect  
descriptor  
  
  
**改SID\_NAME为ora11，重启监听**  
SID\_LIST\_LISTENER =  
  (SID\_LIST =  
    (SID\_DESC =  
      (SID\_NAME = CLRExtProc)  
      (ORACLE\_HOME = F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)  
    )  
  )  
两种方式都可以成功。  
  
  
**总结一下：**  
缺失TNS\_ADMIN，sqlplus test/test可以成功。  
缺失oracle\_sid，sqlplus test/test@ora112可以成功。  
缺失TNS\_ADMIN，oracle\_sid，两个都不成功。  
listener.ora SID\_NAME错误，sqlplus test/test可以成功。  
  
说明oracle\_sid对sqlplus test/test有直接影响。TNS\_ADMIN，listener.ora对sqlplus test/test@ora112有直接影响。  
  
listener.ora中的sid\_name要与tnsname.ora中的SERVICE\_NAME一致。  
listener.ora:  
SID\_LIST\_LISTENER =  
  (SID\_LIST =  
    (SID\_DESC =  
      (SID\_NAME = ora11)  
      (ORACLE\_HOME = F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1)  
      (PROGRAM = extproc)  
      (ENVS = "EXTPROC\_DLLS=ONLY:F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\bin\\oraclr11.dll")  
    )  
  )  
    
tnsname.ora:  
ORA115 =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = ora11)  
    )  
  )