Oracle synonym Creation (synonym) __oracle

There are many interfaces in the current project, the data source may be different database or different users of the table, to access the table caused a certain amount of trouble. This time you can use synonyms to simplify.

The syntax for synonyms is: CREATE [OR REPLACE] [public] synonym [schema.] Synonym name for [schema.] Object [@dblink];

You also need assignment-related permissions when you create synonyms:

The first is: with the SYS account to the DM account to give the Create synonym permissions

GRANT CREATE synonym to User_temp;

User_temp is the user you want to build a synonym for.

This time when you create synonyms, if you can't find the table TableA under the User_res user you want to manage. (Note: To create a synonym for the TableA table under the User_res user under the User_temp user)

This time also requires a permission is the user_temp user select TableA permission, this time is under the User_res user assigns the value

Grant SELECT on TableA to User_temp;

This allows you to create synonyms under the User_temp user.

--Create the synonym 
Create or replace synonym TableA for
  User_res.tablea;

It is said that the same database, different users to create synonyms, of course, synonyms can be created between different databases, you need to create link. You can also create private synonyms, and so on. Here is a list of synonyms related to the detailed collation of the Great God (Source: http://www.cnblogs.com/kerrycode/archive/2012/12/19/2824963.html Author: Xiaoxiang).

Synonyms Concept

The Oracle synonym (synonyms) is literally the meaning of the alias, and similar to the function of the view, is a mapping relationship. It can save a lot of database space, the operation of different users of the same table does not make much difference; it expands the scope of database usage, enables seamless interaction among different database users, and provides synonym management functionality in Oracle databases. Synonyms are an alias for database objects and are often used to simplify object access and improve the security of object access. When using synonyms, the Oracle database translates it to the name of the corresponding schema object. Like a view, synonyms do not occupy the actual storage space, only the definition of a synonym is saved in the data dictionary. Database administrators can define synonyms for most database objects in an 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, namely, Oracle Common synonyms and Oracle private synonyms. Common user created synonyms are generally private synonyms, public synonyms are generally created by the DBA, ordinary users if you want to create synonyms, you need create public synonym this system permissions.

1 Oracle Common synonyms: 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 label some of the more common database objects, which are often referred to by everyone.

2 Oracle private synonym: It corresponds to a common synonym, and he is owned by the user who created him. Of course, the creator of this synonym can control whether other users have permission to use their own private synonyms by authorizing them.

synonym function

1 The cooperative development of multi-user can screen the name of the object and its holder. If there are no synonyms, when you manipulate other users ' tables, Must pass the user name. In the form of an object name, you can hide the user name after using an oracle synonym, but note that the public synonym simply defines a common alias for the database object, and whether other users can access the database object through the alias, and see if the user has been authorized to do so.

2 simplifies SQL statements for users. One of the above is a simple representation of simplifying SQL, and if you build a table with a long name, you can create an Oracle synonym for the table to simplify SQL development.

3 provides location transparency for remote objects in a distributed database.

4 The role of Oracle synonyms in database links

A database link is a named object that describes the path of a database to another database, through which communication between different databases can be achieved.

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

synonym Rights Management

The permissions associated with synonyms are create synonym, create any synonym, create public synonym permissions.

1: The user creates a private synonym in its own mode, and the user must have the Create synonym permission, otherwise the private synonym cannot be created.

As shown below, the user DM is missing the Create synonym permission and the ORA-01031 error is reported when the synonym is created

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

With the SYS account to the DM account to give the Create synonym permissions

sql> GRANT CREATE synonym to DM;

Grant succeeded.

Then create a private synonym

sql> CREATE synonym TEST for DM. Tm_wgg_atm_gtw_mon;
 
Synonym created

2: If you need to create synonyms in other modes, you must have the permission for create any synonym.

Look at the example below

User DM wants to create a private synonym in Scott mode

sql> CREATE synonym SCOTT. EM for SOCTT. EMP; 
CREATE synonym SCOTT. EM for SOCTT. EMP 

ora-01031:insufficient Privileges

With the SYS account to the DM account to give the Create any synonym permission

sql> GRANT CREATE any synonym to DM; 

Grant succeeded.

sql> CREATE synonym SCOTT. EM for SOCTT. EMP;

Synonym created

3: Creating a public synonym requires the Create synonym system permissions.

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) synonyms

CREATE synonym sysn_test for TEST;

--Common synonyms

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 the access, and then use the following statement to create a thesaurus: the CREATE synonym table_name for table_ Name@db_link;

Public synonyms are not related to the user's schema, but the public meaning is not accessible to all users, and must be authorized before they can proceed; private synonyms are objects of the 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;

Use synonyms to ensure that when the location of the database or object name changes, the application code remains stable, only need to change synonyms;

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

Remove 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 is not deleted

Compiling synonyms

ALTER synonym T COMPILE; --synonyms need to be recompiled when the original object of the synonym is re-established

After DDL operations on the original object, the state of the synonym becomes invalid, and when the synonym is referenced again, the synonym is automatically compiled, the state becomes valid, without human intervention, assuming that the original object's name does not change

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

Problem Kam Set

1: Can a common synonym have the same name as a private synonym? If you can, when accessing synonyms, is a common synonym or a private synonym preferred.

Yes, if a common synonym and a private synonym have the same name, the access synonym is the object to which the private synonym is directed.

2: Why the OE user created a common synonym, the HR user can not access it.

Because HR does not have access to the objects in OE mode, HR users can access the OE mode if it gives HR users permissions such as select Objects.

3: Whether objects, private synonyms, public synonyms can exist with the same name of the three cases.

Under User Kerry, create a 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 a existing object

Note: Objects (tables) cannot have the same name as private synonyms, or they will report ORA-00955 errors

Create a public synonym test, as shown below, where the common synonym can have the same name as the object

sql> CREATE public synonym TEST for REF. ref_wgg_student;

Synonym created

When you access test, as follows: It is the content of the table test, not the contents of the public synonyms

Sql> SELECT * from TEST;
 
object_name  subobject_name   object_id data_object_id object_type  CREATED   last_ddl_time TIMESTAMP   STATUS  Temporary