CODEWITHSUNDEEP
oracle-databaseplsql
user3224907
user3224907

Reputation: 768

ORA - 04020: deadlock detected while trying to lock object

Getting an ORA-04020: deadlock detected while trying to lock object and I believe the source of the error could be these statements:

  v_sql := 'DELETE FROM ' || in_table_name || ' SUBPARTITION (' || v_subpart_name || ')';
  EXECUTE IMMEDIATE v_sql;

  v_sql := 'ALTER TABLE ' || in_table_name || ' TRUNCATE SUBPARTITION ' || v_subpart_name;
  EXECUTE IMMEDIATE v_sql;

Any ideas on how to resolve this issue? Could it be the ALTER statement is throwing the error since the DELETE is right before it? Not sure, I thought the ALTER would execute only once the DELETE if finished. Or could it be the procedure doesn't wait for the ALTER to complete before exiting and re-executing?

Upvotes: 1

Views: 7787

Answers (1)

Ramki
Ramki

Reputation: 463

if your already doing TRUNCATE SUBPARTITION then why you need to delete . TRUNCATE would be much efficient way to delete data from table.

other wise you have to do commit after delete , then only truncate is allowed.

Regards Ramki

Upvotes: 1

Related Questions