Oracle Learning (iv) synonyms and database links

1. Synonyms:

In a distributed database environment, to identify a database object, you must specify the host name, the server name, the owner of the object, and the object name. This undoubtedly increases the access difficulty of the visitor. You can create synonyms for database objects to provide a simple, unique name that identifies the database object in order to use the database object for different users.

For example, let's say that we have a table table in the database for a user, so when the database Other users need to access the table in a user.table way, which requires us to know who owns the table when we visit the table. To avoid this, we can create a synonym synonym point to the user.table table, then any user will be able to access the user's table directly using synonym.

Synonyms can point to objects that have tables, views, procedures, functions, packages, and sequences.

Synonyms have both public and private synonyms. Public synonyms are shared by all users in the database, and private synonyms are only available to them.

To create a synonym using sql:

Create [public] synonym [schema.] Synonymname for [schema.] object[@dblink]  
--public represents the creation of a public synonym, which is private by default. @dblink indicates that a synonym for the remote database was created, Dblink

is the name of the remote database link.

To delete synonyms using sql:

drop [public] synonym [schema.] Synonymname  
--public indicates that a common synonym is deleted, and if it is not specified, the default is to delete the private synonym and the error occurs when the corresponding private synonym does not exist

.

2. Database Link:

As a distributed database system, Oracle provides the ability to use a remote database. If the table is in a remote database, in order to specify an access path to an object in the remote database, you must create a database link that enables local users to log on to the remote database using its data through this database link. The database link has both public and private, and private only the creator can access it, and the default is private.

To create a database link:

        Create [public] the database link dababaselinkname [connect to user identified by 

password] using connectstring.  
-When you create a database link, you must specify the user name, the user password, and the name of the server that is linked to the remote database

. If you do not specify a user, Oracle  
--The local user name and password will be used to establish a link to the remote database. Assuming that I am logged into the local

Oracle database with Admin/admin, then if user and password,oracle are not specified when I create the database-link, the  
Local username and password will be used admin/ Admin to Login to build a

database link.  
--connectstring is similar to this form of "127.0.0.1:1521/ORCL", which is host IP: port number/Database

service name. Where the port number defaults to 1521, host IP is local by default.  
-So when creating a local database link, connectstring can be written as ' ORCL ', which is the database service name.  
--Example of creating a database link: Create public database link Locallink connect to username 

identified by password using ' 127.0.0.1 : 1521/orcl ';

To use a remote database link:

Once we have created the remote database link, we can use it. Suppose we now create a database link to the remote database ORCL Orcllink, there is a table in ORCL's current user t_module, user Scott has a table emp, then we can access the data in the T_module in the following ways:

SELECT * FROM T_module@orcllink

But when we need to visit Scott's EMP table, we need to add a schema, which is accessed in the following way:

SELECT * FROM Scott.emp@mylink

We can also establish a remote synonym for T_module:

Create synonym Modulesyn for t_module@orcllink

When you create synonyms for other user tables such as Scott's EMP table, you need to add a schema, such as:

Create synonym Scottemp for scott.emp@orcllink

Once we have established a remote synonym, we can use the following access methods:

SELECT * from Modulesyn;  
SELECT * from Scottemp;

To delete a database link:

drop [public] database link databaselinkname;  
--The default is to delete private database links.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/