Oracle Create Dblink

CREATE Database Link Test_dblink
CONNECT to SJZX identified by SJZX
USING ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.176.150.201) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)‘;

Database Link Overview
Database link is an object that defines a path to another database, which allows you to query remote tables and execute remote programs. In any distributed environment, database is necessary. It is also important to note that the database link is a one-way connection.
When you create the database link, the Oracle Re-data dictionary holds the relevant database link information, and when you use database link, Oracle NET accesses the appropriate remote database with the user's pre-defined connection information to do the 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.
Database link Category

type	Owner	Description
Private	The user who created the database link owns the database link	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 owner can delete its own private database link.
Public	Owner is public.	Public database link is database-level, and all users with database access or PL/SQL programs in the local databases can use this link to access the appropriate remote database.
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 MA Nages 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. Note:in earlier releases of Oracle database, a global database link referred to a Database link, is registered with An Oracle Names server. The use of a Oracle Names server has been deprecated. In this document, global database links refer to the use of the net service names from the directory server.

Permissions required to create Dblink

for

Privilege	Database	Required
CREATE DATABASE LINK	Local	Creation of a private database link.
CREATE PUBLIC DATABASE LINK	Local	Creation of a public database link.
CREATE SESSION	Remote	Creation of any type of database link.

use of database link
Basic syntax
CREATE [shared][public] Database link link_name

      [connect to [user][current_user] identified by password]
       [authenticated by 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 to all users in the database, and a private link is available only to the user who created it. It is not possible for a user to grant a private database link to another user, a database link is either public or private.
2) Link:   when the source side of the database global_name=true, the link name must be the same as the remote database's global database name Global_name), otherwise, can be arbitrarily named.

3) Current_User uses this option to create a dblink of the global type. There are multiple databases in the distributed system. If you want to use the same name in every database to access database A, it is too cumbersome to create a db_link to database a in each database. So now there's this option. You 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. I have not created it, and there is no such environment.
4) ConnectString: Connection string, Tnsnames.ora defines the remote database connection string, or can be specified directly when creating the Dblink.
5) Username, password: The user name of the remote database, password. If you do not specify, log on to the remote database with the current user name and password, and 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

take value	description
Do not specify the	default to establish a dedicated connection, each local session using Database link will have a session for the remote database.
shared	creates a shared database connection, and you specify the DB link_authentication. The Database link using shared mode is the number of connections to the remote database that is restricted by the databases so that too many connections are too stressful for the remote database. When using the shared database link, the connection to the database link will be disconnected from the local connection after the connection is made to prevent the unauthorized session from using this link to create a shared database You must specify database link_authentication when you link. The from Oracle document:a shared database link is a link between a local server process and the remote database. The link is a shared because multiple client processes can use the same link simultaneously.)

Share links More information
A shared database link means that multiple users of the link can share the same underlying network connection. For example, in an MTS (multi-threaded server) environment with four users, each shared server process will have a physical link to the remote server, and the four users share the two links.
on the surface, sharing links at first glance sounds like a good thing. This is true in some environments, but when you consider using shared links, you should be aware that there are many limitations and warnings:
If you use a dedicated server connection to connect to your local database, the link can only be shared between multiple sessions that you create from those connections  . In an MTS environment, each shared server process potentially opens a link. All sessions are provided by the same shared server process and share any shared links opened by that process. Because a shared server process in an MTS environment can serve many user connections, the use of shared links can lead to more links being opened than necessary. Use the Shared keyword to establish a sharing database link. You must also use the authenticated by clause to specify a valid user name and password on the remote system. Create a shared, common, connected user database link as follows:
    create shared public db link GNIS
     authenticated by Dummy_user identified by SECRET
    using ' GNIS ';

