I am trying to understand the reason why i get the below error.
`ORA-04021: timeout occurred while waiting to lock object`
This error is thrown from a procedure while running the command alter table <<T_NAME>> truncate subpartition <<SUBPARTITION_NAME>>.
v_dyncursor_stmt := 'with obj as (select /*+ materialize */ data_object_id, subobject_name from user_objects where object_name = UPPER(''' ||
p_table_name ||
''') and object_type = ''TABLE SUBPARTITION'') select '||p_hint||' distinct subobject_name from ' ||
p_table_name || ' t, obj where data_object_id = DBMS_MView.PMarker(t.rowid) and ' || p_where;
/* log */
log_text(v_unit_name, 'INFO', 'Open cursor', v_dyncursor_stmt);
/* loop over partitions which needs to be truncated */
v_counter := 0;
open c_subpartitions for v_dyncursor_stmt;
loop
FETCH c_subpartitions
INTO v_subpartition_name;
EXIT WHEN c_subpartitions%NOTFOUND;
v_statement := 'alter table ' || p_table_name || ' truncate subpartition "' || v_subpartition_name || '"';
execStmt(v_statement);
the code is calling above procedure twice and the first attempt is successful. it truncates the subpartition fine. In the second attempt it is failing... The execStmt function is given below, the error is thrown from EXCEUTE IMMEDITE line...
procedure execStmt(p_statement IN VARCHAR2) IS
v_unit_name varchar2(1024) := 'execStmt';
v_simulate varchar2(256);
begin
v_simulate := utilities.get_parameter('PART_PURGE_SIMULATE', '0');
if (v_simulate = '1') then
log_text(v_unit_name, 'INFO', 'Statement skipped. (PART_PURGE_SIMULATE=1)',
p_statement);
else
/* log */
log_text(v_unit_name, 'INFO', 'Executing statement', p_statement);
EXECUTE IMMEDIATE p_statement;
end if;
end;
As this happens mostly over the weekend, i do not get a chance to inspect the lock tables to see what has locked the object. but i know for sure that it is a table which has alot of inserts happening. So my question is can an insert operation on a table prevent the above DDL ??
from oracle docs,i see that an insert aquires a SX lock which is explained as below,
A row exclusive lock (RX), also called a subexclusive table lock (SX), indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and SS locks for the same table.
