Casivio's questions

我刚刚安装了 Oracle 19c 和 SQL Developer。我打开 SQL Developer 并尝试建立一个新连接，但我得到了

“侦听器拒绝连接并出现以下错误：ORA-12505，TNS：侦听器当前不知道连接描述符中给出的 SID (CONNECTION_ID=HYjeJ...==)”。

我正在尝试与

Username: SYS
Role: SYSDBA
Hostname: localhost
SID: xe (this is the default, I know nothing about it)

我尝试使用“ALTER USER SYS IDENTIFIED BY oracle ACCOUNT UNLOCK;”更改 SYS 用户密码 正如这里推荐的那样。我只能使用 SYS 作为 SYSDBA 登录 SQL Plus，并且没有密码。我试图创建一个用户并设置他们的个人资料以允许登录，但这样做会出错。

编辑：

lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 08-MAR-2021 19:39:44

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                04-MAR-2021 07:42:09
Uptime                    4 days 11 hr. 57 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Desktop\Drawer\Tutorials\Oracle\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Desktop\Drawer\Tutorials\Oracle\diag\tnslsnr\AlbuGierke\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=AlbuGierke)(PORT=5500))(Security=(my_wallet_directory=C:\DESKTOP\DRAWER\TUTORIALS\ORACLE\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "0154b89e82cd4245957cd2ed1b57502d" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "52448234712340b69f274bcc790ecfe0" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

编辑二：

将@EdStevens 解决方案更进一步，我通过SELECT * FROM all_users ORDER BY CREATED;在 SQL Plus 中运行命令来识别可用用户。然后，我使用安装时创建的 PDBADMIN 用户（在 19c 发布时似乎已经创建了几个用户）来完成连接。

我正在使用 SQL 服务器，但似乎无法构建我想要的查询。我有一个有几列的表，其中有

PARAMETER_NAME、GW_LOCATION_ID、Report_Result、DETECT_FLAG

我想要一个查询返回

1. 每个唯一的参数位置组合对应一行
2. 该唯一参数-位置组合的Report_Result列的最大值和
3. 与最大值关联的DETECT_FLAG值。

除了DETECT_FLAG 之外，我还想返回其他列，但我认为如果我能解决这部分问题，我应该能够以类似的方式返回其他列。

如果我按PARAMETER_NAME和GW_LOCATION_ID 分组并聚合Report_Result列（见下文），则查询有效。但是，当我添加DETECT_FLAG列时，出现错误，“列 'SLVs_Flagged.DETECT_FLAG' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。 ”我想要的是值MAX( Report_Result ) 返回的行的 DETECT_FLAG。

SELECT 
PARAMETER_NAME, GW_LOCATION_ID, MAX(Report_Result)
FROM SLVs_Flagged
GROUP BY PARAMETER_NAME, GW_LOCATION_ID
ORDER BY PARAMETER_NAME, GW_LOCATION_ID;

我尝试进行子选择以返回对应于 MAX( Report_Result ) 的DETECT_FLAG值，但我尝试使用 WHERE 条件并出现另一个错误。请告知我如何执行此查询。

这是用于测试的数据的子集。

PARAMETER_NAME  GW_LOCATION_ID  Report_Result   DETECT_FLAG
Perchlorate CDBO-6  2.38    N
Perchlorate CDBO-6  1.45    N
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02655    0.537   Y
Perchlorate CDV-16-02655    4   N
Perchlorate CDV-16-02656    100 N
Perchlorate CDV-16-02656    0.394   Y
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02656    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    4   N
Perchlorate CDV-16-02657    0.174   Y
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02658    4   Y
Perchlorate CDV-16-02658    0.126   Y
Perchlorate CDV-16-02658    0.0561  Y
Perchlorate CDV-16-02658    20  N
Perchlorate CDV-16-02658    4   N
Perchlorate CDV-16-02659    4   N
Nitrate as Nitrogen R-16 S4 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S1 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S2 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-20 S3 0.003   N
Nitrate as Nitrogen R-27    0.003   N
Nitrate as Nitrogen R-31 S2 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen R-32 S3 0.003   N
Nitrate as Nitrogen Test Well 1A    0.01    N
Nitrate as Nitrogen Test Well 1A    -0.01   N
Nitrate as Nitrogen Test Well 2 0.04    N
Nitrate as Nitrogen Test Well 2 0.01    N
Nitrate as Nitrogen Test Well 2A    0   N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 3 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 4 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N
Nitrate as Nitrogen Test Well 8 0.04    N