For more information on creating links and managing distributed systems, refer to Oracle Technology Network (http://otn.oracle.com/).
Using the authenticated by clause is somewhat disturbing, but it is necessary to implement a shared link in a way that is safe. The username and password Dummy_user/secret In this example must be valid on the remote system. However, the account used on the remote system is still the account of the connecting user. If I log in to my local database with Jeff/secret and use the shared link I just built, the following sequence of events will occur:
To open the link, Oracle uses Dummy_user/secret to authorize the remote database. Then, Oracle tried to use Hmad/secret to make me log on to the remote database. The primary purpose of a shared link is to reduce the number of underlying network connections between two database servers. They are best suited for MTS environments where you have a large number of users who access remote databases through this link. The idea is that you want the number of users to exceed the number of shared server processes. Then you can save resources by opening a link for each shared server process instead of opening a linked method for each user.

How to use Database link

Take value	Description
Do not specify	Default value to create a private database link
Public	Public connection, such a connection can be accessed by all users of the data

Database link user authentication method

Take value	Description
Do not specify	Default value to take connected user authentication method
CONNECT to Current_User	Take the Current_User verification method
CONNECT to user_name identified by password	To take Fiexed user authentication method

Database Link Creation Example as

SQL Statement	connects to Database	connects	Link Type
CREATE Database Link sales.us.americas.acme_auto.com USING ' Sales_us ';	Sales using NET service name Sales_us	Connected User	Private Connected User
CREATE Database link foo CONNECT to Current_User USING ' Am_sls ';	Sales using service name Am_sls	Current global User	Private Current User
CREATE Database Link Sales.us.americas.acme_auto.com CONNECT to Scott identified by Tiger USING ' Sales_us ';	Sales using NET service name Sales_us	Scott using password Tiger	Private Fixed User
CREATE Public Database Link Sales CONNECT to Scott Identified by Tiger USING ' rev ';	Sales using NET service name Rev	Scott using password Tiger	Public fixed user
CREATE SHARED Public Database link Sales.us.americas.acme_auto.com CONNECT to Scott identified by Tiger authenticated by Anupam identified by Bhide USING ' sales ';	Sales using NET Service name Sales	Scott using password tiger, authenticated as Anupam using password Bhide	Shared Public Fixed User

Do not use the TNS name example:
CREATE Database Link Link_name
CONNECT to user identified by Screct
USING ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = sales.company.com) (PORT = 1521))
)
(Connect_data =
(service_name = sales)
)
)‘;
use of database link
--The simplest usage
SELECT * FROM [email protected] link;
-When you don't want people to know the name of the database link
--Build an alias to wrap it up
CREATE synonym table_name for [email protected] link;
SELECT * FROM table_name;
-Or, you can create a view to encapsulate
CREATE VIEW table_name as SELECT * from [email protected] link;
Database link Delete
--Delete the database link of the public type
DROP public database link link_name;
--Delete the non-public type of database link
-Note: Only owner can delete his or her own non-public type database link
DROP database link link_name;
View the database link information
View basic information about the system link
Dba_db_links (All_db_links/user_db_links)
The Dba_db_links view returns a row for each defined link. The owner column and the Db_link column show the owners and names of this link, respectively. For a public database link, the owner column will contain ' publicly '. If you establish a fixed user link, the user name should be username joins in the Dba_db_links view. All_db_links views and user_db_links views are similar to dba_db_links views-they show all the links you can access and all the links you have.
COL OWNER for A15
COL Db_link for A25
COL HOST for A25
COL USERNAME for A15
SELECT * from Dba_db_links;
Dba_objects (all_objects/user_objects)
In this view you can only query the system with the database link and their owner, creation time and other information.
COL OWNER for A15
COL object_name for A25
COL object_type for A25
SELECT Owner,object_name,object_type from dba_objects WHERE object_type like ' database link ';
View the saved user password stored by the Fixed users database link, which is encrypted.
This is a more dangerous thing, it is necessary to restrict the permissions of the table sys.link$.
Col Host for A15
Col userid for A15
Col Passwordx for A40
Col name for A20
Select Name, Host, UserID, PASSWORDX from sys.link$;
Is it a transaction to go through database link to select a table for the remote databases?
SELECT * from V$transaction;
-no record, no transaction
--Connect to the remote database via the database link, select one of the tables
SELECT * from [email protected]
SELECT * from V$transaction;
--Found a record.
Explain
Because the local database simply sends the corresponding SQL to the remote database for execution, accepting the results returned by remote DB, but he does not know if the SQL modifies the data, so you need to mark a transaction for the SELECT statement as well.
Specifically, you can refer to the OTN Distributed database manual, so when using Database link remote access, add set transaction read only;
Close Database Link
ALTER SESSION CLOSE Database link sales;

In fact, the corresponding properties of Dblink correspond to Oracle's data dictionary link$, and any operation against Dblink is an operation of the data dictionary. At 9i, if Oracle's global_name includes only db_name, that is, the value of Db_domain is empty. Then the database chain established at this time, after the database modified global name Global_name (modified to Db_name.db_domain format), will not be deleted.
If you want to create a database chain, you must change the Global_name back to db_name format, that is, remove the back domain, but this time, the rename operation will automatically add the domain name, so that the Oracle global name can not be restored to the original state. So in this case, if you need to delete Dblink, you can only manipulate the link$ data dictionary directly.

Delete from link$ where owner#=user_id and Name=dblink_name

Of course it is dangerous to manipulate the data dictionary directly, and it is best to do a backup before you do it.

Executing DDL with Dblink

We know that no DDL statements can be executed directly in Dblink, such as the following

Sql> desc db_test;
 Name                                        null?    type
 ------------------------------------------- ----------------------------------
 ID                                                    number ( );

sql> drop table [email protected]_connected_hr;
drop table [Email protected]_connected_hr
*
ERROR at line 1:
ORA-02021:DDL operations is not allowed on a remote database

DDL statements can be executed in Dblink by creating a stored procedure. Note You need to create a stored procedure under the appropriate user for the target database, as follows

sql> Create or Replace procedure P_execute_ddl (P_ddl in VARCHAR2)
2 AS
3 begin
4 Execute immediate P_DDL;
5 end;
6/

To delete a table from the target database

sql> exec [email protected]_connected_hr (' drop table db_test ');

PL/SQL procedure successfully completed.

sql> desc db_test;
ERROR:
Ora-04043:object Db_test does not exist

Limitations of Dblink
You cannot perform the following operations using database links:
Grant Privileges on Remote objects
Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:
Tables
Views
Procedures
Functions
Analyze Remote Objects
Define or enforce Referential integrity
Grant roles to the users in a remote database
Obtain nondefault roles on a remote database. For example, if Jane connects to the local database and executes a stored procedure that uses a fixed user link connecting As Scott, Jane receives Scott ' s default roles on the remote database. Jane cannot issue SET role to obtain a nondefault role.
Execute Hash query joins that use shared server connections
Use a current user link without authentication through SSL, password, or NT native authentication

Oracle Create Dblink