[PL/SQL] DBLink establishes data link

Posted Jun 28, 20202 min read

After reading this chapter you will learn the content

  1. Two ways to connect links
  2. Remove the link
  3. How to use the link

method one:

Select File New Database Link to enter the following interface:
Picture.png

Fill in the corresponding information
Name:the name of the link
UserName:User name(default user name:SCOTT)
Password:Password
Database:The name of the target library to be linked(If someone else hosts, please add the IP address)
337944-20180112155853707-38774803.png
Public:Whether it is public
Share:Whether to share

Method Two:
Directly under the Objects object, select Databases Links, right-click New, you can enter the interface above.
Picture.png

Method three:
Write SQL statement, Create [Public]Database link..

- Drop existing database link - Clear the existing Link
drop public database link dblink_name; - clear Link
- Create database link Link external database with SYS
create public database link dblink_name connect to SYSTEM using '192.168.1.73:1521/oracle';

If you just link to the database under the local user
Use SYS to link the ORCL database, the default user name SCOTT, password 123456

CREATE PUBLIC DATABASE LINK DBLINK_NAME
CONNECT TO SCOTT IDENTIFIED BY "123456" USING'ORCL';

Query and use:
Select * from table name@dblink_name;

SELECT * FROM EMP@DBLINK_NAME;

Picture.png

Remove link:
Or delete the corresponding link under the objects object.

drop public database link dblink_name;

View all link information:

SELECT * FROM USER_DB_LINKS; - Query all DBLINK connections in the current account
SELECT * FROM DBA_DB_LINKS; --Query all DBLINK connections in the current system
SELECT * FROM V$DBLINK - query the currently connected DBLINK connection

Source:

  1. Oracle creates Database Link
  2. Creation and deletion of dblink in Oracle
  3. oracle dblink configuration

Related Posts