Database Link
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Database Link as PDF for free.
More details
- Words: 236
- Pages: 1
Doc By – Mahesh Devalekar
DBA
Database Link Scenario:Remote Database SID = ‘testbed’ # IP = 10.0.0.247 Local Database SID = ‘orcl’ # IP = 10.0.0.36 The data of user in the remote database you want to access - here e.g. ‘mdcc’
Procedure:First check the parameter db_domain in the init parameter file of remote database (inittestbed.ora) has set or not by following sql in the remote database. SQL> show parameter db_domain 1. If this parameter is not set then Edit the inittestbed.ora file by adding one entry in it, db_domain = “world” 2.Shut down the remote database ie “testbed” 3.Startup the remote database 4.Edit the tnsnames.ora file of local database ie “orcl” by adding testbed247 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.247) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testbed) (global_dbname = testbed.world) ) )
Connect to local database by system user To create the database link run the following querySQL> create public database link datavsn connect to mdcc identified by mdcc using ‘testbed247’; */(where ‘testbed247’ is the net service name in the tnsnames.ora file of local database)/* Now any user in the local database can access the data of the mdcc user in the remote database by running following querySQL> select * from tab@datavsn; Note:- I) To drop the database link, SQL> drop public database link datavsn; 1. To view the information on database link, refer dba_db_links view. ------------------------------------------------------------------------------------------------------------
DBA
Database Link Scenario:Remote Database SID = ‘testbed’ # IP = 10.0.0.247 Local Database SID = ‘orcl’ # IP = 10.0.0.36 The data of user in the remote database you want to access - here e.g. ‘mdcc’
Procedure:First check the parameter db_domain in the init parameter file of remote database (inittestbed.ora) has set or not by following sql in the remote database. SQL> show parameter db_domain 1. If this parameter is not set then Edit the inittestbed.ora file by adding one entry in it, db_domain = “world” 2.Shut down the remote database ie “testbed” 3.Startup the remote database 4.Edit the tnsnames.ora file of local database ie “orcl” by adding testbed247 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.247) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testbed) (global_dbname = testbed.world) ) )
Connect to local database by system user To create the database link run the following querySQL> create public database link datavsn connect to mdcc identified by mdcc using ‘testbed247’; */(where ‘testbed247’ is the net service name in the tnsnames.ora file of local database)/* Now any user in the local database can access the data of the mdcc user in the remote database by running following querySQL> select * from tab@datavsn; Note:- I) To drop the database link, SQL> drop public database link datavsn; 1. To view the information on database link, refer dba_db_links view. ------------------------------------------------------------------------------------------------------------
