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

Moving data from one database to the next without export/import

Is there a way to move data from one database to the next -- preferably the entire database -- besides creating a script to "create ... as select * from ... ". Exporting the database is reading from the database and writing to the filesystem, then importing is again reading from the dump file and writing to the database! What I effectively want to do is have one 'read' (export) and one 'write' (import). Would you know of any tricks to doing this?

So, since I think you're telling me you don't want to do an export/import (likely the most efficient way to do what you're asking) and you don't want to use CREATE TABLE AS method, you want to know what other choices are available, right?

First, you could use INSERT...SELECT:

   INSERT INTO emp@db1 SELECT * FROM emp@db2 ;
Second, you could use the SQL*Plus COPY command.
 COPY
 ----
COPY copies data from a query to a table in a local or remote database.
 COPY {FROM database | TO database | FROM database TO database}
            {APPEND|CREATE|INSERT|REPLACE} destination_table
            [(column, column, column, ...)] USING query
where database has the following syntax:
     username[/password]@connect_identifier
You should also check into replication services to see if that might be a consideration as well. See the Oracle Replication guide.

For More Information


Dig Deeper on Oracle database export, import and migration

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