Hi Guys,
We have a very large application (30Tb compressed) on Oracle 12.2 running on Linux. One of the tables is partitioned by business date and and sub-partitioned by location. There are 200 sub-partitions for one business dates.
Lately I have noticed when we truncate one sub-partition any process inserting or selecting from the table is stuck. New SQL's fired by process has SQL_ID but has no plan hash value.
Truncation of sub-partition should not lock the table but that's what I am seeing. There is no index on the table and we do not lock or use any hints like PARALLEL, APPEND or ENABLE_PARALLEL_DML or COMPRESSION on this table. As soon as I kill the process that is doing the truncate sub-partition all other process runs okay.
The Syntax used to truncate the sub-partition is
ALTER TABLE TABLE_NAME TRUNCATE SUBPARTITION PART_20221031_NYK reuse storage;
What actually is causing the locking/wait? Am I missing anything here?
additionalinfo.docx (114.91 KB)Please advise.
Thanks