Oralce Database Creation Synonyms

Lead: We do not want to add a business form to our base platform database, then we need to recreate a new database and associate two databases with a synonym.

V6 is the base database, RT is the business database, there is a table in RT Book_count_month

Then create synonyms in V6, SQL as follows:

--Create the synonym create or replace synonym Book_count_month for RT. Book_count_month;

The database displays:

Attached:

Synonym Concepts

Oracle's synonym (synonyms) is literally understood to mean the alias, and the function of the view is similar to that of a mapping relationship. It can save a lot of database space, the operation of different users of the same table is not much difference, it extends the use of the database, the ability to achieve seamless interaction between different database users, the Oracle database provides the functionality of synonym management. 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 into the name of the corresponding schema object. Similar to views, synonyms do not occupy the actual storage space, only the definition of synonyms is saved in the data dictionary. Most database objects in an Oracle database, such as tables, views, materialized views, sequences, functions, stored procedures, packages, synonyms, and so on, can be defined by database administrators as synonyms.

Synonym classification

There are two types of Oracle synonyms, the Oracle common synonym and the Oracle private synonym. Common user-created synonyms are generally private synonyms, public synonyms are generally created by the DBA, ordinary users if you want to create a synonym, you need the synonym this system permissions.

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 mark more common database objects, which are often referred to by everyone.

2) Oracle private synonym: It is equivalent 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 access to their own private synonyms by authorizing them.

Synonym effect

1) in multi-user collaborative development, the name of the object and its holder can be masked. If there is no synonym, when you manipulate other users ' tables, The user name must be hidden after using the Oracle synonym in the form of the user name. Object name, of course, it is important to note that the public synonym simply defines a common alias for the database object, whether the other user can access the database object through the alias, or if the user is authorized.

2) Simplify the SQL statement for the user. The above one is actually a simplified SQL embodiment, and if the name of the table you built is very long, you can create a Oracle synonym for this table to simplify SQL development.

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

4) The role of Oracle synonyms in database linking

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 implemented.

Create database link name connect to user name identified by password using ' Oracle connection string '; The object name must be accessed through the 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 his or her own mode, and the user must have the Create synonym permission, otherwise the private synonym cannot be created.

The user DM is missing the Create SYNONYM permission as shown below, and a ORA-01031 error is reported when creating synonyms

sql> CREATE synonym TEST for DM. Tm_wgg_atm_gtw_mon; CREATE synonym TEST for DM. Tm_wgg_atm_gtw_monora-01031:insufficient Privileges

Use the SYS account to give the DM account permission to create synonym

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 permissions of create any synonym.

Look at the following example

User DM wants to create a private synonym in the 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 give the DM account permission to create any synonym

sql> GRANT CREATE any synonym to DM; Grant succeeded. sql> CREATE synonym SCOTT. EM for SOCTT. EMP; Synonym created

3: Create public synonym synonym system permission is required.

Create synonyms

The syntax for creating synonyms is as follows:

The general 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 first create a database link to extend access, and then create a database synonym using the following statement: creating synonym table_name for [email Protected]_link;

Public synonyms are not relevant to the user's schema, but public means not all users can access it and must be authorized to do so; a private synonym is the object 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        Testetl        sysn_test        ETL        testsql> SELECT * from user_synonyms

Using synonyms

SELECT * from Sysn_test;

Using synonyms ensures that the application's code remains stable when the location or object name of the database changes, and only the synonyms need to be changed;

When using a synonym that does not specify a schema, first look in the user's own schema and then look 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 is not deleted

Compiling synonyms

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

After the DDL operation of the original object, the state of the synonym becomes invalid, and when the synonym is referenced again, the synonym is automatically compiled and the state becomes valid without human intervention, if 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

Problem Brocade Set

1: Can common synonyms and private synonyms have the same name? If so, is it a common synonym or a private synonym to take precedence when accessing synonyms?

Yes, if there is a common synonym and a private synonym with the same name, in the access synonym is the object that is accessed by the private synonym of the point.

2: Why are the common synonyms created by OE users not accessible to HR users?

Because the HR does not have access to the object in OE mode, if the OE mode gives the HR user permissions such as Select Object, then the HR user can access it.

3: Can objects, private synonyms, public synonyms exist with the same name as the three?

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_studentora-00955:name is already used by an existing object

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

Create a common synonym test, as shown below, where the public 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 synonym

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 dropped, the database accesses a public synonym

sql> DROP TABLE TEST PURGE; Table dropped sql> SELECT * from TEST;          ID NAME-------------------------------------------          

Conclusion: When objects of the same name and public synonyms exist, the database takes precedence over the object, and when there is a private object and a public object with the same name, the database takes precedence over the private synonym as the target

Oralce Database Creation Synonyms