OSB: truncating the reporting tables in a PROD environment

One of our services started flooding the reporting db. Deleting records proved to be too slow compared to the production rate, and it would generate too much REDO logs on the Oracle DB.
I had to do this:

- purge the dist_wli.reporting.jmsprovider_error.queue_auto queue to start from a clean position to make sure no reporting messages error out
- stop the deployment JMS Reporting Provider
- disable the FK_WLI_QS_REPORT_DATA constraint
- truncate table WLI_QS_REPORT_DATA;
- truncate table WLI_QS_REPORT_ATTRIBUTE;
- enable the FK_WLI_QS_REPORT_DATA constraint
- restart the deployment JMS Reporting Provider


If you don't suspend JMS Reporting Provider, you get this error while truncating the tables:

SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 00054. 00000 - "resource busy and acquire with NOWAIT specified"


The DML is here:

ALTER TABLE WLI_QS_REPORT_DATA MODIFY CONSTRAINT FK_WLI_QS_REPORT_DATA DISABLE;

truncate table WLI_QS_REPORT_DATA;

truncate table WLI_QS_REPORT_ATTRIBUTE;

ALTER TABLE WLI_QS_REPORT_DATA MODIFY CONSTRAINT FK_WLI_QS_REPORT_DATA enable;


When trying to disable the FK I got several times:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

but finally it worked.
I tried to detect the SQL holding the lok using

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,

S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT

FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,

V$PROCESS P, V$SQL SQ

WHERE L.OBJECT_ID = O.OBJECT_ID

AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR

AND S.SQL_ADDRESS = SQ.ADDRESS;

 
but it could not find it.