DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

The dml_lock_timeout Parameter in the DBMS_REDEFINITION Package

#oracle #devops #linux #database

As you know, during the execution of the FINISH_REDEF_TABLE procedure, the table must be locked exclusively for a short period so that the procedure can complete successfully. Otherwise, the procedure will wait until other transactions running on the table are finished, and it will remain in a waiting state:

SQL> EXEC DBMS_REDEFINITION.can_redef_table('VAHID', 'MYTBL');

PL/SQL procedure successfully completed

SQL> EXEC DBMS_REDEFINITION.start_redef_table('VAHID', 'MYTBL', 'MYTBL_TEMP');

PL/SQL procedure successfully completed

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('VAHID', 'MYTBL', 'MYTBL_TEMP');

PL/SQL procedure successfully completed
Enter fullscreen mode Exit fullscreen mode

Session 1:

SQL> delete MYTBL where rownum<=1;

1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 2:

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’);

Executing…
Enter fullscreen mode Exit fullscreen mode

As shown above, the finish_redef_table command enters a waiting state until Session 1 completes its work.

Starting with Oracle 12c, you can apply a time limit to this waiting period. This is done using the dml_lock_timeout parameter within the finish_redef_table procedure.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’,dml_lock_timeout => 10);

ORA-42012: error occurred while completing the redefinition

ORA-42042: time out in acquiring DML lock during online redefinition

Elapsed: 00:00:11.11
Enter fullscreen mode Exit fullscreen mode

Top comments (0)