在不指定架构名称的情况下访问表

本文介绍了在不指定架构名称的情况下访问表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个名为GBO_ARC_SCHEMA的架构，其中有一个名为TEST_EMP的表， 我有两个用户说USER_A和USER_B.

I have a schema called GBO_ARC_SCHEMA, in which I have one table called TEST_EMP, and I have two users say USER_A and USER_B.

首先我连接到USER_A并在查询下触发

First I connected to USER_A and fired below query

select count(*)from TEST_EMP; count ----- 20

此后，我以USER_b的身份连接并在查询下面触发，但是它给出了错误，表示表或视图没有退出

After that I connected as USER_b and fired below query but it is giving an error, saying that table or view does not exit

select count(*)from TEST_EMP;

但是，如果我使用scma.object name，则可以像下面这样查询

But if I use scma.object name it is allowing me to query like below

select count(*)from GBO_ARC_SCHEMA.TEST_EMP;

但是根据我的要求，我不想指定架构名称.

but as per my requirement I don't want to specify schema name.

有人可以帮我吗?

推荐答案

如果希望所有用户能够从表中进行选择而无需使用架构名称，则您需要创建公共同义词:

If you want all users to be able to select from the table without qualifying with the schema name, you want to create a public synonym:

create public synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

如果只希望user_b省略架构名称，则要在用户_b的架构(以user_b登录)中创建一个私有同义词.

If you only want user_b to omit the schema name, you want to create a private synonym WITHIN user_b's schema (that is logged on as user_b)

create synonym TEST_EMP for GBO_ARC_SCHEMA.TEST_EMP;

如果您坚持不使用同义词，则登录后，请执行

If you insist on not using synonyms, then, after logging in, do a

alter session set current_schema = GBO_ARC_SCHEMA;