First blog and DBLink
Hi All,
This is Shaon and probably my first blog will gonna be proved as a boring one. Oracle is the platform where my earning and living depend on. Actually I am playing the role of DBA in an organization. So what I did in my workplace I try to document it with any form. For this purpose I initiated my blog and here i will try to share some silly knowledge with you who loves DBA and Oracle as well. Ok lets start with something like DBLink in Oracle.
PURPOSE:
------------------
CREATE A DBLINK FROM ORCL DATABASE TO CONNECT TO XLE SCHEMA IN DEVP DATABASE TO ACCESS THOSE TABLES WHICH HAS PRIVILEGES IN SOME TABLES OF DEVP DATABASE. #############################################################################################################################
PRE-REQ:
---------
CREATE A USER NAMED XCLE IN DEVP DB.GRANT REQUIRED PRIVILEGE TO XCLE SO THAT USER IN ORCL CAN DIRECTLY ACCESS ON THOSE TABLES OF DEVP DB. ############################################################################################################################
DBLINK CREATION:
----------------
CREATE DATABASE LINK DBLNK_DEVPRTP CONNECT TO XCLE IDENTIFIED BY xcle USING 'DEVP' ###########################################################################################################################
TESTING
-----------
SQL> select name from v$database@dblnk_devprtp;
NAME
---------
DEVP
SQL> insert into xcircle.tbl_dblink@dblnk_devp values (007,'shaon');
1 row created.
SQL> select * from xcircle.tbl_dblink@dblnk_devp;
ID NAME
---------- --------------------
7 shaon
SQL> rollback;
Rollback complete.
SQL> select * from xcle.tbl_dblink@dblnk_devp;
no rows selected ############################################################################################################################
Points to be noted:
-------------------
-------------------
While using the tnsname in dblink creation script be confirmed that what your system parameter's value is for global_names.
if it is false then tnsname should exclude full domain name.
Otherwise the tnsname should contain full domain name. If it is not followed you will get the below error
ERROR at line 1:
ORA-02085: database link .... connects to ...
to set that parameter false please put
This is Shaon and probably my first blog will gonna be proved as a boring one. Oracle is the platform where my earning and living depend on. Actually I am playing the role of DBA in an organization. So what I did in my workplace I try to document it with any form. For this purpose I initiated my blog and here i will try to share some silly knowledge with you who loves DBA and Oracle as well. Ok lets start with something like DBLink in Oracle.
PURPOSE:
------------------
CREATE A DBLINK FROM ORCL DATABASE TO CONNECT TO XLE SCHEMA IN DEVP DATABASE TO ACCESS THOSE TABLES WHICH HAS PRIVILEGES IN SOME TABLES OF DEVP DATABASE. #############################################################################################################################
PRE-REQ:
---------
CREATE A USER NAMED XCLE IN DEVP DB.GRANT REQUIRED PRIVILEGE TO XCLE SO THAT USER IN ORCL CAN DIRECTLY ACCESS ON THOSE TABLES OF DEVP DB. ############################################################################################################################
DBLINK CREATION:
----------------
CREATE DATABASE LINK DBLNK_DEVPRTP CONNECT TO XCLE IDENTIFIED BY xcle USING 'DEVP' ###########################################################################################################################
TESTING
-----------
SQL> select name from v$database@dblnk_devprtp;
NAME
---------
DEVP
SQL> insert into xcircle.tbl_dblink@dblnk_devp values (007,'shaon');
1 row created.
SQL> select * from xcircle.tbl_dblink@dblnk_devp;
ID NAME
---------- --------------------
7 shaon
SQL> rollback;
Rollback complete.
SQL> select * from xcle.tbl_dblink@dblnk_devp;
no rows selected ############################################################################################################################
Points to be noted:
-------------------
-------------------
While using the tnsname in dblink creation script be confirmed that what your system parameter's value is for global_names.
show parameter global_names
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
if it is false then tnsname should exclude full domain name.
Otherwise the tnsname should contain full domain name. If it is not followed you will get the below error
ERROR at line 1:
ORA-02085: database link .... connects to ...
to set that parameter false please put
SQL> alter system set global_names=false scope=both;
System altered.
Comments
Post a Comment