SQL & PL/SQL

Hi There,

I'm working on Oracle 19c. I have the following process in each stored procedure. All DML statements are using EXECUTE IMMEDIATE. There is no Cursors or Transactions in the code. It's merely performing the below in a sequence.

Note: We have a million of records in scope for below DMLs.

1. DROP INDEXS of the TABLE
2. DELETE FROM TABLE
3. INSERT INTO TABLE
4. MERGE or UPDATE the TABLE
5. COMMIT
6. CREATE INDEXES of the TABLE

The Stored Procedure is been called via Client App. The table is getting locked mostly during the MERGE or UPDATE. Can you please let me know

1. The root cause for the table lock
2. How can we avoid the table lock

Regards,

RV