|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
To access two databases in the same sqlplus session, such as to copy records from a table in one database to the same table in another database directly without having to go through export/import, you can create a database link to name the "remote" database, and, then, use your normal SQL commands to access that remote database by appending that name suffix onto the referenced tables for that remote database. (This requires dba privileges, and may not work between databases with different Oracle versions. Thanks to Pamela Creek for this information.)
Before you can do this, you will have to copy (or ftp) your latest tnsnames.ora file from the orawin\network\admin directory on your PC's client network (Novell, etc.) to the $ORACLE_HOME/network/admin directory on your server (RS/6000, etc.). You will also have to put the parameter "distributed_transactions = 12" (or, some larger value) into the init.ora files (such as $ORACLE_HOME/dbs/initPROD.ora) of BOTH your current and remote databases, and shutdown and startup both of them to put that parameter into effect. (If that parameter with a sufficiently large value is already in both init.ora files, you don't have to restart your databases.) Also, if either database was created using the cloning procedure, the global_name table for the clone must have been changed to match the new SID; otherwise, you'll get a "loopback" error.
To create the database link in sqlplus, such as for pprd_db (the database connect string name you use to log into Banner, which is defined in tnsnames.ora), with a name of pprd for user ID myuserid with a password of mypswd, enter the following command:
create database link pprd connect to myuserid identified by mypswd using 'pprd_db';
Then, you can access the tables in pprd_db by suffixing their names with @pprd, such as in the following command, which copies newer address type codes from the pprd database to the current (. oraenv) prod database (prefix the table name with the table owner if needed, such as saturn.stvatyp@pprd):
insert into stvatyp select * from stvatyp@pprd where stvatyp_activity_date > to_date('15-Jan-01');
After you have finished working with the remote database, drop the database link (otherwise, it stays around in the database between sessions):
drop database link pprd;
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
