我试图理解出现以下错误的原因。
`ORA-04021: timeout occurred while waiting to lock object`
运行命令时过程引发此错误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);
该代码调用上述过程两次，第一次尝试成功。它可以很好地截断子分区。在第二次尝试中它失败了......下面给出了 execStmt 函数，错误是从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;
由于这种情况主要发生在周末，因此我没有机会检查锁定表以查看锁定对象的内容。但我确信这是一个发生了大量插入的表。 所以我的问题是表上的插入操作可以防止上述 DDL 吗？
从oracle文档中，我看到插入获取了aSX锁，解释如下，
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.
发生此错误是因为您尝试截断的分区当时正在使用中。正如您所提到的，这些插入语句正在运行，它会影响 DDL 操作。