I have a set of partitioned tables in database. The partitions of a single table are spread across tablespaces. Also 1 tablespace is holding partitions of multiple tables. The structure is somehow like this-

TABLESPACE_NAME	SEGMENT_NAME	PARTITION_NAME
PDB_TB_DS1	TB_PR_DAILY_ROUND_AMOUNT_AML	TB_353822332_142
PDB_TB_DS1	TB_PR_DAILY_SUMMARY_AML	TB_1227160661_142
PDB_TB_DS1	TB_PR_DAILY_ACTION_SUMMARY_AML TB_1572829648_142
PDB_TB_DS2	TB_PR_DAILY_ACTION_SUMMARY_AML TB_1572829648_143
PDB_TB_DS2	TB_PR_DAILY_SUMMARY_AML	TB_1227160661_143
PDB_TB_DS2	TB_PR_DAILY_ROUND_AMOUNT_AML	TB_353822332_143
PDB_TB_DS3	TB_PR_DAILY_ACTION_SUMMARY_AML TB_1572829648_136
PDB_TB_DS3	TB_PR_DAILY_SUMMARY_AML	TB_1227160661_136
PDB_TB_DS3	TB_PR_DAILY_ROUND_AMOUNT_AML	TB_353822332_136

My Program design
As per the program delivered to us, the EXCHANG PARTITION concept is used.

The principle of scripts is as following-
1) Create the temporary table structure as of main table in the tablespace where partition is existing.
2) Insert into temporary table the entire data of 1 partition.
3) Then Exchange the partition with temporary table.

Problem
As per the program design all the tables scripts run in parallel. This means the copies of partitions of different tables are created at the same time. This means there should ne enough space in tablespace to hold all the partitions of tablespace. The space requirement is ‘Combined size of all partitions of particular tablespace’.

This means if sum of partitions (TB_353822332_142 , TB_1227160661_142 and TB_1572829648_142) of tablespace PDB_TB_DS1 is 30 GB I need 30 GB more space in the same tablespace added.


Help required
Is my analysis of space requirement correct? Am I right in saying that DBS need to increase the space in each tablespace as ‘sum of all partitions of that tablespace’.


This leads to space requirement of around 900 GB in our application. Can’t the DBAs add the required datafiles for duration of our program and then remove the added datafiles. Are there any chances of data loss by having this approach? Please keep in mind that our program execution is one off task.

I would appreciate if somebody can have a look and put some light please

nobody here owes you any response.

>Can’t the DBAs add the required datafiles for duration of our program and then remove the added datafiles.
Nothing is impossible for the person who does not have to actually do it.