SQL 从 oracle 中的另一个模式中选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13709164/
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
SELECT data from another schema in oracle
提问by marianov
I want to execute a query that selects data from a different schema than the one specified in the DB connection (same Oracle server, same database, different schema)
我想执行一个查询，该查询从与数据库连接中指定的架构不同的架构中选择数据（相同的 Oracle 服务器、相同的数据库、不同的架构）
I have an python app talking to an Oracle server. It opens a connection to database (server/schema) A, and executes select queries to tables inside that database.
我有一个与 Oracle 服务器通信的 python 应用程序。它打开到数据库（服务器/模式）A 的连接，并对该数据库中的表执行选择查询。
I've tried the following :
我试过以下：
select ....
from pct.pi_int, pct.pi_ma, pct.pi_es
where ...
But I get:
但我得到：
ORA-00942: table or view does not exist
I've also tried surrounding the schema name with brackets:
我也试过用括号括起模式名称：
from [PCT].pi_int, [PCT].pi_ma, [PCAT].pi_es
I get:
我得到：
ORA-00903: invalid table name
The queries are executed using the cx_Oracle python module from inside a Django app.
使用 Django 应用程序内部的 cx_Oracle python 模块执行查询。
Can this be done or should I make a new db connection?
这可以完成还是应该建立一个新的数据库连接？
回答by Justin Cave
Does the user that you are using to connect to the database (user Ain this example) have SELECTaccess on the objects in the PCTschema? Assuming that Adoes not have this access, you would get the "table or view does not exist" error.
您用于连接到数据库的用户A（本例中的用户）是否有权SELECT访问PCT架构中的对象？假设A没有此访问权限，您将收到“表或视图不存在”错误。
Most likely, you need your DBA to grant user Aaccess to whatever tables in the PCTschema that you need. Something like
很可能，您需要您的 DBA 授予用户A访问PCT您需要的架构中的任何表的权限。就像是
GRANT SELECT ON pct.pi_int
TO a;
Once that is done, you should be able to refer to the objects in the PCTschema using the syntax pct.pi_intas you demonstrated initially in your question. The bracket syntax approach will not work.
完成后，您应该能够PCT使用pct.pi_int最初在问题中演示的语法来引用模式中的对象。括号语法方法将不起作用。
回答by Sanjaya Balasuriya
In addition to grants, you can try creating synonyms. It will avoid the need for specifying the table owner schema every time.
除了赠款，您还可以尝试创建同义词。它将避免每次都需要指定表所有者架构。
From the connecting schema:
从连接模式：
CREATE SYNONYM pi_int FOR pct.pi_int;
Then you can query pi_intas:
然后你可以查询pi_int为：
SELECT * FROM pi_int;
回答by Michael Ballent
Depending on the schema/account you are using to connect to the database, I would suspect you are missing a grant to the account you are using to connect to the database.
根据您用于连接到数据库的架构/帐户，我怀疑您缺少对用于连接到数据库的帐户的授权。
Connect as PCT account in the database, then grant the account you are using select access for the table.
在数据库中作为 PCT 帐户连接，然后授予您正在使用的帐户对表的选择访问权限。
grant select on pi_int to Account_used_to_connect
