Copying data between tables on one database:
SQL> COPY FROM SCOTT/TIGER@MYDATABASE - INSERT EMPCOPY2 - USING SELECT * FROM EMPHere in the @mydatabase, what do I have to use? What is the default database name? I am using Oracle 220.127.116.11.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
- 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.
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.