从Oracle中的另一个架构中选择数据 [英] SELECT data from another schema in oracle
问题描述
我想执行一个查询,该查询从与数据库连接中指定的模式不同的模式中选择数据(相同的Oracle服务器,相同的数据库,不同的模式)
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服务器通信的python应用程序.它打开与数据库(服务器/架构)A的连接,并对数据库中的表执行选择查询.
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.
我尝试了以下方法:
select ....
from pct.pi_int, pct.pi_ma, pct.pi_es
where ...
但是我得到了
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
我得到:
ORA-00903: invalid table name
查询是从Django应用程序内部使用cx_Oracle python模块执行的.
The queries are executed using the cx_Oracle python module from inside a Django app.
可以做到这一点还是我应该建立新的数据库连接?
Can this be done or should I make a new db connection?
推荐答案
您用来连接到数据库的用户(在此示例中为用户 A
)是否具有 SELECT 代码>访问
PCT
模式中的对象?假设 A
没有此访问权限,您将收到表或视图不存在"错误.
Does the user that you are using to connect to the database (user A
in this example) have SELECT
access on the objects in the PCT
schema? Assuming that A
does not have this access, you would get the "table or view does not exist" error.
最有可能的是,您需要DBA授予用户 A
对您所需的 PCT
模式中的任何表的访问权限.像
Most likely, you need your DBA to grant user A
access to whatever tables in the PCT
schema that you need. Something like
GRANT SELECT ON pct.pi_int
TO a;
完成后,您应该能够使用语法 pct.pi_int
来引用 PCT
模式中的对象,如您最初在问题中所展示的那样.方括号语法方法无效.
Once that is done, you should be able to refer to the objects in the PCT
schema using the syntax pct.pi_int
as you demonstrated initially in your question. The bracket syntax approach will not work.
这篇关于从Oracle中的另一个架构中选择数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!