Dblink detailed
1. Create the Dblink syntax:
CREATE [public] DATABASE link link
CONNECT to username identified by password
USING ' ConnectString '
Description
1) Permissions: The account that created the database link must have the system permissions of Create databases link or create public database link, and the account used to log on to the remote databases 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 Global_name=true, the link name must be the same as the global database name Global_name of the remote database; otherwise, it can be named arbitrarily.
3) ConnectString: Connection string, Tnsnames.ora defines the remote database connection string.
4) Username, password: the user name and password of the remote database. If not specified, the current user name and password are used to log on to the remote database.
2. Delete the database link statement:
DROP [public] DATABASE LINK Zrhs_link
3. View the dblink you have created
Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';
References to 4.dblink:
[User.] Table|view@dblink
Such as:
SELECT * from [email protected]_link;
SELECT * from [email protected]_link;
5. Create synonyms:
For frequently used database links, you can create a local synonym for ease of use:
CREATE synonym Worker_syn for [email protected]_link;
6. Create a remote view:
CREATE VIEW worker as SELECT * from [email protected]_link where ...;
This view can now be treated like any other view in the local database, or it can be authorized for other users to access the view, but the user must have access to the database link.
Other:
To modify the Global_name method:
1. Set Global_names to False in the Init.ora file of the remote database.
Or
2. Execute the following statement with the SYS user: ALTER SYSTEM SET global_name=true/false;
You can restart the database settings after you modify it to take effect.
The database global name can be detected with the following command: SELECT * from Global_name;
===== Dblink Combat ======
1.dblink is divided into public and private categories.
The public Dblink uses the publicly modifier keyword. You need to use the Public keyword for both create and drop.
Public Dblink are open to all, and synonyms created on top of the Dblink are also open to all. (Test and confirm, but test is performed between multiple users in one instance)
Private Dblink can only be accessed by the creator, and synonyms on it cannot be accessed by other users. Users will not be able to access the view until the user is created with a view and the view is authorized to the desired user.
In addition, a synonym with Dblink cannot be directly authorized to the user. Otherwise, the error is equivalent to:
Grant Select on [e-mail protected] to User2 *
ERROR at line 1:
ORA-02021:DDL operations is not allowed on a remote database
2. When creating Dblink, you can use the connection string (with Tnsname.ora) for greater efficiency.
--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------
Note: The account that created the database link must have the system permissions of Create DB link or create public database link, and the account used to log on to the remote databases must have the Create session permission. Both of these permissions are included in the Connect role (CREATE public link permissions in the DBA).
Two different database servers, one user from one database server, reads data from a user under another database server, which can use Dblink.
In fact, Dblink and the database in the same view, build dblink need to know the IP address of the database to be read, SSID and database user name and password.
There are two ways to create:
1. Local Service has been configured
Create Public database
Link fwq12 Connect to fzept
Identified by Neu using ' fjept ' Create database link name connect to user name identified by password using ' locally configured data instance name ';
2. Local Service Not Configured
Create DATABASE link Linkfwq
Connect to fzept identified by Neu
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.142.202.12) (PORT = 1521))
)
(Connect_data =
(service_name = fjept)
)
)‘;
host= the IP address of the database, service_name= the SSID of the database.
In fact, the two methods of configuration dblink is similar, I personally feel that the second method is better, this is not affected by local services.
The database connection string can be NET8 easy config or directly modify the TNSNames. Ora definition.
Database parameter global_name=true requires the database link name to be the same as the remote database name
The database global name can be isolated with the following command
Select * from Global_name;
Querying tables in the remote database
Select ... From table name @ database link name;
Querying, deleting, and inserting data is the same as manipulating the local database, except that the table name needs to be written as a "table name @dblink Server".
Synonyms are created:
Create synonym synonym name for table name;
Create synonym synonym name for table name @ database link name;
Delete Dblink:drop public DATABASE LINK Linkfwq.
If you create a global dblink, you must use the SYSTM or SYS user to add public before database.
Oracle dblink resolves a lock in the background
Creation is very simple, but in the background of the use of the lock, the way to view the lock can go to the console to see or query the database. Actually, this lock down
Not the most annoying, but every time you use the Dblink query, will create a connection with the remote database, Dblink should not automatically release this 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.
Example:
Create DATABASE link Hr_tables
Connect to Manbu
Identified by "admin"
Using ' WALK '
Where Hr_tables is an alias for the remote database
Manbu is the database user name of the remote database
Admin for Manbu Password
Walk is the SID of the database on the local database (specifically, the service name of the local database server. A service needs to be established on the local server Tnsname.ora. Instead of being built on the client. ）
Next, we can do a test, for example, we want to get a table data of the database, then we can do the following test under the name line:
Select user_name from [email protected]_tables;
Each table must be prefixed with a @hr_tables string indicating that the table is the form of the remote database that Dblink points to
Or, you might find this a lot of trouble, so we can configure an alias for [email Protected]_tables, that's no need to bother.
Create [email protected]_tables synonyms, also aliases:
Create synonym Mall_user for [email protected]_tables;
Oracle uses Dblink-detailed
