当前位置：文江博客话题详情

SQL*加上与命令提示符连接到DB的问题

发布于 2025-02-01 17:37:52 字数 3229 浏览 2 评论 0原文

我在SQL*Plus上：Windows机器上的版本21.0.0.0.0。

在命令提示中，当我这样做时

c：\ users \ tommy＆gt; sqlplus /nolog < /p>

我看到了

sql＆gt;

使用连接字符串（EZ Connect Format）连接到数据库。

在这里，我使用凭据，

用户名 - ＆gt; dddd

pwd - ＆gt; wefm@vrev

主机 - ＆gt;本地

端口 - ＆GT; 15210

SERVICENAME-＆GT; abcd.xysp.gthe.com

sql＆gt;连接dddd/“ wefm@vrev” @local：15210/abcd.xysp.gthe.com

，我看到了输出（几秒钟后），

连接。

当我决定直接从Windows连接SQLPLU时，我的问题就开始了。下面提到的

c：\ users \ tommy＆gt; sqlplus -s dddd/“ wefm@vrev” @local：15210/abcd.xysp.gthe.com

导致错误，我感到困惑。我想念什么

错误：ORA-12154：TNS：无法解析连接标识符指定

我还尝试将连接字符串放入单引号（'）中。当我这样做时，我将这一切打印出来，我认为这表明我经过错误的args。

sql*plus：版本21.0.0.0.0-生产版本21.3.0.0.0

使用SQL Plus执行SQL，PL/SQL和SQL Plus语句。

用法1：sqlplus -h | -v

  -h显示SQL*Plus版本和
               使用帮助。
-v显示SQL*Plus版本。

用法2：sqlplus [[] [{登录| /nolog}] []]

IS：[-c] [-f] [-l] [-m“”] [-Nogologintime] [-r] [-s]

  -c＆lt;版本＆gt;将受影响命令的兼容性设置为
               版本由＆lt;版本＆gt;。版本具有
               形式“ xy [.z]”。例如，-c 10.2.0
-f此选项一般可以提高性能。它改变了
               默认值设置。
               有关详细设置，请参见SQL*Plus用户指南。
-l尝试仅登录一次，而不是
               提示错误。
-m“＆lt;选项＆gt;”设置自动HTML或输出的CSV标记。选项
               有形式：
               {html html_options | csv csv_options}
               有关详细的HTML和CSV选项，请参见SQL*Plus用户指南。
-Nogologintime不会显示最后一个成功的登录时间。
-r＆lt; late＆gt;将限制模式设置为禁用SQL*Plus命令
               与文件系统交互。水平可以
               是1、2或3。最限制的是-r 3
               禁用所有用户命令与
               文件系统。
-s设置静音模式，以抑制
               SQL*加上横幅，提示和回声
               命令。

is：{[/] [@＆lt; connect_identifier＆gt;] | /} [as {sysdba | Sysoper | sysasm | sysbackup | sysdg | syskm | sysrac}] [Edition = value]

 指定数据库帐户用户名，密码和连接
数据库连接的标识符。没有连接
标识符，SQL*Plus连接到默认数据库。

AS sysdba，作为sysoper，sysasm，sysbackup，as sysdg，
作为syskm和Sysrac选项是数据库管理特权。

