Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to use synonyms in Oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Oracle synonym concept:

The synonym (synonyms) of Oracle literally means an alias, which is similar to the function of a view, which is a mapping relationship.

It can save a lot of database space, and there is little difference in the operation of the same table for different users; it expands the scope of use of the database and can achieve seamless interaction between different database users; Oracle database provides the function of synonym management.

A synonym is an alias for database objects, which is often used to simplify object access and improve object access security.

When a synonym is used, the Oracle database translates it into the name of the corresponding scheme object. Similar to the view, synonyms do not take up actual storage space, only the definition of synonyms is saved in the data dictionary.

Database administrators can define synonyms for most database objects in Oracle database, such as tables, views, materialized views, sequences, functions, stored procedures, packages, synonyms, and so on.

Classification of synonyms:

There are two types of Oracle synonyms, which are Oracle public synonyms and Oracle private synonyms.

Synonyms created by ordinary users are generally private synonyms, while public synonyms are generally created by DBA. Ordinary users need the system permission of CREATE PUBLIC SYNONYM if they want to create synonyms.

1): Oracle common synonym: owned by a special user group, Public. As the name implies, all users in the database can use common synonyms. Common synonyms are often used to indicate some of the more common database objects that everyone needs to refer to.

2): Oracle private synonym: it corresponds to a public synonym, which is owned by the user who created it. Of course, the creator of this synonym can control whether other users have the right to use their own private synonyms through authorization.

The function of synonyms:

1): in multi-user collaborative development, the name of the object and its owner can be shielded.

If there is no synonym, when manipulating other users' tables, it must be in the form of user name. object name. After using Oracle synonyms, the user name can be hidden.

Of course, it is important to note here that the public synonym only defines a public alias for the database object, and whether other users can access the database object through this alias depends on whether the user has been authorized.

2): simplify sql statements for users. The above is actually a way to simplify sql, and if you create a table with a long name, you can create an Oracle synonym for the table to simplify sql development.

3): provide location transparency for remote objects in distributed databases.

4): the role of Oracle synonyms in database links A database link is a named object that indicates the path from one database to another, through which communication between different databases can be realized.

Create database link database chain name connect to user name identified by password using 'Oracle connection string'; access object through object name @ database chain name. The role of synonyms in the database chain is to provide location transparency.

Create synonyms:

The syntax for creating synonyms is as follows:

The common usage is as follows:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] Synonym name FOR [schema.] Object [@ dblink];-- proprietary (private) synonym CREATE SYNONYM SYSN_TEST FOR TEST;-- public synonym CREATE PUBLIC SYNONYM PUBLIC_TEST FOR TEST

If you want to create a synonym for a table on a remote database, you need to create a Database Link (database connection) to extend access, and then use the following statement to create a database synonym: create synonym table_name for table_name@DB_Link

Public synonyms have nothing to do with the user's schema, but the public meaning is that not all users can access it and must be authorized to do so; private synonyms are objects of schema.

Synonym rights management and use:

The permissions related to synonyms are CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM permissions.

1): a user creates a private synonym in his own mode. The user must have CREATE SYNONYM permission, otherwise he cannot create a private synonym.

-- create private synonyms-- use System to authorize c##MLQ1 users to create private synonyms grant create synonym to c##MLQ1--c##MLQ1 create synonyms (Student is a table) The create synonym syn_MLQ1 for c##MLQ1.Student--c##MLQ1 user calls the synonym select * from syn_MLQ1-- to log in using the c##MLQ user, Query (first requires the c##MLQ1 user to grant query permissions to the c##MLQ user) grant select on syn_MLQ1 to centering MLQ copyright-revoking the query permission revoke select on syn_MLQ1 from centering MLQ copyright-using the c##MLQ user to query select * from c##MLQ1.syn_MLQ1

2): the user creates a common synonym in his own mode, and the user must have CREATE PUBLIC SYNONYM permission, otherwise he cannot create a public synonym.

-- create public synonyms-- use System to authorize c##MLQ1 users to create public synonyms grant create public synonym to c##MLQ1--c##MLQ1 users to create public synonyms create Public synonym syn_MLQ1_pub for c##MLQ1.userinfo | | create public synonym syn_MLQ1_pub for userinfo-- deletes synonyms under c##MLQ1 users. Drop synonym c##MLQ1.syn_MLQ1_pub--c##MLQ1 users call synonyms select * from syn_MLQ1_pub-- using c##MLQ users to log in, Query (first requires c##MLQ1 users to grant query permissions to all users) grant select on syn_MLQ1_pub to public | | grant select on userinfo to public-- revokes query permissions revoke select on syn_MLQ1_pub from public-- uses c##MLQ users to query select * from syn_MLQ1_pub

View synonyms:

SQL > SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME IN ('SYSN_TEST','PUBLIC_TEST'); OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK-- PUBLIC PUBLIC_TEST ETL TESTETL SYSN_TEST ETL TESTSQL > SELECT * FROM USER_SYNONYMS

Use synonyms:

SELECT * FROM SYSN_TEST

Using synonyms can ensure that when the location of the database or the object name changes, the application code remains stable and only needs to change the synonyms.

When using a synonym that does not specify schema, first look for it in the user's own schema, and then look for it in the public synonym

Delete synonyms:

DROP [PUBLIC] SYNONYM [schema. ] synonym name [FORCE]; DROP SYNONYM SYSN_TEST;DROP PUBLIC SYNONYM PUBLIC_TEST;-- when the original object of the synonym is deleted, the synonym will not be deleted

Compile synonyms:

ALTER SYNONYM T COMPILE;-when the original object of a synonym is re-established, the synonym needs to be recompiled

After the DDL operation on the original object, the state of the synonym becomes INVALID;. When the synonym is referenced again, the synonym is automatically compiled and the state becomes VALID without human intervention, of course, provided that the name of the original object is not changed.

SQL > SELECT * FROM T; ID NAME--SQL > SELECT * FROM TEST; ID NAME--SQL > ALTER TABLE TEST ADD SEX NUMBER (1); Table alteredSQL > SELECT OBJECT_NAME, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME='T' OBJECT_NAME STATUS---T INVALID

Collection of questions:

1: can public synonyms and private synonyms have the same name? If possible, when accessing synonyms, do public synonyms or private synonyms take precedence?

Yes, if there is a public synonym and a private synonym with the same name, the access synonym is the object that the private synonym points to.

2: why can't OE users access public synonyms created by HR users?

Because HR does not have access to objects in OE mode, if OE mode gives HR users permissions such as SELECT objects, then HR users can access them.

After reading the above, do you have any further understanding of how to use synonyms in Oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.