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

Different ways of transferring data between tables in different schemas

I found these ways to transfer data between two tables in two seperate 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 the COPY command in SQL*PLUS (but in one case I copied data with a successful message and committed it, but I could not see my records in the destination table! Any suggestions?)

4. Using imp and exp (This way's problem is: I define my destination table in a specific table_space named TS1, but destination table uses source tables table_space named TS2. Should I use ALTER TABLE .... TABLE SPACE...for all my destination tables? Any better way?)

5. Using EM->Change Manager->DB propogate

Now, my question is: Which one of these ways is better in the case of performance and speed?

Method #3 - I have rarely used this command so I hesitate to make any suggestions.

Method #4 - I am not sure I understand the question. Are you saying that if you export form a db with TS1 as the source tablespace and then import into a second database your tables end up in TS2? If your import user doesn't have quota on TS1 in the target then the objects he imports will go into the default tablespace for the user I assume this is TS2.

Final question - Without testing I couldn't say, but I always opt for the most simple method that meets my requirements. I would test doing a truncate and insert and the CTAS to see which is faster. In some cases I have seen the CTAS work faster.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.