＆lt; connect_Identifier＆gt;可以是净服务名称的形式
或容易连接。

  @[＆lt; net_service_name＆gt; | [[//]主机[：port]/＆lt; service_name＆gt;] |
    [[[协议：] //] host1 {，host12} [：port1] {，host2：port2} [/service_name]
     [：server] [/实例名称] [？[参数名称= value]
     {＆amp;参数名称= value}]]]]]

    ＆lt; net_service_name＆gt;是解决方案的服务的简单名称
    到连接描述符。

    示例：使用NET服务名称和
             数据库Net服务名称为ORCL。

       sqlplus用户名/myPassword@orcl

    主机指定数据库的主机名或IP地址
    服务器计算机。

    端口指定数据库服务器上的侦听端口。

    ＆lt;服务名称＆gt;指定数据库的服务名称
    想访问。

    示例：使用Easy Connect和
             服务名称为ORCL。

       sqlplus用户名/mypassword@host/orcl

/nolog选项启动sql*plus而不连接到
数据库。

该版本指定了会话版的值。

is： @| [。] [...]

 运行来自Web服务器（URL）或
本地文件系统（filename.ext），带有指定参数
将分配给脚本中的替换变量。

当SQL plus启动时，在连接命令之后，网站配置文件（例如$ oracle_home/sqlplus/admin/glogin.sql）和用户配置文件（例如，在工作目录中的登录sql）运行。文件可能包含sql plus命令。

有关更多信息，请参阅SQL*Plus用户指南和参考。

我想要一个仅涉及使用连接字符串的解决方案。我不想使用tns_ora文件也没有任何手动干预。放入“用户名@Connect-Identifier”之后，我不想单独输入密码。

请忽略与安全性相关的问题，即在CMD提示符下可见密码传递。我很好。

原文

I am on SQL*Plus: Release 21.0.0.0.0, on a Windows machine.

In the command prompt, when I do

C:\users\TOMMY> sqlplus /nolog

I see this

SQL>

Here I connect to the Database using a connection string(EZ Connect format)

With Credentials,

username -> dddd

pwd -> wefm@vrev

host -> local

port -> 15210

ServiceName -> abcd.xysp.gthe.com

SQL> connect dddd/"wefm@vrev"@local:15210/abcd.xysp.gthe.com

And I see Output(after a few seconds),

Connected.

My issue begins when I decide to connect from Windows directly with sqlplus. Mentioned below

C:\users\TOMMY> sqlplus -S
dddd/"wefm@vrev"@local:15210/abcd.xysp.gthe.com

Results in an error, I am baffled. What am I missing

ERROR: ORA-12154: TNS:could not resolve the connect identifier
specified

I have also tried putting the connection string in single quotes (').
When I do that I get this whole thing printed out, which I assume is indicative of me passing the wrong args.

SQL*Plus: Release 21.0.0.0.0 - Production Version 21.3.0.0.0

Use SQLPlus to execute SQL, PL/SQL and SQLPlus statements.

Usage 1: sqlplus -H | -V

-H             Displays the SQL*Plus version and the
               usage help.
-V             Displays the SQL*Plus version.

Usage 2: sqlplus [ [] [{logon | /nolog}] [] ]

is: [-C ] [-F] [-L] [-M ""]
[-NOLOGINTIME]
[-R ] [-S]

-C <version>   Sets the compatibility of affected commands to the
               version specified by <version>.  The version has
               the form "x.y[.z]".  For example, -C 10.2.0
-F             This option improves performance in general. It changes
               the default values settings.
               See SQL*Plus User's Guide for the detailed settings.
-L             Attempts to log on just once, instead of
               prompting on error.
-M "<options>" Sets automatic HTML or CSV markup of output.  The options
               have the form:
               {HTML html_options|CSV csv_options}
               See SQL*Plus User's Guide for detailed HTML and CSV options.
-NOLOGINTIME   Don't display Last Successful Login Time.
-R <level>     Sets restricted mode to disable SQL*Plus commands
               that interact with the file system.  The level can
               be 1, 2 or 3.  The most restrictive is -R 3 which
               disables all user commands interacting with the
               file system.
-S             Sets silent mode which suppresses the display of
               the SQL*Plus banner, prompts, and echoing of
               commands.

is: {[/][@<connect_identifier>] | / }
[AS {SYSDBA | SYSOPER | SYSASM | SYSBACKUP | SYSDG
| SYSKM | SYSRAC}] [EDITION=value]

Specifies the database account username, password and connect
identifier for the database connection.  Without a connect
identifier, SQL*Plus connects to the default database.

The AS SYSDBA, AS SYSOPER, AS SYSASM, AS SYSBACKUP, AS SYSDG,
AS SYSKM and AS SYSRAC options are database administration privileges.

<connect_identifier> can be in the form of Net Service Name
or Easy Connect.

  @[<net_service_name> | [[//]Host[:Port]/<service_name>] |
    [[[protocol:]//]host1{,host12}[:port1]{,host2:port2}[/service_name]
     [:server][/instance name][?[parameter name=value]
     {¶meter name=value}]]]

    <net_service_name> is a simple name for a service that resolves
    to a connect descriptor.

    Example: Connect to database using Net Service Name and the
             database net service name is ORCL.

       sqlplus username/mypassword@ORCL

    Host specifies the host name or IP address of the database
    server computer.

    Port specifies the listening port on the database server.

    <service name> specifies the service name of the database you
    want to access.

    Example: Connect to database using Easy Connect and the
             Service name is ORCL.

       sqlplus username/mypassword@Host/ORCL

The /NOLOG option starts SQL*Plus without connecting to a
database.

The EDITION specifies the value for Session Edition.

is: @|[.] [ ...]

Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.

When SQLPlus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQLPlus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

I would like a solution that only involves using a connection string. I don't want to be using tns_ora file
Also without any manual intervention. I don't want to separately enter the password after I put in 'username@connect-identifier'.

Please ignore security related issues of passing in the password visibly in the cmd prompt. That is fine with me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

别挽留 2025-02-08 17:37:52

解决方案powershell脚本test-connect2.ps1：

$username = "demin"
$password = '"test@test"'
$connect_string ="10.241.33.71:1521/DEV"
$log_file = "log.txt"

$list_scripts = "connect $username/$password@$connect_string"
$list_scripts = $list_scripts + " 
select 1,sysdate from dual;
@file1.sql
@file2.sql"
$list_scripts | sqlplus -s /nolog  >>$log_file

运行脚本：

C:\instantclient_21_3>powershell .\test-connect2.ps1

输出log.txt

     1 SYSDATE
---------- ---------
     1 27-MAY-22


     2 SYSDATE
---------- ---------
     2 27-MAY-22


     3 SYSDATE
---------- ---------
     3 27-MAY-22


SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:34:41 2022
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
SQL> alter user DEMIN identified by "test@test";
User altered.
SQL> exit    
    
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
C:\>sqlplus demin/"test@test"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:14 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
    
C:\>sqlplus demin/\"test@test\"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:23 2022
           
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
            
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
            
SQL> show user
USER is "DEMIN"
SQL>

C:\app\product\18.0.0\dbhomeXE\bin>sqlplus demin/\"test@test\"@dev19                                                                                                                                               
SQL*Plus: Release 18.0.0.0.0 - Production on Fri May 27 10:28:18 2022
Version 18.4.0.0.0

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

Last Successful login time: Fri May 27 2022 06:35:29 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

C:\instantclient_19_15>sqlplus demin/\"test@test\"@dev19                                                                                                                                                           
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 27 11:23:35 2022
Version 19.15.0.0.0

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

Last Successful login time: Fri May 27 2022 10:31:49 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>


C:\instantclient_21_3>sqlplus demin/\"test@test\"@dev19                                                                                                                                                            
SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 27 11:18:08 2022
Version 21.3.0.0.0
    
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution powershell script test-connect2.ps1:

$username = "demin"
$password = '"test@test"'
$connect_string ="10.241.33.71:1521/DEV"
$log_file = "log.txt"

$list_scripts = "connect $username/$password@$connect_string"
$list_scripts = $list_scripts + " 
select 1,sysdate from dual;
@file1.sql
@file2.sql"
$list_scripts | sqlplus -s /nolog  >>$log_file

Run script:

C:\instantclient_21_3>powershell .\test-connect2.ps1

output log.txt

     1 SYSDATE
---------- ---------
     1 27-MAY-22


     2 SYSDATE
---------- ---------
     2 27-MAY-22


     3 SYSDATE
---------- ---------
     3 27-MAY-22


SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:34:41 2022
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
SQL> alter user DEMIN identified by "test@test";
User altered.
SQL> exit    
    
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
C:\>sqlplus demin/"test@test"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:14 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
    
C:\>sqlplus demin/\"test@test\"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:23 2022
           
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
            
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
            
SQL> show user
USER is "DEMIN"
SQL>

C:\app\product\18.0.0\dbhomeXE\bin>sqlplus demin/\"test@test\"@dev19                                                                                                                                               
SQL*Plus: Release 18.0.0.0.0 - Production on Fri May 27 10:28:18 2022
Version 18.4.0.0.0

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

Last Successful login time: Fri May 27 2022 06:35:29 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

C:\instantclient_19_15>sqlplus demin/\"test@test\"@dev19                                                                                                                                                           
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 27 11:23:35 2022
Version 19.15.0.0.0

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

Last Successful login time: Fri May 27 2022 10:31:49 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>


C:\instantclient_21_3>sqlplus demin/\"test@test\"@dev19                                                                                                                                                            
SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 27 11:18:08 2022
Version 21.3.0.0.0
    
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

回复收藏 0 原文

~没有更多了~