For faster access, it was caching temporary table into the keep cache before exchanging partition with the main table but at some place code was getting stuck and we were struggling to find the sql text which was blocked and every time code was getting stuck there, it was completely not moving at all.
We were killing and restarting the whole process again and again but every time code was getting stuck at the same place and it was displaying the sql_id but it was not displaying the sql_fulltext.
We tried many views like v$sql, v$session, v$sqlarea, dba_hist_sqltext but no luck.
It was displaying wait event “enq: TX – contention” but not showing any sql_text.
SID Serial# A W Sec in Wait Event SQL SQL_ID
——- ——- —————— – – ———- ————————- ————————- ————-
1637 28617 Y Y 159205 enq: TX – contention – Not Available – 5dxybryysj4g7
Finally after struggling through the codes, I tried to fetch all the sessions and sql text which was active. From the active session output, we observed that one of the insert statement was using hint and due to which it was blocking the sessions.
We fetch the sql_fulltext from the v$open_cursor which earlier it was not showing from the v$session. This sql_id was putting temporary table into the keep buffer cache.
Finally we killed the session which was using hint for the insert statement and after that everything was moving fine.
SQL> select * from v$open_cursor where sql_id like ‘5dxybryysj4g7’;
alter TABLE XXXX storage (buffer_pool keep);