How to query synonyms of a table in Oracle-Oracle-php.cn

How to query synonyms of a table in Oracle

WBOY

Release： 2022-05-25 15:19:51

Original

11786 people have browsed it

In Oracle, you can use the select statement with the "dba_synonyms" query table to query all synonyms. The syntax is "select * from dba_synonyms"; the functions of synonyms and views are similar and are a mapping relationship, which can save a lot of time. Database space.

How to query synonyms of a table in Oracle

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query the synonyms of a table in oracle

Detailed explanation of Oracle's synonyms

Literally understood, it means an alias, which is similar to the function of a view. It is a mapping relationship.

Synonym syntax: CREATE

[PUBLIC] SYNONYM synonym FOR object;

Copy after login

View all synonyms:

select * from dba_synonyms

Copy after login

How to query synonyms of a table in Oracle

##Synonyms have the following benefits: Save a lot The database space allows different users to operate the same table without much difference; the expanded scope of database usage enables seamless interaction between different database users; synonyms can be created on different database servers and connected through the network .

Extended knowledge:

1: Get all current synonym table names:

select table_name from user_synonyms

Copy after login

2: Query the owners of all synonym tables:

select table_owner from user_synonyms

Copy after login

3: Query the owner of the current synonym table:

select table_owner from user_synonyms WHERE table_name =&#39;大写表名&#39;

Copy after login

4: Query the table data of the synonym table:

select * from 大写所有者名.大写表名

Copy after login

5: Query the basic information of the table structure of the synonym table (fields Name, field type, field length and other information):

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columnswhere table_name =&#39;(大写表名)&#39; AND OWNER  = (大写所=属名) order by COLUMN_NAME&#39;;

Copy after login

6: Query the primary key information of the oracle synonym table:

select a.column_name,
                    DECODE(A.column_name, b.column_name, 1, 0) pk
               from all_tab_columns a,
                    (select column_name
                       from all_constraints c, all_cons_columns col
                      where c.constraint_name = col.constraint_name
                        and c.constraint_type = &#39;P&#39;
                        and c.table_name =  &#39;大写表名&#39; ) b
              where table_name =&#39;大写表名&#39;
                and a.column_name = b.column_name(+) and  DECODE(A.column_name, b.column_name, 1, 0) = 1

Copy after login