How to query synonyms of a table in Oracle

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.

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;

View all synonyms:

select * from dba_synonyms

##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

2: Query the owners of all synonym tables:

select table_owner from user_synonyms

3: Query the owner of the current synonym table:

select table_owner from user_synonyms WHERE table_name ='大写表名'

4: Query the table data of the synonym table:

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

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 ='(大写表名)' AND OWNER  = (大写所=属名) order by COLUMN_NAME';

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 = 'P'
                        and c.table_name =  '大写表名' ) b
              where table_name ='大写表名'
                and a.column_name = b.column_name(+) and  DECODE(A.column_name, b.column_name, 1, 0) = 1

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of How to query synonyms of a table in Oracle. For more information, please follow other related articles on the PHP Chinese website!