Action
Synchronizing data with remote database(NEW_XE)
SQL>
BEGIN DBMS_SNAPSHOT.REFRESH(LIST => 'HR.SS_EMPLOYEES_NEW',METHOD => 'F' ); END;
Problem
enq: JI - contention wait event occurs
Determine the problem
SQL>
SELECT eq_name "Enqueue",
ev.name "Enqueue Type",
eq.req_description "Description"
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event# = ev.event# AND ev.name = 'enq: JI - contention'
ORDER BY ev.name;
Description -> Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Solution I
Kill problematic session
SQL>
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| SID
|| ','
|| SERIAL#
|| ''' IMMEDIATE;'
AS KILL_THEM_ALL
FROM V$SESSION
WHERE TYPE <> 'BACKGROUND'
AND STATUS = 'ACTIVE'
AND EVENT = 'enq: JI - contention';
Solution II
Check database link
SQL>
SELECT * FROM V$INSTANCE@NEW_XE
