adeelminhaj
asked on
Creating tables by selecting from a remote database
How can I create a table by a 'Select' statement from a remote database. E.g., I was creating a table ...
CREATE TABLE BALTRANS AS
( SELECT * FROM RS53E.BALTRANS@ADM );
there was an error, stating...
ORA-02019: connection description for remote database not found
CREATE TABLE BALTRANS AS
( SELECT * FROM RS53E.BALTRANS@ADM );
there was an error, stating...
ORA-02019: connection description for remote database not found
HI
first make database link then run your query
it'll work
---rmz---
first make database link then run your query
it'll work
---rmz---
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It was my first experience with Expert Exchange and it was really good.
Subject: ORA-02019 WHEN USING A DATABASE LINK
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 25-JUL-2000
Last Revision Date: 21-MAY-2001
Problem Description
-------------------
You attempt to use a database link and receive the following error message:
ORA-02019: "connection description for remote database not found"
Cause: The user attempted to connect or log in to a remote
database using a connection description that could not
be found.
Action: Specify an existing database link. Query the data
dictionary to see all existing database links. See
your operating system-specific SQL*Net documentation
for valid connection descriptors.
Problem Explanation
-------------------
This error can be reported for the following reasons:
1. You are trying to use a database link created by another user as a private
database link. If the 'PUBLIC' option is not specified in the create
database link statement, the database link will be created as a private
database link and will only be accessible to the user who created it.
2. You are trying to use a database link that was created without a connect
string. Although you will not receive an error when creating the database
link, at the moment you try to use the newly created database link you will
receive the error.
3. You are trying to use a database link that does not exist.
Solution Description
--------------------
Select from the following view:
SQL> col owner format a10
SQL> col db_link format a15
SQL> col username format a10
SQL> col host format a10
SQL> col created format a10
SQL> select * from all_db_links;
1. If you query ALL_DB_LINKS and receive 'no rows selected' or no rows
returned where the owner is PUBLIC, the database link was created without
the 'PUBLIC' option. Create a public database link as follows:
- Log in to SQL*Plus or SVRMGR.
- Create the public database link:
SQL> create public database link <dblink name>
2 connect to <user> identified by <password>
3 using '<connect string>';
- Query ALL_DB_LINKS and verify that the owner is PUBLIC.
Example:
~~~~~~~~
SQL> connect system/manager
Connected.
SQL> select * from all_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- -----------
SYSTEM STAR.WORLD SYSTEM STAR 25-JUL-00
SQL> create public database link star.world
2 connect to system identified by manager
3 using 'STAR';
Database link created.
SQL> select * from all_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- -----------
PUBLIC STAR.WORLD SYSTEM STAR 25-JUL-00
SYSTEM STAR.WORLD SYSTEM STAR 25-JUL-00
SQL> select * from global_name@star;
GLOBAL_NAME
--------------------------
STAR.WORLD
SQL> connect scott/tiger
Connected.
SQL> select * from global_name@star;
GLOBAL_NAME
--------------------------
STAR.WORLD
2. If you query ALL_DB_LINKS and there is nothing under the HOST column for the
database link you are trying to use, the connect string was not specified
when the database link was created. Recreate the database link with a
connect string as follows:
- Log in to SQL*Plus or SVRMGR.
- Drop the database link.
- Create the database link with a connect string.
The connect string is the alias specified in the tnsnames.ora.
SQL> create {public} database link <dblink name>
2 connect to <user> identified by <password>
3 using '<connect string>';
- Query ALL_DB_LINKS and verify that the HOST is specified.
Example:
~~~~~~~~
SQL> connect scott/tiger
Connected.
SQL> select * from all_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- -----------
SCOTT TREK.WORLD SCOTT 25-JUL-00
SQL> drop database link trek.world;
Database link dropped.
SQL> create database link trek.world
2 connect to scott identified by tiger
3 using 'TREK';
Database link created.
SQL> select * from all_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- -----------
SCOTT TREK.WORLD SCOTT TREK 25-JUL-00
SQL> select * from global_name@trek;
GLOBAL_NAME
--------------------------
TREK.WORLD
3. If you query ALL_DB_LINKS and you get 'no rows selected' either the database
link you are trying to use is a private database link owned by a different
user or the database link does not exist.
To verify if the database link is a private database link owned by a
different user, connect as a DBA user and run the following query:
SQL> select * from dba_db_links
2 where db_link = '<dblink name>;
If the database link does not exist, create it.
SYNTAX FOR CREATE DATABASE LINK
~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE {PUBLIC} DATABASE LINK <database link name>
CONNECT TO <username> IDENTIFIED BY <password>
USING '<connect string>';
Solution Explanation
--------------------
1. Creating a public database link will allow all users to access that link,
not just the user who created it.
2. Adding the connect string to the database link allows the connection to the
database to be established.
3. The database link must exist.
.