Ask the Expert

Accessing data from two databases

I have two databases, orcl & orc0 respectively. I made two services for above databases, and I want to access some data from one databse to another database, but I could not access the data.
 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 database 
 
Can you answer the above problem?

    Requires Free Membership to View

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


This was first published in July 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: