I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it
- First I rename the original tables in schema B
- Then, using SQL Developer I copy the table to copy from schema A to B. (I right click the table to copy and select Copy and choose the destination schema)
- Then I create all the indices that exist in the original tables.(Copying tables does not copy indexes, so you have to create them manually)
After the process is over, I run queries against all the tables. Everything goes fine with 9 of them, but with one of the tables the executed query hangs. The next day, when we run the same query against the same table, the execution takes only a moment. I have no idea what the problem is. I tried not adding indexes but it didn't help. I know he way we copy table content might seem weird.