Need some help here.
I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by reporting team to generate reports. This is done to minimize the downtime for report generation. This way the reports are always accessing the latest data.
Target table B is a List partition table, partitioned on Client ID. ETL team loads data for each client in the respective partition, in Table A.
I tried doing it with Exchange partition: exchange partition mechanism to swap the segments of A and partitioned table B. But couldn’t do it as both tables are List partitioned and Oracle doesn’t like that. I created partitions so as to avoid creating multiple tables (TableA_ClientId) for each Client.
My other option: whenever data is loaded into target table A, rename the table B as table Temp, table B as table A and table A as table Temp.
Can you please suggest a better approach.
