62 - [Oracle 19C] DB Link

 

• DATABASE LINK

 

• A database link is a schema object in one database that enables you to access objects

on another database.

• The other database need not be an Oracle Database system.

However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

• After you have created a database link, you can use it in SQL statements to refer to tables, views,

and PL/SQL objects in the other database by appending @dblinkname to the table, view,

or PL/SQL object name.

• To create a private database link, you must have the CREATE DATABASE LINK system privilege.

• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege

 

 

 

 

 

 

2023.11.27 - [Database/Oracle] - 47 - [Oracle 19C] To create PDB from seed PBD(DBCA)

47 - [Oracle 19C] To create PDB from seed PBD(DBCA)

 

 

위의 링크를 참고해서 PDB 디비를 생성한다.

Instance - orcl

Pluggable database name - prod1

Administrator user name - prod1_admin

default user tablespace - selected

 

 

SQL> select name, open_mode from v$pdbs;

NAME		     OPEN_MODE
-------------------- ----------
PDB$SEED	     READ ONLY
ORCLPDB 	     READ WRITE
PDB1		     MOUNTED
PDBTEST1	     MOUNTED
PROD1		     READ WRITE



SQL> alter session set container=prod1;

Session altered.



SQL> show con_name

CON_NAME
------------------------------
PROD1



SQL> show user
USER is "SYS"



 # prod1_admin 사용자에게 권한을 부여한다
SQL> grant dba to prod1_admin;

Grant succeeded.

 

 

 

 

 

 

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO
	 4 PDB1 			  MOUNTED
	 5 PDBTEST1			  MOUNTED
	 6 PROD1			  READ WRITE NO
	 7 PROD2			  READ WRITE NO



SQL> alter session set container=prod2;

Session altered.



SQL> show con_name

CON_NAME
------------------------------
PROD2



SQL> show user
USER is "SYS"



SQL> grant dba to prod2_admin;

Grant succeeded.

SQL>

 

 

 

# 새로운 PDB 들이 리스너에 추가된 것을 볼 수 있다.

Service "orcl.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbtest1.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "prod1.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "prod2.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@test ~]$

 

 

 

Add new Service Name for PROD1, PROD2

 

 

 

 

[oracle@test ~]$ sqlplus prod1_admin/Manager123#@prod1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 1 14:27:29 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PROD1




SQL> conn prod2_admin/Manager123#@prod2
Connected.
SQL> show con_name

CON_NAME
------------------------------
PROD2
SQL>

 

 

 

SQL> show user
USER is "PROD2_ADMIN"



SQL> create table employees (id number, name varchar2(100) );

Table created.



SQL> insert into employees values(1,'seoul');

1 row created.



SQL> commit;

Commit complete.



SQL> select * from employees;

	ID NAME
---------- --------------------
	 1 seoul

SQL>

 

 

 

SQL> conn prod1_admin/Manager123#@prod1
Connected.



SQL> show user
USER is "PROD1_ADMIN"



SQL> show con_name

CON_NAME
------------------------------
PROD1



SQL> create database link read_prod2
  2  connect to prod2_admin identified by Manager123#
  3  using 'prod2' ;

Database link created.

 

 

 

 

 

 

SQL> show user
USER is "PROD1_ADMIN"



SQL> select * from employees@read_prod2;

	ID NAME
---------- --------------------
	 1 seoul


SQL> insert into employees@read_prod2 values (2,'suwon');

1 row created.


SQL> select * from employees@read_prod2;

	ID NAME
---------- --------------------
	 2 suwon
	 1 seoul

SQL> commit;

Commit complete.