[oracle] DB LINK에 있는 SYS_HUB DROP and CREATE 방법

 

 

oracle 12.2 이후 부터 DB LINK에 기본적으로 생성되어 있는 SYS_HUB는

read only 인스턴스에서 DML을 read write 가능한 인스턴스로 라우팅해주는 용도이다.

read only / read write 를 별도 구성하여 사용하지 않는 일반적인 환경에서는 불필요한 기능이며 삭제 하여도 영향도가 없음

 

• 공식 문서

What Is The Use Of DataBase Link SYS.SYS_HUB And Can It Be Deleted (Doc ID 2413797.1)

https://support.oracle.com/epmos/faces/DocContentDisplay?id=2413797.1 

 

How to Drop and Create SYS_HUB (Database Link) ? (Doc ID 2442938.1)

https://support.oracle.com/epmos/faces/DocContentDisplay?id=2442938.1

 

 

SYS_HUB DROP 방법

Read Write 인스턴스인지 확인한다.

SQL> select instance_mode from v$instance;

INSTANCE_MO
-----------
REGULAR

SQL> show parameter instance_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_mode                        string      READ-WRITE
SQL>

 

해당 링크를 사용하는 오브젝트가 있는지 확인

SQL> SELECT o.owner , o.object_name , o.object_type
, o.status , o.created , o.last_ddl_time
FROM dba_objects o
, dba_dependencies d
WHERE o.owner = d.owner
AND o.object_name = d.name
AND o.object_type = d.type
AND d.referenced_owner = 'SYS'
AND d.referenced_name = 'SYS_HUB'
AND d.referenced_type = 'DATABASE LINK'
/

no rows selected

SQL>

 

SYS(SYSDBA)로 로그인 후 DROP 진행

> SELECT * FROM dba_db_links 

OWNER|DB_LINK|USERNAME|HOST    |CREATED                |HIDDEN|SHARD_INTERNAL|VALID|INTRA_CDB|
-----+-------+--------+--------+-----------------------+------+--------------+-----+---------+
SYS  |SYS_HUB|        |SEEDDATA|2019-04-17 01:12:06.000|NO    |NO            |YES  |NO       |

1 row(s) fetched.

-- 삭제
> DROP DATABASE LINK SYS_HUB 

0 row(s) modified.


> SELECT * FROM dba_db_links 

OWNER|DB_LINK|USERNAME|HOST|CREATED|HIDDEN|SHARD_INTERNAL|VALID|INTRA_CDB|
-----+-------+--------+----+-------+------+--------------+-----+---------+

0 row(s) fetched.

 

SYS_HUB CREATE 방법

DBMS_PQ_INTERNAL 패키지가 있는지 확인

> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED, LAST_DDL_TIME 
FROM DBA_OBJECTS 
WHERE OBJECT_NAME = 'DBMS_PQ_INTERNAL' AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')


OWNER|OBJECT_NAME     |OBJECT_TYPE |STATUS|CREATED                |LAST_DDL_TIME          |
-----+----------------+------------+------+-----------------------+-----------------------+
SYS  |DBMS_PQ_INTERNAL|PACKAGE     |VALID |2019-04-17 01:12:06.000|2019-04-17 01:12:06.000|
SYS  |DBMS_PQ_INTERNAL|PACKAGE BODY|VALID |2019-04-17 01:12:06.000|2019-04-17 01:12:06.000|

2 row(s) fetched.

 

패키지 실행

SQL> execute dbms_pq_internal.create_db_link_for_hub ;

PL/SQL procedure successfully completed.

-- 생성 확인
SQL> SELECT * FROM dba_db_links 

OWNER|DB_LINK|USERNAME|HOST |CREATED                |HIDDEN|SHARD_INTERNAL|VALID|INTRA_CDB|
-----+-------+--------+-----+-----------------------+------+--------------+-----+---------+
SYS  |SYS_HUB|        |ENT19|2024-09-15 08:21:32.000|NO    |NO            |YES  |NO       |

1 row(s) fetched.