enq: JI - contention wait event solution

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

How to use existed tns connection for new one?

* The key point is 'Using' keyword that checks tnsnames.ora connection node then you can reach remote database.

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK2"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK3"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

How to define new db link?

Add a new connection node into tnsnames.ora file.
vi $ORACLE_HOME/network/admin/tnsnames.ora
e.g.

ALPER_DB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))

Check remote host via tnsping

$ tnsping ALPER_DB

Define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

If you desire all users use this link you should add "public" into create statement

For All users (public):

SQL> CREATE PUBLIC DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

Alternative option: define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))';

Observe the new link and check it

For specific users:

SQL> select * from user_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

For All users (public):

SQL> select * from all_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

Extra
Connect without db link:
$ sqlplus "hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))"
or
CMD> sqlplus hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))

How to check database link?

SQL> SELECT 1 FROM dual@mydblink