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

Optimal way to transfer data between two tables in two separate schemas

I found these ways to transfer data between two tables in two separate schemas:

  1. INSERT INTO dest_table SELECT * from source_table
  2. First DROP TABLE dest_table and second CREATE TABLE dest_table as SELECT * from source_table
  3. Using COPY command in SQL*PLUS (But in one case I copy data successfully and commit it, but I could not see my records in the destination table! Any suggestions?)
  4. Using imp and exp (This way the problem is that I define my destination table in a specific table_space named TS1. But doing it this way, the destination table uses table_space named TS2 from the source tables. Should I use ALTER TABLE .... TABLE SPACE... for all my destination tables? Is there any better way?)
  5. Using EM->Change Manager->DB propagate

Now, my question is, which one of these ways is better in terms of performance and speed?

In this case I think the simplest is the best. Go with number 1 --it accomplishes what you want with the least overhead.

Dig Deeper on Oracle database performance problems and tuning

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.