blocks|key|655505|text|我认为SELECT+user+FROM+dual;应该给你当前的用户|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|655506|和SELECT+sys_context('userenv','instance_name')+FROM+dual;作为实例的名称|655507|我相信你可以让希德当SELECT+sys_context('USERENV',+'SID')+FROM+DUAL;|655508|entityMap^0|3|M|0|1|1K|0|A|1B|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|K|$]]

I presume <code>SELECT user FROM dual;</code> should give you the current user

and <code>SELECT sys_context('userenv','instance_name') FROM dual;</code> the name of the instance

I believe you can get SID as <code>SELECT sys_context('USERENV', 'SID') FROM DUAL;</code>

blocks|key|655603|text|V$视图主要是系统指标的动态视图。它们用于性能调优、会话监控等。因此，默认情况下，只有数据库管理员用户才能访问，这就是为什么要使用ORA-00942。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|655604|查找数据库名称的最简单方法是：|655605|select+*+from+global_name;|code-block|syntax|javascript|655606|此视图被授予PUBLIC，因此任何人都可以查询它。|655607|entityMap^0|1T|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

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 <code>ORA-00942</code>.

The easiest way of finding the database name is:

<pre><code>select * from global_name;
</code></pre>

This view is granted to PUBLIC, so anybody can query it.

blocks|key|872948|text|如上所述，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|872949|select+global_name+from+global_name;|code-block|syntax|javascript|872950|才是正确的选择。|872951|您无法查询v$database/v$instance/v$thread，因为您的用户没有所需的权限。您可以(通过DBA帐户)向它们授予以下权限：|872952|grant+select+on+v$database+to+<username+here>;|872953|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

As has been mentioned above, 

<pre><code>select global_name from global_name;
</code></pre>

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:

<pre><code>grant select on v$database to &lt;username here&gt;;
</code></pre>

blocks|key|655753|text|出于完整性考虑，您也可以使用ORA_DATABASE_NAME。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|655754|值得注意的是，并不是所有的方法都会给出相同的输出：|655755|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|code-block|syntax|javascript|655756|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Just for completeness, you can also use ORA_DATABASE_NAME. 

It might be worth noting that not all of the methods give you the same output:

<pre><code>SQL&gt; select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
orcl

SQL&gt; select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM

SQL&gt; select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM
</code></pre>

blocks|key|873072|text|如果像我一样，您的目标是让数据库主机和SID生成Oracle+JDBC+url，则如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|873073|jdbc:oracle:thin:@<server_host>:1521:<instance_name>|code-block|syntax|javascript|873074|以下命令将有所帮助：|873075|用于检查SID+(或实例名称)的Oracle查询命令：|873076|select+sys_context('userenv','instance_name')+from+dual;+|873077|用于检查数据库名称(或服务器主机)的Oracle查询命令：|873078|select+sys_context('userenv',+'server_host')+from+dual;|873079|阿特。塞尔吉奥·马塞洛|873080|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

If, like me, your goal is get the database host and SID to generate a Oracle JDBC url, as 

<pre><code>jdbc:oracle:thin:@&lt;server_host&gt;:1521:&lt;instance_name&gt;
</code></pre>

the following commands will help:

Oracle query command to check the SID (or instance name):

<pre><code>select sys_context('userenv','instance_name') from dual; 
</code></pre>

Oracle query command to check database name (or server host):

<pre><code>select sys_context('userenv', 'server_host') from dual;
</code></pre>

Att.
Sergio Marcelo

blocks|key|655835|text|在sqlplus命令提示符下键入|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|655836|SQL>+select+*+from+global_name;|code-block|syntax|javascript|655837|然后，您将在命令提示符上看到结果|655838|SQL+ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM|655839|这里的第一个"ORCL“是数据库名称，可能是您的系统"XE”和其他给定的oracle下载时间。|655840|entityMap^0|1|7|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|Y|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

Type on <code>sqlplus</code> command prompt 

<pre><code>SQL&gt; select * from global_name;
</code></pre>

then u will be see result on command prompt

<pre><code>SQL ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
</code></pre>

Here first one "ORCL" is database name,may be your system "XE" and other what was given on oracle downloading time.

blocks|key|873147|text|从dual中选择sys_context('userenv'，'instance_name')；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|873148|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

SELECT sys_context('userenv','instance_name') FROM dual;

I want to check SID and current database name.

I am using following query for checking oracle SID

<pre><code>select instance from v$thread;
</code></pre>

but table or view does not exist error is coming.

I am using following query for checking current database name

<pre><code>select name from v$database;
</code></pre>

but table or view does not exist error is coming.

Any idea for above two problems?

Checking oracle sid and database name

数据库管理

数据库

服务器

我想检查SID和当前数据库名称。我正在使用以下查询来检查oracle SIDselect instance from v$thread;但是表或视图不存在错误即将到来。我正在使用以下查询来检查当前数据库名称select name from v$database;但是表或视图不存在错误即将到来。对上述两个问题有什么想法吗...

问检查oracle sid和数据库名称
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检查oracle sid和数据库名称EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检查oracle sid和数据库名称
EN