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

This article mainly introduces "what is Oracle synonym". In daily operation, I believe many people have doubts about what Oracle synonym is. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "what is Oracle synonym?" Next, please follow the editor to study!

Synonym concept

The synonym (synonyms) of Oracle literally means an alias, which is somewhat 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.

Synonym classification

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 public 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 and 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.

Synonym function

1 in multi-user collaborative development, the names of objects and their holders can be shielded. If there are no synonyms, when manipulating other users' tables, you must use the user name .object name. After using the Oracle synonym, you can hide the user name. Of course, it should be noted 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 database.

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.

Synonym rights management

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

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

As shown below, user DM lacks CREATE SYNONYM permission and will report an ORA-01031 error when creating synonyms

SQL > CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON; CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON ORA-01031: insufficient privileges

Use the sys account to grant CREATE SYNONYM permissions to the DM account

SQL > GRANT CREATE SYNONYM TO DM; Grant succeeded.

Then create private synonyms

SQL > CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON; Synonym created

two。 If you need to create synonyms in other modes, you must have permission to CREATE ANY SYNONYM.

Looking at the following example, user DM wants to create private synonyms in SCOTT mode

SQL > CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP; CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP ORA-01031: insufficient privileges

Use the sys account to grant CREATE ANY SYNONYM permissions to the DM account

SQL > GRANT CREATE ANY SYNONYM TO DM; Grant succeeded. SQL > CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP; Synonym created

3. Creating public synonyms requires CREATE PUBLIC SYNONYM system permissions.

Create synonyms

The syntax for creating synonyms is as follows:

The common usage is as follows:

SQL > GRANT CREATE ANY SYNONYM TO DM; Grant succeeded. SQL > CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP; Synonym created

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

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 TEST ETL SYSN_TEST ETL TEST SQL > 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

Compilation synonym

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 altered SQL > SELECT OBJECT_NAME, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME='T' OBJECT_NAME STATUS-T INVALID

A 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, when accessing the synonym, you are accessing the object to which the private synonym points.

two。 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 users give HR users permissions such as SELECT objects, then HR users can access them.

3. Can objects, private synonyms and public synonyms have the same name? what is the priority analysis of synonyms and tables in ORACLE?

Under user kerry, create the table TEST

SQL > CREATE TABLE TEST AS SELECT * FROM USER_OBJECTS WHERE 1 = 0

Create a private synonym TEST

SQL > CREATE SYNONYM TEST FOR REF.REF_WGG_STUDENT; CREATE SYNONYM TEST FOR REF.REF_WGG_STUDENT ORA-00955: name is already used by an existing object

Note: objects (tables) and private synonyms cannot have the same name, otherwise an ORA-00955 error will be reported

Create a public synonym TEST, as shown below. Public synonyms can have the same name as an object.

SQL > CREATE PUBLIC SYNONYM TEST FOR REF.REF_WGG_STUDENT; Synonym created

When you access TEST, it looks like this: it is the content of the table TEST, not the content of public synonyms

SQL > SELECT * FROM TEST; OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY

When the table TEST is deleted, the database accesses public synonyms

SQL > DROP TABLE TEST PURGE; Table dropped SQL > SELECT * FROM TEST; ID NAME-1 12 SQL >

Conclusion: when there are objects of the same name and public synonyms, the database gives priority to selecting the object as the target, and when there are private synonyms and public synonyms of the same name, the database gives priority to choosing private synonyms as the target.

At this point, the study of "what is Oracle synonym" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.