Hi all!
This is my set up config for Public DB Link.
Please let me know whether the set up is OK!
I used both SERVICE_NAME and SID Name below in the
TNSNAMES.ORA Files and found both are working fine.
Rem /* Creation of Public Database Link */
Rem /* FROM SYSTEM USER GRANT CREATE PUBLIC DATABASE LINK PRIVILEGE TO SCOTT*/
Rem /* IN THE TNSNAMES.ORA FILE CONFIGURE THE FOLLOWING */
Rem IN THE LOCAL TEST DB I HAVE SET GLOBAL_NAMES = FALSE(DEFAULT).
Rem NOW EVEN IN THE DBA STUDIO BY PRESSING THE TEST BUTTON,ORACLE SHOWS DB LINK IS ACTIVE.
Rem IN THE REMOTE DB I HAVE SET GLOBAL_NAMES = TRUE.
Rem IN SQL*NET FILE OF TEST DB(LOCAL),I HAVE USED THE FOLLOWING:
# SQLNET.ORA Network Configuration File: D:\Oracle\Oracle8i\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
#NAMES.DEFAULT_DOMAIN = TEST.ITMIT
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
# TNSNAMES.ORA Network Configuration File: D:\Oracle\Oracle8i\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
/* remotesite =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PGVL)
(GLOBAL_NAME = PGVL.ITMIT)
)
)
pgvl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PGVL)
)
) */
SQL> conn scott/tiger@test
Connected.
SQL> create public database link PGVL.ITMIT
2 connect to scott identified by tiger
3 using 'remotesite';
Database link created.
SQL> select * from [email protected]
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
9999 SMITH CLERK 7902 17-DEC-80 4000
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> show user
USER is "SCOTT"
SQL> select * from global_name
SQL> /
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST
SQL> conn scott/tiger@pgvl
Connected.
SQL> select * from [email protected];
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> conn scott/tiger@test
Connected.
SQL> select * from [email protected];
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> sho user
USER is "SCOTT"
SQL> select * from [email protected];
GLOBAL_NAME
--------------------------------------------------------------------------------
PGVL.ITMIT
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
TEST
SQL>
Thanks&Regards,
Amit.
