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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.