Oracle教程 2023-03-21

Oracle 视图 ALL_SYNONYMS 官方解释，作用，如何使用详细说明

ALL_SYNONYMS是Oracle提供的用于查询当前用户持有的所有同义词的内置视图，其列如下：

SYNONYM_NAME：该列用于显示同义词的名称，由当前用户创建。
TABLE_OWNER：该列用来显示表所有者的名称，此列中数据可以和该用户相同、也可以是其它用户。
TABLE_NAME：显示同义词翻译的表名称。
DB_LINK：显示创建同义词的数据库之间链接，默认为NULL。

使用方法：

要查看当前用户所拥有的所有同义词，可以使用如下SELECT语句：
SELECT * FROM ALL_SYNONYMS;

要查看除当前用户之外，另一用户创建的同义词，可以使用携带TABLE_OWNER的SELECT语句：
SELECT * FROM ALL_SYNONYMS WHERE TABLE_OWNER = ‘other_user’;

此外，如果要查看特定表的同义词，也可以使用携带TABLE_NAME的SELECT语句：
SELECT * FROM ALL_SYNONYMS WHERE TABLE_NAME = ‘my_table’;

ALL_SYNONYMS describes the synonyms accessible to the current user.

The following criteria determine the list of synonyms that ALL_SYNONYMS shows:

All private synonyms owned by the logged-in user, even if the base object pointed to is not accessible.
All public synonyms, even if the base object pointed to is not accessible.
All private synonyms owned by a different user, where the ultimate base object pointed to by that synonym or by any chain of nested synonyms, is know to be accessible because of a grant to the logged-in user, or a grant to a role in effect for this session.
If the current session has any of the following privileges, then all synonyms that point directly to local objects are shown because it is assumed that the session can access those objects:
- LOCK ANY TABLE
- READ ANY TABLE
- SELECT ANY TABLE
- INSERT ANY TABLE
- UPDATE ANY TABLE
- DELETE ANY TABLE
Synonyms that point to remote objects are excluded because the system privileges just listed do not automatically convey access to those remote objects. Also, if the synonyms point to objects other than tables and views (such as sequences, PL/SQL procedures, and so on) then this rule may show synonyms that ultimately resolve to objects that this session cannot access.
All private synonyms owned by a different user, where the synonym is via a database link, are excluded.

Related Views

DBA_SYNONYMS describes all synonyms in the database.
USER_SYNONYMS describes the synonyms owned by the current user. This view does not display the OWNER column.

Column	Datatype	Description
`OWNER`	`VARCHAR2(128)`	Owner of the synonym
`SYNONYM_NAME`	`VARCHAR2(128)`	Name of the synonym
`TABLE_OWNER`	`VARCHAR2(128)`	Owner of the object referenced by the synonym, or creator of the referring synonym if the target is a public synonym (that is, the object referred to by `TABLE_NAME`). Although the column is called `TABLE_OWNER`, the object owned is not necessarily a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
`TABLE_NAME`	`VARCHAR2(128)`	Name of the object referenced by the synonym. Although the column is called `TABLE_NAME`, the object does not necessarily have to be a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
`DB_LINK`	`VARCHAR2(128)`	Name of the database link referenced, if any
`ORIGIN_CON_ID`	`VARCHAR2(256)`	The ID of the container where the data originates. Possible values include: `0`: This value is used for rows in non-CDBs. This value is not used for CDBs. n: This value is used for rows containing data that originate in the container with container ID n (n = 1 if the row originates in root)