Что такое dblink в oracle
Что такое dblink в oracle
Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
Oracle Database Development Guide for information about accessing remote tables or views with PL/SQL functions, procedures, packages, and data types
Oracle Database Administrator’s Guide for information on distributed database systems
ALTER DATABASE LINK for information on altering a database link when the password of a connection or authentication user changes.
DROP DATABASE LINK for information on dropping existing database links
INSERT, UPDATE, DELETE, and LOCK TABLE for using links in DML operations
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle Database.
Oracle Net must be installed on both the local and remote Oracle Databases.
Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database. In a shared server configuration, shared database links can keep the number of connections into the remote database from becoming too large. Shared links are typically also public database links. However, a shared private database link can be useful when many clients access the same local schema, and therefore use the same private database link.
In a shared database link, multiple sessions in the source database share the same connection to the target database. Once a session is established on the target database, that session is disassociated from the connection, to make the connection available to another session on the source database. To prevent an unauthorized session from attempting to connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for the users authorized to use the database link.
Oracle Database Administrator’s Guide for more information about shared database links
Specify PUBLIC to create a public database link visible to all users. If you omit this clause, then the database link is private and is available only to you.
The data accessible on the remote database depends on the identity the database link uses when connecting to the remote database:
If you omit both of those clauses, then the database link connects to the remote database as the locally connected user.
Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.
The maximum number of database links that can be open in one session or one instance of an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links
«References to Objects in Remote Databases» for guidelines for naming database links
«RENAME GLOBAL_NAME Clause» (an ALTER DATABASE clause) for information on changing the database global name
The CONNECT TO clause lets you specify the user and credentials, if any, to be used to connect to the remote database.
If the database link is used directly rather than from within a stored object, then the current user is the same as the connected user.
CREATE FUNCTION for more information on invoker-rights functions
user IDENTIFIED BY password
You can specify this clause only if you are creating a shared database link—that is, you have specified the SHARED clause. Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
USING ‘ connect string ‘
Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Oracle Database Administrator’s Guide for information on specifying remote databases
Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote :
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
After this database link is created, hr can query tables in the schema hr on the local database in this manner:
User hr can also use DML statements to modify data on the local database:
Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
Что такое dblink в oracle
Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
Oracle Database Development Guide for information about accessing remote tables or views with PL/SQL functions, procedures, packages, and data types
Oracle Database Administrator’s Guide for information on distributed database systems
ALTER DATABASE LINK for information on altering a database link when the password of a connection or authentication user changes.
DROP DATABASE LINK for information on dropping existing database links
INSERT, UPDATE, DELETE, and LOCK TABLE for using links in DML operations
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle Database.
Oracle Net must be installed on both the local and remote Oracle Databases.
Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database. In a shared server configuration, shared database links can keep the number of connections into the remote database from becoming too large. Shared links are typically also public database links. However, a shared private database link can be useful when many clients access the same local schema, and therefore use the same private database link.
In a shared database link, multiple sessions in the source database share the same connection to the target database. Once a session is established on the target database, that session is disassociated from the connection, to make the connection available to another session on the source database. To prevent an unauthorized session from attempting to connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for the users authorized to use the database link.
Oracle Database Administrator’s Guide for more information about shared database links
Specify PUBLIC to create a public database link visible to all users. If you omit this clause, then the database link is private and is available only to you.
The data accessible on the remote database depends on the identity the database link uses when connecting to the remote database:
If you omit both of those clauses, then the database link connects to the remote database as the locally connected user.
Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.
The maximum number of database links that can be open in one session or one instance of an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links
«References to Objects in Remote Databases» for guidelines for naming database links
«RENAME GLOBAL_NAME Clause» (an ALTER DATABASE clause) for information on changing the database global name
The CONNECT TO clause lets you specify the user and credentials, if any, to be used to connect to the remote database.
If the database link is used directly rather than from within a stored object, then the current user is the same as the connected user.
CREATE FUNCTION for more information on invoker-rights functions
user IDENTIFIED BY password
You can specify this clause only if you are creating a shared database link—that is, you have specified the SHARED clause. Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
USING ‘ connect string ‘
Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Oracle Database Administrator’s Guide for information on specifying remote databases
Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote :
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
After this database link is created, hr can query tables in the schema hr on the local database in this manner:
User hr can also use DML statements to modify data on the local database:
Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
CREATE DATABASE LINK
Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
Oracle Database Administrator’s Guide for information on distributed database systems
DROP DATABASE LINK for information on dropping existing database links
INSERT, UPDATE, DELETE, and LOCK TABLE for using links in DML operations
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Oracle Net must be installed on both the local and remote Oracle databases.
Description of the illustration create_database_link.gif
Description of the illustration dblink_authentication.gif
Keyword and Parameters
Oracle Database Administrator’s Guide for more information about shared database links
Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.
Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.
The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
«Referring to Objects in Remote Databases» for guidelines for naming database links
«RENAME GLOBAL_NAME Clause» (an ALTER DATABASE clause) for information on changing the database global name
The CONNECT TO clause lets you enable a connection to the remote database. You can specify this clause and the dblink_authentication clause only if you are creating a shared database link.
If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
CREATE FUNCTION for more information on invoker-rights functions
user IDENTIFIED BY password
Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
You must specify this clause when you specify the SHARED clause. You cannot specify this clause unless you specify the SHARED clause.
USING ‘ connect string ‘
Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Oracle Database Administrator’s Guide for information on specifying remote databases
Defining a Public Database Link: Example The following statement defines a shared public database link named remote that refers to the database specified by the service name remote :
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
Defining a Fixed-User Database Link: Example In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
After this database link is created, hr can query tables in the schema hr on the local database in this manner:
User hr can also use DML statements to modify data on the local database:
Defining a CURRENT_USER Database Link: Example The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
Что такое dblink в oracle
Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
Oracle Database Development Guide for information about accessing remote tables or views with PL/SQL functions, procedures, packages, and data types
Oracle Database Administrator’s Guide for information on distributed database systems
ALTER DATABASE LINK for information on altering a database link when the password of a connection or authentication user changes.
DROP DATABASE LINK for information on dropping existing database links
INSERT, UPDATE, DELETE, and LOCK TABLE for using links in DML operations
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle Database.
Oracle Net must be installed on both the local and remote Oracle Databases.
Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database. In a shared server configuration, shared database links can keep the number of connections into the remote database from becoming too large. Shared links are typically also public database links. However, a shared private database link can be useful when many clients access the same local schema, and therefore use the same private database link.
In a shared database link, multiple sessions in the source database share the same connection to the target database. Once a session is established on the target database, that session is disassociated from the connection, to make the connection available to another session on the source database. To prevent an unauthorized session from attempting to connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for the users authorized to use the database link.
Oracle Database Administrator’s Guide for more information about shared database links
Specify PUBLIC to create a public database link visible to all users. If you omit this clause, then the database link is private and is available only to you.
The data accessible on the remote database depends on the identity the database link uses when connecting to the remote database:
If you omit both of those clauses, then the database link connects to the remote database as the locally connected user.
Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.
The maximum number of database links that can be open in one session or one instance of an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links
«References to Objects in Remote Databases» for guidelines for naming database links
«RENAME GLOBAL_NAME Clause» (an ALTER DATABASE clause) for information on changing the database global name
The CONNECT TO clause lets you specify the user and credentials, if any, to be used to connect to the remote database.
If the database link is used directly rather than from within a stored object, then the current user is the same as the connected user.
CREATE FUNCTION for more information on invoker-rights functions
user IDENTIFIED BY password
You can specify this clause only if you are creating a shared database link—that is, you have specified the SHARED clause. Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
USING ‘ connect string ‘
Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Oracle Database Administrator’s Guide for information on specifying remote databases
Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote :
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
After this database link is created, hr can query tables in the schema hr on the local database in this manner:
User hr can also use DML statements to modify data on the local database:
Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
Связь базы данных Oracle (database link) — это одностороннее соединение локальной базы данных с удаленной базой данных. Связь всегда односторонняя. Пользователи удаленной базы не могут применять ее для подключения к локальной базе — вместо этого они должны создать отдельную связь базы данных.
Database Link (cвязь) базы данных позволяет получать доступ к разным базам данных через учетную запись пользователя удаленной базы; вы сами не обязаны быть зарегистрированным пользователем удаленной базы данных. Привилегии в этой базе данных будут идентичны привилегиям пользовательской учетной записи, которая применялась для создания связи. Связи баз данных удобны, когда необходимо запросить таблицу в распределенной базе данных, или даже вставить данные из таблицы другой базы в собственную локальную таблицу. Связи баз данных позволяют пользователям обращаться к множеству баз данных как к единой логической базе данных.
Создавать можно приватные и общедоступные связи баз данных. Далее мы рассмотрим соответствующие примеры.
Создание приватной связи базы данных
Приватная связь базы данных принадлежит пользователю, который ее создал. В следующем операторе пользователь SYSTEM создает приватную связь базы данных. Связь базы данных позволяет устанавливать соединение с удаленной базой, используя имя и пароль пользователя hr этой удаленной базы.
После создания связи пользователь SYSTEM может выполнять запросы к таблице hr.employees в удаленной базе данных.
На заметку! Для создания связи базы данных пользователь должен иметь привилегию CREATE PRIVATE DATABASE LINK или CREATE PUBLIC DATABASE LINK в локальной базе данных
Обратите внимание, что в предыдущем операторе имя связи удаленной базы данных — MONITOR — совпадает с TNS-псевдонимом удаленной базы (псевдонимом Oracle Net Service), но вообще может быть любым, по вашему усмотрению. Конструкция CONNECT TO. IDENTIFIED BY означает, что пользователь этой связи базы данных применяет указанное имя и пароль для входа на удаленную базу данных. Конструкция USING ‘monitor’ просто специфицирует TNS-псевдоним удаленной базы данных.
Поскольку это приватная связь базы данных, только пользователь SYSTEM может использовать ее. Когда пользователь hr попытается воспользоваться этой связью удаленной базы данных, произойдет следующее:
Создание общедоступной связи базы данных
Общедоступная связь базы данных позволяет любому пользователю или любому модулю программы PL/SQL обращаться к объектам удаленной базы данных. Оператор создания такой связи очень похож на оператор создания приватной связи. К оператору CREATE DATABASE LINK просто добавляется ключевое слово PUBLIC :
Совет. Если нескольким пользователям требуется доступ к удаленной базе данных Oracle из локальной, можно создать общедоступную связь. В противном случае создавайте приватную связь базы данных, которая позволяет обращаться к объектам удаленной базы данных только владельцу.
На заметку! Пользователь tester может обращаться к удаленной базе, даже не будучи ее зарегистрированным пользователем, потому что применяет общедоступную связь, что избавляет его от необходимости указывать комбинацию имени и пароля пользователя hr для доступа к объектам удаленной базы. Разумеется, с точки зрения безопасности общедоступная связь базы данных — не слишком хорошая идея, особенно с точки зрения аудиторов базы данных.
Использование Database Control для создания связей баз данных
С помощью OEM Database Control очень легко создать связь базы данных. Для этого на домашней странице Database Control нужно щелкнуть на вкладке Administration (Администрирование), а затем на ссылке Database Links (Связи базы данных) в группе Schema (Схема). На этой странице можно создать связь базы данных, ответив на несколько простых вопросов.