Q
Problem solve Get help with specific problems with your technologies, process and projects.

"mydatabase" and database links

Copying data between tables on one database:

 SQL> COPY FROM SCOTT/TIGER@MYDATABASE -
   INSERT EMPCOPY2 -
   USING SELECT * FROM EMP
Here in the @mydatabase, what do I have to use? What is the default database name? I am using Oracle 8.0.5.0.0, user name scott.

The item called "mydatabase" is a database link to a remote database. To create a database link, use the following command:

CREATE DATABASE LINK my_link CONNECT TO scott
IDENTIFIED BY tiger USING 'tns_entry';
The username/password for the remote database is already coded in the database link! So you do not need to include it in your SQL statement. If the userid is not included in the database link definition, then it will assume your current userid. You cannot override this behavior. If the password is omitted from the link definition, then you will be prompted for the password. To see how your database link is defined, query USER_DB_LINKS.

Once you have the link set up correctly, you can copy a table to your database by using the CTAS (Create Table As Select) method. It's really easy. For example:

CREATE TABLE my_table AS SELECT * FROM
my_table@my_link;
Notice the "my_table@my_link" in the FROM clause. The "@my_link" tells SQL that this table is in a remote database. The definition for "my_link" tells how to sign on to that remote database. The table that will be the source table in the remote database is "my_table".

For More Information


Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close