Q

Copying tables from one DB to another via database link

I want to set up a database link to copy tables from one database to another. I followed all the steps: changed

global_name to TRUE in init.ora, modified the TNSNAMES.ORA file, setup remote database link. However when I want to copy the tables using the link, it still comes out: ORA-12154: TNS: could not resolve service name. My question is after I change the parameter in these files, should I restart the database? Or are there any other steps I should do? Could you please help me?

You do not need to restart the database to fix your problem. But you must correctly configure your TNSNAMES.ORA file. One thing that people don't understand about database links is that it will only look in $ORACLE_HOME/network/admin for a TNSNAMES.ORA file. And this file must reside on the server that the database is running! So correctly configure this file in this location for your database links. I always test database links by issuing the following query:

SELECT sysdate FROM dual@remotedb;
If I get the results back, then the link is working fine. Otherwise, fix the corresponding error.

For More Information


This was first published in September 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close