Hi Friends,
I want to make my question easily understandable by indicating below short forms
Our application - A
Another application by others - B
Oracle Database table - C
The scenario is :
Application A wants to purge the old data in table C every day based on some condition. Application B has its own process that adds data into table C (24/7).They can't stop their process even for a second.
When Application A starts purging process we don't want Application B has any conflict while it is adding data or updating existing rows as we don't want to loss new data.
Note : we may need to delete 15 million rows whenever purge process happens and we want to make sure not to loose any new data inserted or updated by Application B
1 st use case : When Application B adds new data :
What could be the best approach in this scenario in the below options.
- If we use truncate table , will it lock the table that interrupts Application B process?
When A purges the data in the table and if it does not have any lock , Application B does not have any issue for adding the data. Will it be ok to follow this truncate table process?
I read in one of the answers in the forum, table will not be locked or blocked When application B performs adding data into table while deleting other rows. Only to be deleted rows will be blocked that is irrelevant for application B as it adds new data in this case.
Can you guys confirm on this?
2 nd use case ( I need to confirm this by Application B whether they are doing any updates apart from inserting new data in table C - but I am giving this use case if they confirm about this) :
Applciation B updates the some rows that can be deleted by Application A purge process at the same time.
What shall we do about the rows that are being updated by Application B in this case
I want to handle both use cases ( need to confrim about 2 nd use case):
Can somebody help me to have best approach?
I am from Java and Oracle background and I need to know what happens to table in both cases