About database link in Distributed Oracle

Dblink is inevitably used for distributed databases, but there is very little information about dblink. Many books have mentioned it, but it is not detailed.

A database link defines a single-row communication channel from one Oracle database to another.
Before creating a connection, you must first have the Naming Service (connection string) of the remote database, which is the one described in tnsnames. ora, such as 'oradxp'
Test: $ tnsping oradxp
Attemping to contact (...) OK (30 ms ).
It indicates that the remote database oradxp can be accessed.

The syntax for creating a database link is:
Create public database link oradxp.cug.edu.cn using 'oradxp ';
Here, oradxp.cug.edu.cn must be the global name (SID) of the remote database, and 'oradxp 'is the remote connection string. The global name of the remote database can be logged on to the remote machine,
Select * From global_name;
.
Now you can use the link oradxp.cug.edu.cn to access the remote database.
For example
Select * From global_name@oradxp.cug.edu.cn;
Check whether the global name of the remote machine is obtained.
All select statements followed by the Link name are for remote access.
Note: The login name and password are the user name and password of the current login to the local machine. That is, if you are using a public link, you must have the same user/password as the local machine in the remote connection. For example, you log on to the local machine with AAA/AAA, and then execute
Select * From BBB.bbb@oradxp.cug.edu.cn;
The user accessing the remote data BBB. BBB uses the AAA password as AAA.
That is to say, on both the local machine and remote machine, the user's AAA password is AAA to be successfully executed!
Private links:
Create database link oradxp.cug.edu.cn connect to system identified by AAA;
This creates a private link to the system.
The difference between a private link and a public link is the limitation of the login name password. That is

No matter which user you use to log on to the local machine, execute
Select * From BBB.bbb@oradxp.cug.edu.cn;
The user and password used to access the remote data BBB. BBB are system/AAA.
Place the data link in the system table SYS. Link $.
You can view all links through the distribution> database link on the OEM console.
To delete a public link, you can
Drop public database link linkname;
For private links, log on with the corresponding user first, such as the above system/AAA
Then drop database link linkname;
Of course, it is equally feasible to directly delete records in the SYS. Link $ table.

---------------------------------------------

Unreliable dblink -- manually disable dblink [ref]

In csdn, the user asked about the problem of dblink closure. I have always kept a reserved attitude towards this dblink. It is best not to use it unless necessary.
When we use dblink to operate a remote ORACLE database, this dblink access will create a remote session separately, this session to the remote database, it will not automatically close the session established with the remote server after you establish it, so this is terrible. If a constant query produces many remote database sessions, if there are too many sessions, the query will fail, so as long as I keep a reserved attitude towards this dblink technology, I will try not to use her.
However, if dblink is used, we need to disable dblink in time. The following two methods are provided:
1.
Alter sesssion close database link <dblink_name>;
2.
Dbms_session.close_database_link (<dblink_name> );
After each connection, you need to go to commit and manually go to colse. It's really painful.

------------------------------------------

If the Oracle database is opened in read only mode, you cannot access the remote database through the DB link. As Long As DB link is used, Oracle must enable distributed transaction support even if only select is executed, and the transaction needs to allocate rollback segments, in read-only mode, there is no online rollback segment:

SQL> select 1 from dual @ lnk_db1;

Select 1 from dual @ lnk_db1

*

Error at line 1: ORA-16000: Database open for read-only access

Perform a test on a read write database. We can see that transactions are enabled through the DB link query and rollback segments are allocated.

SQL>select sid from v$mystat where rownum=1;       SID----------      1270SQL>select 1 from dual@lnk_db1;         1----------         1SQL>select s.sid,s.serial#,s.sql_hash_value,  2   r.segment_name,  3   t.xidusn,  4   t.xidslot,  5   t.xidsqn  6   from v$session s,  7   v$transaction t,  8   dba_rollback_segs r  9   where s.taddr = t.addr 10   and t.xidusn = r.segment_id(+);                                                  SID    SERIAL# SQL_HASH_VALUE SEGMENT_NA     XIDUSN    XIDSLOT     XIDSQN---------- ---------- -------------- ---------- ---------- ---------- ----------      1270      37655              0 _SYSSMU10$         10         45    2042124

We can see that session 1270 only executes a SELECT statement, but because dB link is used, an active transaction is started and a rollback segment _ syssmu10 $ is assigned.

Update:Qi Gong reminded me that there is still a way to bypass this problem. Oracle provides read-only transactions and does not need to use rollback segments.

SQL>select 1 from dual@lnk_db1;select 1 from dual@lnk_db1              *ERROR at line 1:ORA-16000: database open for read-only accessSQL>set transaction read only;Transaction set.SQL>select 1 from dual@lnk_db1;         1----------         1