Table Data Delete with out putting write lock
Hi Friends,
We are in the process of archiving old data for which now we want to clear the old data by deletes. We are using delete rather than partition because we are on 10g and most of the tables are a form of reference partition,since it is available only in 11g we are going for physical deletes.
The problem we have here is ,One of the main transaction table while trying to delete is putting row share and row exclusive locks. This prevents some other program in doing updates to this table. My application tries to insert/update data in this period. Is there a way to allow deletes to happen and still prevents table lock.All the keys used in this table for delete are indexed. As I am deleting only past data,it should not affect the new data. Please advice
SSN