Oracle_ Advanced Features (3) Synonym and database link

First, synonym synonym
Connect sys/123 as SYSDBA;
SELECT * from EMP;
ORA-00942: Table or view does not exist
Create synonym emp for scott.emp;
SELECT * from EMP;

1. Definition
A synonym is an alias for a pattern object.
Can be a table or view, sequence, PL/SQL program unit, user-defined object type,
or another synonym for creating synonyms.
Because synonyms are only aliases, there is no need for additional storage in addition to storing their definitions in the data dictionary.

2. Use
Synonyms can simplify SQL statements for database users.
Synonyms can also be used to hide the identity and location of the underlying schema object.
If you must rename or move the underlying object, you only need to redefine the synonym.
A synonym-based application that can continue to work without modification.

3. Classification
Classification: Private synonyms, public synonyms.
Private synonyms in the same pattern as their owners, only their owners have control over their availability.
Public synonyms are owned by a group of users named publicly and can be accessed by every database user.

4. Syntax:
4.1 Create
Create [public] synonym Syn_name for <obj>;
Description
Private synonyms are not with the public keyword;
Common synonyms are the public keyword.
For ease of use, syn_name synonym names are generally consistent with the table names.

Example:
Create synonym emp for scott.emp;
Create public synonym dept for scott.dept;

SELECT * from EMP;
SELECT * FROM dept;
--alter user system identified by system;
4.2 Renaming (only for private synonyms)
Rename Syn_name to <new_syn>;
Example:
Rename EMP to syn_emp;
Rename syn_emp to EMP;
--rename Dept to Syn_dept;

4.3 delete
drop [public] synonym syn_name;
Description
Delete a private synonym without the public keyword;
You must take the Public keyword when you delete a common synonym.
Example:
drop synonym emp;
Drop public synonym dept;

4.4 Data Dictionary
SELECT * from dba_synonyms where synonym_name in (' EMP ', ' DEPT ')
SELECT * from all_synonyms;
SELECT * from user_synonyms;

5. Restrictions:
Try to use less common synonyms as they make database consolidation more difficult.
Overuse of public synonyms can cause namespace collisions between applications.
Synonyms are not inherently safe to control.
When you grant an object permission on a synonym, you are actually granting permissions on the underlying object.
Synonyms are used only as aliases for objects in the grant statement.

Second, Database link
1. Definition
The way that two Oracle DB instances communicate directly with each other.
Database link is an object that defines a path to another database.
Database link allows users to query tables in remote databases and execute remote programs.
In any distributed environment, the database link is necessary.
It is also important to note that the database link is a one-way connection.
When creating the database link, Oracle holds the relevant database link information in the Data dictionary,
Oracle NET uses user-defined connection information when using Database link
Access the appropriate remote database to perform the appropriate work.
Things to check before establishing database link:
Verify that the network connection from the local database to the remote database is normal and tnsping to be successful.
Verify that you have the appropriate access rights on the remote database.
Verify that the local account has permission to establish Dblink.
If you need to create a global DBLink, you need to first determine the user has permission to create DBLink:
SELECT * from User_sys_privs where privilege like Upper ('%database link% ');
If not, you need to use the SYSDBA role to empower the user:
Grant create public database link to username;

2.database Link Category
Divided into three categories: Private, Public, Global.
2.1 Private
The user who created the database link has the database link permission
The database link is established under a specific schema for the local databases.
Only the session of the schema that created the database link can use this database link to access the remote databases.
At the same time, only the owner can delete its own private database link.

2.2 Public
Owner is public.
The database link for public is at the db level,
All users in the local database who have access to the database or the PL/SQL program can use this data link
To access the appropriate remote database.
2.3 Global
Owner is public.
Global's database link is network-level,
When an Oracle network uses a directory server,
The directory server automatically create and manages global database links (as Net service names)
For every Oracle Database in the network.
Users and PL/SQL subprograms in any database can use a global link
To access objects in the corresponding remote database.

3. Permissions required to create Dblink
3.1 Create DATABASE link
Local creation of a private database link.
3.2 Create public database link
Local creation of a public database link.
3.3 Create session
Remote creation of any type of database link.

