Explain the usage examples of oracle cross-database query dblink, oracledblink

Explain the usage examples of oracle cross-database query dblink, oracledblink

This example describes how to query dblink across oracle databases. We will share this with you for your reference. The details are as follows:

1. jobs before creation

Before creating a dblink, you must first check whether the user has the corresponding permissions. For specific users, after using sqlplus user/pwd to log on, execute the following statement:
Copy codeThe Code is as follows: select * from user_sys_privs t where t. privilege like upper ('% link % ');
Under the sys user, the result is:

SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
SYS CREATE PUBLIC DATABASE LINK NO

We can see that dblink has three permissions in the database:

Create database link -- the created dblink can only be used by the creator, but cannot be used by other users.
Create public database link -- public indicates that all users of the created dblink can use
Drop public database link -- delete a specified dblink

If you want to change the permissions of a user, you must modify them under the sys User:
Copy codeThe Code is as follows: grant create public database link, drop public database link to scott;
You can view dblink in either of the following ways:

①.Copy codeThe Code is as follows: select owner, object_name from dba_objects where object_type = 'database link ';
②.Copy codeThe Code is as follows: select * from dba_db_links;

2. Create a dblink

create public database link LINK_NAMEconnect to USRNAME identified by "PASSWORD"using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX)) )';

Note: using is followed by a string, which must not contain unnecessary spaces, otherwise the ORA-12514 will failIn the above Code, to facilitate reading the text, there may be space leading to errors, so it is OK to remove the space when using it.

Here LINK_NAM is the custom name; USERNAME and PASSWORD are the user name and PASSWORD in the specified oracle database. If SERVICE_NAME is not determined, you can obtain it using the following statement:
Copy codeThe Code is as follows: show parameter service_names;
Or
Copy codeThe Code is as follows: select name, value from v $ parameter where name = 'service _ names'
3. Use of dblink

Dblink is relatively simple to use. You can change the table name used to access a local table to the following format: [user.] table @ link_name.
Copy codeThe Code is as follows: select studentid from abc. studeng @ abc_ten;
4. Delete dblink

After determining the name of the dblink to be deleted, you can use the drop command to directly delete it:
Copy codeThe Code is as follows: drop public database link abc_ten;

I hope this article will help you with Oracle database programming.

Articles you may be interested in:

• Example of accessing MySQL through DBLink at Oracle10g
• Dblink syntax for oracle database creation on the client
• Introduction to Distributed Query of replicated table data in Oracle cross-Database Query
• Order by sorting and query IN Oracle Database are output IN the ORDER of IN conditions
• SQL syntax summary for querying by page in Oracle
• ORACLE Study Notes-Query
• Query table information in Oracle to obtain table fields and field comments
• SQL statement examples and detailed annotations for simple queries, restricted queries, and data sorting in Oracle