ORACLE DATABASE LINK(DBLINK)
CREATE METHOD
- FIXED USER EXAMPL
- PUBLIC EXAMPLE
- DBLINK CREATE COMMAND
CURRENT USER
DBLINK CREATE COMMAND:
sql> CREATE DATABASE
LINK MYLINLK
CONNECT TO HR
USING 'EMPLOYEE';
FIXED USER BDLINK CREATE COMMAND
The following statement defines a fixed-user database link named MYLINK:CREATE DATABASE LINK MYLINK
CONNECT TO scott IDENTIFIED BY tiger
USING 'EMPLOYEE';
Once this
database link is created, you can query tables in the schema SCOTT on the
remote database in this manner:SELECT *
FROM emp@MYLINK;
With mylink any DML sql to modify data on the remote database:INSERT INTO emp@mylink(emp_no,emp_id,emp_name )
VALUES (1, 12000,’test’);
UPDATE emp @ mylink
SET emp_name = ‘niger’;
DELETE FROM emp@mylink
WHERE emp_name = 'niger';
How to get sequence.nextval through oracle database link
select schema.s_test_seq.nextval@remote
from dual;
NEXTVAL
----------
1
DESCRIBING
OBJECT WITH DBLINKDESC S_PM_EDU_QUALIFICATIONS@MYLINK
DESC TABLE/SEQUENCE/TRIGGER/ETC@MYLINK
HOW TO ALTER/CHANGE SEQUENCE SCRIPT/INCREAMENT VALUE CHANGE FOR SQEUENCE WITH ORACLE DBLINK :
Following command is show remote sequence nextval:
SELECT
S_PM_EDU_QUALIFICATIONS.nextval@MYLINK source_seq,
S_PM_EDU_QUALIFICATIONS.nextval
S_PM_EDU_QUALIFICATIONS
FROM dual
--------------------------------------
CREATE OR REPLACE
PROCEDURE
reset_sequence
AS
l_source_sequence pls_integer;
l_target_sequence pls_integer;
l_sql VARCHAR2(100);
BEGIN
SELECT
S_PM_EDU_QUALIFICATIONS.nextval@MYLINK,
PMIS.S_PM_EDU_QUALIFICATIONS.nextval
INTO
l_source_sequence,
l_target_sequence
FROM dual;
l_sql :='alter
sequence S_PM_EDU_QUALIFICATIONS START
WITH 368'||to_number(l_source_sequence-l_target_sequence);
EXECUTE immediate l_sql;
COMMIT;
END
reset_sequence;
/
Sequence is modifies
on remote database.
SELECT *
FROM pmis.department@mylink;
The previous
statement connects to the user SCOTT on the remote database and then queries
pmis’s DEPARTMENT table. YOU MAY CREATE synonym to hide the fact that SCOTT's EMP table is on a remote database. Below statement causes all future references to EMP to access a remote EMP table owned by SCOTT:
CREATE SYNONYM emp
FOR scott.emp@mylink;
PUBLIC Example
The following statement defines a shared public fixed user dblink named ‘mylink’that refers to user SCOTT with password TIGER on the database specified by the string service name 'employee':
CREATE SHARED PUBLIC DATABASE LINK mylink
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY niger IDENTIFIED BY 123
USING 'employee';
Use of the CREATE DATABASE LINK Command
NB: All of the following examples will use DECNET and TCP/IP format.Let's assume we have two instances, the local instance and an instance in Newyork called New2, and there are communicating via TCP/IP. The node address in Newyork is 170.222.00.12 and the account we will connect to is LIMITED_USER with a password of NONE.
SQL> CREATE PUBLIC DATABASE LINK USA
CONNECT TO LIMITED_USER IDENTIFIED BY NONE
USING 'N: 170.222.00.12: NEW2';
USA db link
IS CREATE, now access tables that the LIMITED_USER has privileges on, for example,
the country table. To get information from the table we could now issue the
command:SQL> SELECT * FROM country@usa;
If you don't
want the users to know they are accessing a table in the remote database, you can
hide this object by synonym:SQL> CREATE PUBLIC SYNONYM country FOR country@usa;
You may read
from synonym:SQL> SELECT * FROM country;
You can
access views, tables, clusters and sequences via db links and connect strings.Example VMS DATABASE LINK
Unless network proxies are used under DECNET for VMS, must specify a username and password for connection string. The connect string format is:NODE "username password " : : " TASK=ORDNsid"
The TASK variable is actually a command procedure located in either the ORA_NETCONFIG directory or in the user's top level directory. Some sites set up a non-privileged, non-interactive account to connect through, others use net proxies. What your site uses will depend on your site security policy and the policies of your system administrator.
The entire connect string can be loaded into a logical symbol:
$define/nolog usa usa1 " " " sqlnet sqlnet " " " : : " " " TASK=ORDNTEST1 " " "
The triple
sets of " allow a single set of quotes to remain in the logical symbol.
The command to create the DB LINK now becomes:SQL> CREATE PUBLIC DATABASE LINK USA
CONNECT TO LIMITED_USER IDENTIFIED BY NONE
USING 'D:USA';
If you are
using DATABROWSER some versions will not allow the use of logicals and the entire
connect string definition will have to be loaded into the db link.
