Disini digunakan 2 database Oracle dengan nama Ora1 ( Remote ) dan Ora2 ( Diremote )
Berikut adalah cara membuat link database Oracle to Oracle :
Format :
-- private
CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';
-- dipublic
CREATE PUBLIC DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';
-- tambahkan di TNSNAMES.ora
CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name))
)';
buka dan login sqlplus atau Toad atau SQL Developer, masuk ke database Ora1 , lalu tuliskan perintah dibawah :
Contoh :
1. Tambahkan script dibawah pada file tnsnames.ora yang ada di directory /NETWORK/ADMIN/ dibawah ORACLE_HOME
PENJUALAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.100.13)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = POS)
)
)
2. Buat Link database private
2.1. privateCREATE DATABASE LINK PenjualanCONNECT TO userlinkora2 IDENTIFIED BY Abcd1234USING 'PENJUALAN';2.2. publicCREATE PUBLIC DATABASE LINK PenjualanCONNECT TO userlinkora2 IDENTIFIED BY Abcd1234USING 'PENJUALAN';
3. Menjalankan Query
3.1. Select
SELECT * FROM customers@Penjualan;
3.2. Insert
INSERT INTO customers@Penjualan(customer_id, name, email)
VALUES(2,'XYZ Inc','contact@xyzinc.com');
lalu select untuk melihat hasil insert
SELECT * FROM customers@Penjualan
Baca juga artikel yang berkaitan :
- Linked Table Database SQL Server ke Microsoft Access
- Link Database My SQL to SQL server
- Link Database SQL Server to SQL Server
- Link Microsoft Excel to SQL Server
- Querying Excel pada linked Database Server
- Migrasi Database Access to MySQL
- Link Database Oracle to SQL Server
- Link Database Access ( Link Table )