:

DBMS_CAPTURE_ADM.BUILD Hangs

Having recently set-up Change Data Capture (CDC) on a development environment which in use, I was running a command to build a copy of the data dictionary into the redo log, as below:

set serveroutput on
variable f_scn number;
begin
:f_scn := 0;
dbms_capture_adm.build(:f_scn);
dbms_output.put_line('The first_scn value is ' || :f_scn);
end;
/

Looking at v$session_wait there was a wait event “TX – contention“. The time was increasing consistently only for this wait event.

I looked in v$lock for the session running the command and saw the lock modes requested. One LMODE of 4 and one of 6 which had a type of DB and TX, respectively.

Using the query below it showed which sessions where causing the locks and these could then be dealt with.

SELECT S.SID ORACLE_SID,
       S.PROCESS CLIENT_PID
FROM V$SESSION S, V$LOCK L1
WHERE L1.TYPE = 'TX'
AND L1.LMODE = 6
AND S.SID = L1.SID
AND L1.BLOCK = 1
AND NOT EXISTS (SELECT *
                FROM V$LOCK L2
                WHERE L2.TYPE = 'TX'
                AND L2.REQUEST > 0
                AND L1.SID = L2.SID);

I think ideally you would stop all connections to the DB when setting this up anyway because there may be some contention and this would have avoided the problem all together. Also, the next stage where you instantiate the tables is much better done when there are no sessions locking objects in the DB to reduce the chances of something going wrong.

Like it, share it...

Category: Change Data Capture


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *