检查 oracle sid 和数据库名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6288122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking oracle sid and database name
提问by Adnan
I want to check SID and current database name.
我想检查 SID 和当前数据库名称。
I am using following query for checking oracle SID
我正在使用以下查询来检查 oracle SID
select instance from v$thread;
but table or view does not exist error is coming.
但表或视图不存在错误即将到来。
I am using following query for checking current database name
我正在使用以下查询来检查当前数据库名称
select name from v$database;
but table or view does not exist error is coming.
但表或视图不存在错误即将到来。
Any idea for above two problems?
对以上两个问题有什么想法吗？
回答by V4Vendetta
I presume SELECT user FROM dual;should give you the current user
我想SELECT user FROM dual;应该给你当前用户
and SELECT sys_context('userenv','instance_name') FROM dual;the name of the instance
和SELECT sys_context('userenv','instance_name') FROM dual;实例的名称
I believe you can get SID as SELECT sys_context('USERENV', 'SID') FROM DUAL;
我相信你可以得到 SID 作为 SELECT sys_context('USERENV', 'SID') FROM DUAL;
回答by Sergio M C Figueiredo
If, like me, your goal is get the database host and SID to generate a Oracle JDBC url, as
如果像我一样，您的目标是获取数据库主机和 SID 以生成 Oracle JDBC url，如
jdbc:oracle:thin:@<server_host>:1521:<instance_name>
the following commands will help:
以下命令将有所帮助：
Oracle query command to check the SID (or instance name):
用于检查 SID（或实例名称）的 Oracle 查询命令：
select sys_context('userenv','instance_name') from dual;
Oracle query command to check database name (or server host):
Oracle查询命令来检查数据库名称（或服务器主机）：
select sys_context('userenv', 'server_host') from dual;
Att. Sergio Marcelo
阿特。塞尔吉奥·马塞洛
回答by Patrick Marchand
Just for completeness, you can also use ORA_DATABASE_NAME.
为了完整起见，您还可以使用 ORA_DATABASE_NAME。
It might be worth noting that not all of the methods give you the same output:
可能值得注意的是，并非所有方法都为您提供相同的输出：
SQL> select sys_context('userenv','db_name') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
orcl
SQL> select ora_database_name from dual;
ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM
回答by APC
The V$ views are mainly dynamic views of system metrics. They are used for performance tuning, session monitoring, etc. So access is limited to DBA users by default, which is why you're getting ORA-00942.
V$ 视图主要是系统指标的动态视图。它们用于性能调优、会话监控等。因此默认情况下，访问权限仅限于 DBA 用户，这就是您获得ORA-00942.
The easiest way of finding the database name is:
查找数据库名称的最简单方法是：
select * from global_name;
This view is granted to PUBLIC, so anybody can query it.
这个视图被授予 PUBLIC，所以任何人都可以查询它。
回答by anant kumar
Type on sqlpluscommand prompt
在sqlplus命令提示符下键入
SQL> select * from global_name;
then u will be see result on command prompt
然后你会在命令提示符下看到结果
SQL ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
Here first one "ORCL" is database name,may be your system "XE" and other what was given on oracle downloading time.
这里第一个“ORCL”是数据库名，可能是你的系统“XE”等oracle下载时给出的。
回答by Phil
As has been mentioned above,
如上所述，
select global_name from global_name;
is the way to go.
是要走的路。
You couldn't query v$database/v$instance/v$thread because your user does not have the required permissions. You can grant them (via a DBA account) with:
您无法查询 v$database/v$instance/v$thread，因为您的用户没有所需的权限。您可以通过以下方式授予它们（通过 DBA 帐户）：
grant select on v$database to <username here>;
