ALL_SYNONYMS

📄字数 857

👁️阅读量 加载中...

功能描述

ALL_SYNONYMS系统视图用于保存和管理同义词相关信息。

字段说明

序号	字段名	类型	说明	V11	V12
0	DB_ID	INTEGER	库ID	√	√
1	SCHEMA_ID	INTEGER	模式ID	√	√
2	USER_ID	INTEGER	同义词的属主ID	√	√
3	SYNO_ID	INTEGER	同义词ID	√	√
4	SYNO_NAME	VARCHAR	同义词名	√	√
5	TARG_SCHE_ID	INTEGER	目标模式	√	√
6	TARG_NAME	VARCHAR	目标对象名	√	√
7	IS_PUBLIC	BOOLEAN	是否全局同义词	√	√
8	VALID	BOOLEAN	是否有效	√	√
9	CREATE_TIME	DATETIME	创建时间	√	√
10	IS_SYS	BOOLEAN	是否系统自建	x	√
11	RESERVED2	VARCHAR	保留字段	√	√

应用举例

• 查询V12内置同义词(V11无)

SQL> SELECT * FROM ALL_SYNONYMS;
+-------+-----------+---------+---------+------------+--------------+-----------------+-----------+-------+--------------------------+--------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | SYNO_ID | SYNO_NAME  | TARG_SCHE_ID |    TARG_NAME    | IS_PUBLIC | VALID |       CREATE_TIME        | IS_SYS | RESERVED2 |
+-------+-----------+---------+---------+------------+--------------+-----------------+-----------+-------+--------------------------+--------+-----------+
| 1     | 0         | 1       | 1048576 | RECYCLEBIN | 1            | USER_RECYCLEBIN | T         | T     | 2025-06-25 10:08:43.581  | T      | <NULL>    |
+-------+-----------+---------+---------+------------+--------------+-----------------+-----------+-------+--------------------------+--------+-----------+

• 查询拥有同义词的库名和模式名

SQL> CREATE TABLE tbsyns_info(id INT);

-- 为该表名创建同义词
SQL> CREATE SYNONYM sync FOR tbsyns_info;

SQL> SELECT ss.db_id,sm.schema_name,ss.syno_id,syno_name FROM ALL_SYNONYMS ss JOIN ALL_SCHEMAS sm USING(db_id,schema_id) WHERE ss.syno_name='SYNC';
+-------+-------------+---------+-----------+
| DB_ID | SCHEMA_NAME | SYNO_ID | SYNO_NAME |
+-------+-------------+---------+-----------+
| 1     | SYSDBA      | 1048578 | SYNC      |
+-------+-------------+---------+-----------+

-- 使用同义词
SQL> INSERT INTO sync values(1);

SQL> SELECT * FROM sync;
+----+
| ID |
+----+
| 1  |
+----+

SQL> SELECT * FROM tbsyns_info;
+----+
| ID |
+----+
| 1  |
+----+

• 查询同义词的目标对象

相关系统表

• SYS_OBJECTS
• SYS_SCHEMAS
• SYS_SYNONYMS