引用其他架构中的表，省略架构名称 [英] Reference a table in other schema omiting schema name

问题描述

如果我有表sch1.tab1，是否可以从架构/用户sch2调用它 select * from tab1(假设我们拥有所有特权)?

If I have a table sch1.tab1 is it possible to call it from schema/user sch2 just with select * from tab1 (assume that we have all the privilegies)?

我知道，在postgresql中，您可以设置db在其中查找表的搜索路径，这使您在引用表时可以省略模式，但是我不知道在oracle中是否存在.

I am aware that in postgresql you can set the search path where db would look for tables which enables you to omit the schema when you are referencing a table but I do not know if this exists in oracle.

谢谢.

推荐答案

您可以

You can create a synonym, but you'd have to make one for each table you wanted to access; from sch2:

create synonym tab1 for sch1.tab1;

一种更通用的方法是切换当前架构:

alter session set current_schema = 'SCH1';

您仍与原始用户帐户保持联系，并且仍然仅具有那些特权，但是您不必再限定该架构中的对象.但是，如果两个模式中都有对象，那么现在您必须限定自己的任何表(回到sch2).

You're still connected with your original user account and only have those privileges still, but you don't have to qualify objects in that schema any more. But now you would have to qualify any of your own tables (back in sch2), if you have objects in both schemas.

这篇关于引用其他架构中的表，省略架构名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！