Hi,
I'm required to refresh our production database from a warehouse. There are a number of tables to be refreshed.
Now, I cannot have materialized views that does incremental refreshes since the tables in the warehouse do not have mview logs on them. So I go for full table refreshes. So I create a temp tables, refresh that first, and then post it into the production tables. I use the temp tables instead of the production tables refreshing directly off the remote tables to reduce the refresh time of the production tables. Please find below the sample code.
begin
execute immediate 'create table table1_temp as select * from remote_tab@dblink';
execute immediate 'drop table prod_table1';
execute immediate 'create table prod_table1 as select * from table1_temp';
execute immediate 'drop table table1_temp';
Now, I've read this somewhere that temporary tables can fragment disk space and degrade DB performance over time.
Could you please suggest any alternative strategies for this task, and their pros and cons?
Thanks,
CJM