Example: 1) SQL> create table xyx as select * from establish.estt_dept_master@fndn; ERROR at line 1: ORA-02019: connection description for remote database not found 2) create table ankush_engg.xyz@domain as select * from establish.estt_dept_master@fndn; ERROR at line 1: ORA-02021: DDL operations are not allowed on a remote databaseCan you answer the above problem?
First, you'll need to fire up the Net8 Assistant and create a Service entry for each of your databases. Or, you can do this manually, by modifying your TNSNAMES.ORA file found in %ORACLE_HOME%\network\admin to include an entry like the following:
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )Change 'ORCL' to be your database identifier or some other term to uniquely identify this service entry. It can be anything. In the ADDRESS list, make sure you change your HOST to be the machine your database runs on. Also make sure that the PORT is the port your Listener listens on (it defaults to 1521). Finally, the SERVICE_NAME entry must be your database identifier.
Make two service entries, one for ORCL and one for ORC0.
The next step is to create a database link from one database to another. For instance, sign on to the ORC0 instance. Then create a database link to the ORCL instance with the following command:
CREATE DATABASE LINK orcl_db CONNECT TO myuser IDENTIFIED BY mypassword USING 'orcl';You can name the database link anything you want. In the above command you are identifying the userid (myuser) and password(mypassword) of a valid database user in the ORCL instance. The USING clause tells the database link which Service name entry in your TNSNAMES.ORA file to use that we created above.
Your database link is now created. I always test my database link by issuing the following:
SELECT SYSDATE FROM DUAL@orcl_db;If it returns a value, everything is fine. If it returns an error, you'll have to resolve that error.
Now that the database link is set up, it is time to copy a table to this local database, from the remote database. This can be done simply as follows:
CREATE TABLE local_table AS SELECT * FROM remote_table@orcl_db;You are now on your way to using database links. The Oracle 8i Distributed Database Systems documentation has tons of other information you may want to look at.
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.
This was first published in July 2002