Tibero DBLINK

login as: tibero
tibero@192.168.56.241's password:
Last login: Tue Apr 11 16:40:26 2023 from 192.168.56.1
[tibero@T1:/home/tibero]$
[tibero@T1:/home/tibero]$ cd /tibero
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ -- DATABASE LINK Tibero to Tibero
bash: --: command not found...
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ tbsql tibero/tmacx

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

TBR-17001: Login failed: invalid user name or password.

SQL> conn tibero/tmax
Connected to Tibero.

SQL> SELECT * FROM user_db_links;

0 row selected.

SQL> Q
Disconnected.
[tibero@T1:/tibero]$
[tibero@T1:/tibero]$ cat  $TB_HOME/client/config/tbdsn.tbr
#-------------------------------------------------
# /tibero/tibero7/client/config/tbdsn.tbr
# Network Configuration File.
# Generated by gen_tip.sh at Thu Apr  6 14:32:38 KST 2023
tibero=(
    (INSTANCE=(HOST=localhost)
              (PORT=8629)
              (DB_NAME=tibero)
    )
)

tibero2=(
    (INSTANCE=(HOST=localhost)
              (PORT=8629)
              (DB_NAME=tibero)
    )
)

[tibero@T1:/tibero]$ tbsql tibero/tmax@tibero2

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero using tibero2.

SQL> q
Disconnected.
[tibero@T1:/tibero]$ tbsql tibero/tmax

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> CREATE DATABASE LINK tlink CONNECT TO tibero IDENTIFIED BY 'tmax'  USING 'tibero2';

Database Link 'TLINK' created.

SQL>  DROP DATABASE LINK tlink;

Database Link 'TLINK' dropped.

SQL> CREATE DATABASE LINK tlink CONNECT TO tibero IDENTIFIED BY 'tmax' USING 'tibero22';

Database Link 'TLINK' created.

SQL> DROP DATABASE LINK tlink;

Database Link 'TLINK' dropped.

SQL> CREATE DATABASE LINK tlink CONNECT TO tibero IDENTIFIED BY 'tmax'  USING 'tibero2';

Database Link 'TLINK' created.

SQL> CREATE DATABASE LINK tlink_error CONNECT TO tibero IDENTIFIED BY 'tmax'  USING 'tibero22';

Database Link 'TLINK_ERROR' created.

SQL> DESC USER_DB_LINKS

COLUMN_NAME                              TYPE               CONSTRAINT 
---------------------------------------- ------------------ --------------------
OWNER                                    VARCHAR(128)
DB_LINK                                  VARCHAR(128)
USERNAME                                 VARCHAR(128)
HOST                                     VARCHAR(128)
CREATED                                  DATE

SQL>  COL OWNER FOR A10
SQL> COL DB_LINK FOR A10
SQL> COL USERNAME FOR A10
SQL> COL HOST FOR A10
SQL> COL CREATED FOR A10
SQL>
SQL> SELECT * FROM USER_DB_LINKS;

OWNER      DB_LINK    USERNAME   HOST       CREATED
---------- ---------- ---------- ---------- ----------
TIBERO     TLINK      TIBERO     tibero2    2023/04/13
TIBERO     TLINK_ERRO TIBERO     tibero22   2023/04/13
           R


2 rows selected.

SQL> COL DB_LINK FOR A12
SQL> SELECT * FROM USER_DB_LINKS;

OWNER      DB_LINK      USERNAME   HOST       CREATED
---------- ------------ ---------- ---------- ----------
TIBERO     TLINK        TIBERO     tibero2    2023/04/13
TIBERO     TLINK_ERROR  TIBERO     tibero22   2023/04/13

2 rows selected.

SQL> SELECT DB_NAME FROM V$INSTANCE;

DB_NAME
----------------------------------------
tibero

1 row selected.

SQL> SELECT DB_NAME FROM V$INSTANCE@TLINK;

DB_NAME
----------------------------------------
tibero

1 row selected.

SQL> LS TABLE

NAME                               SUBNAME                  TYPE       
---------------------------------- ------------------------ --------------------
DEPT                                                        TABLE
EMP                                                         TABLE
PRODUCT                                                     TABLE

SQL>
SQL>
SQL> CREATE TABLE EMP_LOCAL AS
   2 SELECT * FROM EMP@TLINK
   3 WHERE ROWNUM < 1;
 
Table 'EMP_LOCAL' created.

SQL>

CREATE TABLE EMP_LOCAL AS SELECT * FROM EMP;
일반 로컬 안에서 사용한다.

CREATE TABLE EMP_LOCAL AS SELECT * FROM EMP@TLINK;
일반적으로 데이터가 많다고 하면 객체를 먼저 가져온다.


CREATE TABLE EMP_LOCAL AS SELECT * FROM EMP@TLINK WHERE ROWNUM < 1;
1보다 작은 ROWNUM은 존재하지 않기 때문에 스키마 객체만 가져온다.

SQL> LS TABLE

NAME                               SUBNAME                  TYPE       
---------------------------------- ------------------------ --------------------
DEPT                                                        TABLE
EMP                                                         TABLE
EMP_LOCAL                                                   TABLE
PRODUCT                                                     TABLE

SQL> SELECT * FROM EMP_LOCAL
   2 ;

0 row selected.

SQL> INSERT INTO EMP_LOCAL
   2 SELECT * FROM EMP@TLINK;
   -> 일반적으로는 데이터를 잘라서 넣는다.
14 rows inserted.

SQL> COMMIT;

Commit completed.

SQL> SELECT COUNT(*) FROM EMP_LOCAL;

  COUNT(*)
----------
        14

1 row selected.

SQL>