Could someone please explain how DDL_LOCK_TIMEOUT feature actually works?
Let's say a database has table A. Every second the application creates a new transaction against this table and these transactions last for 100 seconds, so at each point in time there are 100 transactions. Let's say I execute alter session set ddl_lock_timeout = 30 followed by truncate. I think it will fail as 30 is less than 100, is it going to impact the transactions in any way? Then I'll try again but use longer timeout = 200 seconds, what will happen?
Is Oracle going to stop new transactions from starting?
The reason for asking is this: we attempted to truncate materialized view log while the application was running. Naturally it failed with ORA-00054. So we tried again after setting ddl_lock_timeout to 30 sec, then 1 minute, and finally 2 minutes, all failed with ORA-00054. This caused TM locks on the parent table of this MV log. The log isn't very big, 22 GB, 730 extents, in a locally managed tablespace. Version 11.2 on Linux.
I did a simple test:
- session#1: created a table, inserted a row, did't commit
- session#2: alter session set ddl_lock_timeout = 100 followed by truncate. It was waiting on TM lock
- session#3: trying to insert a row: it waited on TM lock for 100 seconds and then completed.