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
Related Q&A from Mike Ault
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists. Continue Reading
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to... Continue Reading
I'm having a problem with dynamic SQL, specifically getting my incoming parameter into the SQL statement. I am using Oracle 9i and this procedure ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.