4. Steps
4.1 Checking the remote server network
Ping 192.168.1.105
4.2 Configuring the Local instance Name
Modify the Tnsnames.ora file or configure it with the Net Configuration Assistant tool
4.3 Log in to the remote database
IP, instance name, account, password, port
4.4 Creating Dblink
4.4.1 Private
Basic syntax
CREATE Database Link Link_name
[CONNECT to user_name identified by password]
[USING ' connect_string ']
Example:
Create DATABASE link DB connection name
Connect to User name identified by password
Using ' locally configured instance name '
Connect sys/123 as SYSDBA;
Create DATABASE link ORCL111 Connect to Scott identified by "123" using ' ORCL111 ';
SELECT * from [email protected];

Create Public database link orcl_111 Connect to Scott identified by "123" using ' ORCL111 ';
SELECT * from [email protected]_lv;

drop database link ORCL111;
SELECT * from [email protected];

4.4.2 Public
Basic syntax
CREATE Public Database Link Link_name
[CONNECT to user_name identified by password]
[USING ' connect_string ']

5. Using Dblink
SELECT * from table name @dblink_name;
Create or replace view name as (select field from user. Table name @dblink_name);
Create or replace synonym synonym name for table name @dblink_name;
SELECT [Email protected]_111 (' SMITH ') from dual; --Call a remote function
exec [email protected]_111 (1,10); --Invoke Remote procedure

6. Delete Dblink
drop database link dblink_name;

7. Restrictions
Creating a DBLink is simple, but locks appear in the background of use,
The way to view this lock is to go to the console to see or query the database.
Each time a dblink query is used, a connection is created with the remote database, and Dblink should not automatically release the connection.
If a large number of dblink queries are used, the number of connections to the Web project is insufficient, causing the system to not function properly.

8. Complete syntax
CREATE [shared][public] Database link link_name
[CONNECT to [user][current_user] identified by password]
[Authenticated by the user identified by password]
[USING ' connect_string ']

Description
1) Permissions:
The account that created the database link must have the system permissions of Create DB link or create public link.
The account used to log on to the remote database must have the CREATE session permission.
Both of these permissions are included in the Connect role (Create public link permissions in the DBA).
A public database link is available for all users in the database,
A private link is available only to the user who created it.
It is not possible for a user to authorize a private database link to another user.
A database link is either public or private.
2) Link:
When the source side of the database is Global_name=true,
The link name must be the same as the global database name global_name the remote database;
Otherwise, you can name it arbitrarily.
3) Current_User Use this option to create a global type of dblink.
There are multiple databases in the distributed system,
If you want to use the same name in every database to access database A,
It's too cumbersome to create a db_link to database a in each database.
So now there's this option, just create it once.
All databases can be accessed using this db_link.
To use this feature, you must have an Oracle NameServer or an Oracle directory server.
and the parameter of database a global_names=true.
4) ConnectString: Connection string,
The connection string for the remote database is defined in Tnsnames.ora, and can be specified directly when the Dblink is created.
5) Username, password: the user name and password of the remote database.
If not specified, log on to the remote database with the current user name and password,
When you create a connected user type of Dblink, you need the user name password for both sides of the database to be consistent if you use data dictionary validation.
Create DATABASE Link Option description
6) SHARED, not specified
Do not specify: The default value establishes a dedicated (private) connection, and each local session using Database link will have a session with a remote database.
Shared: Create a shared database connection, and specify databases Link_authentication.
Database link using shared mode is the number of connections to the remote database that are restricted by the databases so that excessive connections are too stressful for the remote database.
When using the shared database link, the connection to database link is disconnected from the local connection after the connection
To prevent unauthorized sessions from using this link, you must specify database link_authentication when creating a shared database link.
7) public, not specified
Do not specify: Default value to create a private database link
Public: A common connection, such that a connection can be accessed by all users of the data
8) Database Link user authentication method value Description
Do not specify: Default value to take connected user's authentication method
CONNECT to Current_User: Take Current_User authentication method
CONNECT to user_name identified by password: Take fiexed user authentication method

Oracle_ Advanced Features (3) Synonym and database link