この記事は約170分59秒で読むことができます。

docker内でdblinkしてみた!

目次

docker images一覧

今回は18cr3同士でdblinkする。


[oracle@centos7 ~]$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     18.3.0-ee           1aa141ccee74        9 hours ago         8.39GB
oraclelinux         7-slim              c3d869388183        3 days ago          117MB

独自ネットワーク作成

次のネットワークを定義する。subnet=192.168.200.0/24


[oracle@centos7 ~]$ docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
cb877dc172c0        bridge              bridge              local
7786448a4929        host                host                local
d4bf04d92948        none                null                local
[oracle@centos7 ~]$ docker network create --subnet=192.168.200.0/24 user_defined_nw
20b6968054a5f8d8c14a247e6a665feba8847c4e1d788e020a6a283b92dbcc28
[oracle@centos7 ~]$ docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
cb877dc172c0        bridge              bridge              local
7786448a4929        host                host                local
d4bf04d92948        none                null                local
20b6968054a5        user_defined_nw     bridge              local
[oracle@centos7 ~]$

コンテナ起動

コンテナ外部から接続する際のポート割り当てはかぶらないように振る。コンテナの待ちうけポートはすべて同じ。—net=user_defined_nwがミソ。

name port ORACLE_PWD ORACLE_SID ORACLE_PDB
orcl_18cr3_1 11521 ORACLE_PWD ORCL pdb1
orcl_18cr3_2 21521 ORACLE_PWD ORCL pdb1
docker run -v /home/oracle/doclan:/mnt -v /home/oracle/doclan/18cr3/init:/docker-entrypoint-initdb.d/startup -d --name orcl_18cr3_1 --net=user_defined_nw --shm-size=4g -p 11521:1521 -p 15500:5500 -e TZ=Asia/Tokyo -e ORACLE_PWD=ORACLE_PWD -e ORACLE_SID=ORCL -e ORACLE_PDB=pdb1 oracle/database:18.3.0-ee
docker run -v /home/oracle/doclan:/mnt -v /home/oracle/doclan/18cr3/init:/docker-entrypoint-initdb.d/startup -d --name orcl_18cr3_2 --net=user_defined_nw --shm-size=4g -p 21521:1521 -p 25500:5500 -e TZ=Asia/Tokyo -e ORACLE_PWD=ORACLE_PWD -e ORACLE_SID=ORCL -e ORACLE_PDB=pdb1 oracle/database:18.3.0-ee

コンテナの起動確認

orcl_18cr3_1


[oracle@centos7 ~]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                       PORTS                                              NAMES
46ba47fe0739        oracle/database:18.3.0-ee   "/bin/sh -c 'exec $O…"   About an hour ago   Up About an hour (healthy)   0.0.0.0:21521->1521/tcp, 0.0.0.0:25500->5500/tcp   orcl_18cr3_2
6898242bcdd5        oracle/database:18.3.0-ee   "/bin/sh -c 'exec $O…"   2 hours ago         Up 2 hours (healthy)         0.0.0.0:11521->1521/tcp, 0.0.0.0:15500->5500/tcp   orcl_18cr3_1
[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 10:17:07 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 10:21:43 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

orcl_18cr3_2


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_2 bash
[oracle@46ba47fe0739 ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 10:22:46 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@46ba47fe0739 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 10:23:24 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PDB1

割り当てられたipの確認

デフォルトゲートウェイは192.168.200.1にわりあてられている模様。


[oracle@centos7 ~]$ docker network inspect $(docker network ls -q) | grep -E "Subnet|Name|IPv4"
        "Name": "bridge",
                    "Subnet": "172.17.0.0/16",
        "Name": "host",
        "Name": "none",
        "Name": "user_defined_nw",
                    "Subnet": "192.168.200.0/24"
                "Name": "orcl_18cr3_2",
                "IPv4Address": "192.168.200.3/24",
                "Name": "orcl_18cr3_1",
                "IPv4Address": "192.168.200.2/24",

コンテナ内の環境整備

コンテナのorcl_18cr3_1とorcl_18cr3_2のそれぞれに適用する。記載しているのはorcl_18cr3_1のみ。

sudo package install


[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_18cr3_1 bash
bash-4.2# yum install -y sudo

vim install


bash-4.2# yum -y install vim

passwdコマンド install


bash-4.2# whereis passwd
passwd: /etc/passwd
bash-4.2# yum install -y passwd
bash-4.2# whereis passwd
passwd: /usr/bin/passwd /etc/passwd

passwordの設定


bash-4.2# whoami
root
bash-4.2# passwd
Changing password for user root.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
bash-4.2# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
bash-4.2#

oracleユーザーがsudoコマンド使えるようにする


bash-4.2# visudo
## Same thing without a password
# %wheel        ALL=(ALL)       NOPASSWD: ALL
oracle          ALL=(ALL)       ALL
bash-4.2# exit
[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ whoami
oracle
[oracle@6898242bcdd5 ~]$ sudo echo "hey"

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for oracle:
hey
[oracle@6898242bcdd5 ~]$

epel repo install


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sudo curl -O https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-11.noarch.rpm
[oracle@6898242bcdd5 ~]$ ll
total 16
-rw-r--r--. 1 root root 15080 Jan 19 20:23 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root root    26 Jan  2 21:05 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@6898242bcdd5 ~]$ sudo rpm -ivh epel-release-7-11.noarch.rpm

rlwrap install sqlplus


[oracle@6898242bcdd5 ~]$ sudo yum -y install rlwrap
[oracle@6898242bcdd5 ~]$ which rlwrap
/usr/bin/rlwrap
[oracle@6898242bcdd5 ~]$ rlwrap
Usage: rlwrap [options] command ...

Options:
  -a[password prompt]        --always-readline[=password prompt]
  -A                         --ansi-colour-aware
  -b                  --break-chars=
  -c                         --complete-filenames
  -C  <name|n>               --command-name=<name|n>
  -D  <0|1|2>                --history-no-dupes=<0|1|2>
  -e  <char|''>              --extra-char-after-completion=<char|''>
  -f        --file=
  -g                 --forget-matching=
  -h                         --help
  -H                   --history-filename=
  -i                         --case-insensitive
  -I                         --pass-sigint-as-sigterm
  -l                   --logfile=
  -m[newline substitute]     --multi-line[=newline substitute]
  -M  <.ext>                 --multi-line-ext=<.ext>
  -n                         --no-warnings
  -N                         --no-children
  -o                         --one-shot
  -O                 --only-cook=
  -p[colour]                 --prompt-colour[=colour]
  -P                  --pre-given=
  -q                  --quote-characters=
  -r                         --remember
  -R                         --renice
  -s                      --histsize= (negative: readonly)
  -S                 --substitute-prompt=
  -t                   --set-term-name=
  -U                         --mirror-arguments
  -v                         --version
  -w                      --wait-before-prompt= (msec, <0  : patient mode)
  -W                         --polling
  -z         --filter= ('rlwrap -z listing' writes a list of installed filters)

bug reports, suggestions, updates:
https://github.com/hanslub42/rlwrap
[oracle@6898242bcdd5 ~]$
[oracle@6898242bcdd5 ~]$ rlwrap -pRed sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 19 20:27:14 2019

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

SQL>

rlwrap dictionary install sqlplus


[oracle@6898242bcdd5 ~]$ ll
total 16
-rw-r--r--. 1 root root 15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root root    26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@6898242bcdd5 ~]$ mkdir rlwrap-extensions
[oracle@6898242bcdd5 ~]$ ll
total 16
-rw-r--r--. 1 root   root     15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
drwxr-xr-x. 2 oracle oinstall     6 Jan 20 10:57 rlwrap-extensions
lrwxrwxrwx. 1 root   root        26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@6898242bcdd5 ~]$ cd rlwrap-extensions
[oracle@6898242bcdd5 rlwrap-extensions]$ pwd
/home/oracle/rlwrap-extensions
[oracle@6898242bcdd5 rlwrap-extensions]$ sudo curl -O http://www.linuxification.at/download/rlwrap-extensions-V12-0.05.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 57847  100 57847    0     0  16479      0  0:00:03  0:00:03 --:--:-- 16480
[oracle@6898242bcdd5 rlwrap-extensions]$ ll
total 60
-rw-r--r--. 1 root root 57847 Jan 20 10:57 rlwrap-extensions-V12-0.05.tar.gz
[oracle@6898242bcdd5 rlwrap-extensions]$ tar xvfz rlwrap-extensions-V12-0.05.tar.gz
adrci
asm+
asmcmd
dgmgrl
README
README.english
rman
sql+
sql.functions
sqlplus
sqlplus.all
sqlplus.cdb
sqlplus.dba
sqlplus.dbms_packages
sqlplus.functions
sqlplus.gvdollar
sqlplus.init
sqlplus._init
sqlplus.packages
sqlplus.rman
sqlplus.tables
sqlplus.user
sqlplus.utl_packages
sqlplus.vdollar
[oracle@6898242bcdd5 rlwrap-extensions]$

aliasの追記


[oracle@6898242bcdd5 ~]$ ll -la
total 40
drwx------. 1 oracle oinstall   113 Jan 20 10:57 .
drwxr-xr-x. 1 root   root        20 Jan 19 23:19 ..
-rw-------. 1 oracle oinstall   168 Jan 20 10:22 .bash_history
-rw-r--r--. 1 oracle oinstall    18 Aug 24 16:12 .bash_logout
-rw-r--r--. 1 oracle oinstall   193 Aug 24 16:12 .bash_profile
-rw-r--r--. 1 oracle oinstall   231 Aug 24 16:12 .bashrc
-rw-r--r--. 1 oracle oinstall   172 Aug 31 18:34 .kshrc
drwxr-x---. 2 oracle oinstall    74 Jan 20 08:47 .oracle_jre_usage
-rw-r--r--. 1 root   root     15080 Jan 20 10:52 epel-release-7-11.noarch.rpm
drwxr-xr-x. 2 oracle oinstall  4096 Jan 20 10:58 rlwrap-extensions
lrwxrwxrwx. 1 root   root        26 Jan 19 23:19 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@6898242bcdd5 ~]$ vi .bashrc

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=

# User specific aliases and functions
alias sqlplus='rlwrap -pRed -if ~/rlwrap-extensions/sqlplus sqlplus'

gloginの追記


[oracle@6898242bcdd5 ~]$ find / -name *login*.sql -type f 2>/dev/null
/opt/oracle/product/18c/dbhome_1/sqlplus/admin/glogin.sql
[oracle@6898242bcdd5 ~]$ vim /opt/oracle/product/18c/dbhome_1/sqlplus/admin/glogin.sql

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
SET FEED ON
--SET AUTOTRACE TRACEONLY
SET TIMING ON
--SET HIST ON
DEFINE_EDITOR=VIM
SET SQLP "_USER'@'_CONNECT_IDENTIFIER> "

gloginの設定確認


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 11:07:58 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@ORCL>
Display all 104 possibilities? (y or n)
ABORT          COMMENT        DEFERRED       FROM           INTEGER        MLSLABEL       OPTION         RENAME         SIZE           TRANSACTIONAL  VIEW
ACCESS         COMPRESS       DELETE         GRANT          INTERSECT      MODE           ORDER          RESOURCE       SMALLINT       TRIGGER        WHENEVER
ALTER          CONNECT        DESC           GROUP          INTO           MODIFY         PAGESIZE       REVOKE         SPOOL          UNION          WHERE
ARCHIVE        CREATE         DISTINCT       HAVING         LEVEL          NOAUDIT        PARAMETER      ROWID          START          UNIQUE         WITH
AUDIT          CURRENT        DROP           IDENTIFIED     LIKE           NOCOMPRESS     PCTFREE        ROWNUM         STARTUP        UNQUIESCE
BETWEEN        DATABASE       ELSE           IMMEDIATE      LINESIZE       NOWAIT         PLUGGABLE      ROWS           SUCCESSFUL     UPDATE
CHAR           DATE           EXCLUSIVE      INCREMENT      LOCK           NULL           PRIOR          SELECT         SYNONYM        VALIDATE
CHECK          DECIMAL        EXISTS         INDEX          LONG           NUMBER         PRIVILEGES     SESSION        TABLE          VALUES
CLUSTER        DEFAULT        FILE           INITIAL        MAXEXTENTS     OFFLINE        PUBLIC         SHARE          TABLESPACE     VARCHAR
COLUMN         DEFERRABLE     FLOAT          INSERT         MINUS          ONLINE         QUIESCE        SHUTDOWN       THEN           VARCHAR2
SYS@ORCL>
SYS@ORCL> show pdbs

       CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------------- ------------------------------ ---------- ----------
            2 PDB$SEED                       READ ONLY  NO
            3 PDB1                           READ WRITE NO
SYS@ORCL> show pdbs

コンテナ内のip確認

デフォルトゲートウェイは
192.168.200.1

コンテナ名 ipアドレス サブネットワーク
orcl_18cr3_1 192.168.200.2 192.168.200.0/24
orcl_18cr3_2 192.168.200.3 192.168.200.0/24

接続元のtnsnames.oraの編集

接続先のipを追記する


[oracle@6898242bcdd5 ~]$ find / -name tnsnames.ora -type f 2>/dev/null
/opt/oracle/product/18c/dbhome_1/network/admin/samples/tnsnames.ora
/opt/oracle/oradata/dbconfig/ORCL/tnsnames.ora
[oracle@6898242bcdd5 ~]$ vim /opt/oracle/oradata/dbconfig/ORCL/tnsnames.ora

修正前


ORCL=localhost:1521/ORCL
PDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)

修正後


ORCL=localhost:1521/ORCL
PDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)
RMTPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.3)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)

接続元のユーザへの権限付与


[oracle@6898242bcdd5 ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 11:47:23 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> grant create database link to aine;

Grant succeeded.

Elapsed: 00:00:00.03
SYS@pdb1> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

接続元のユーザによるDBLINK作成


[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 11:48:09 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 10:21:43 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> CREATE DATABASE LINK testlink CONNECT TO aine IDENTIFIED BY ORACLE_PWD USING 'RMTPDB1';

Database link created.

Elapsed: 00:00:00.02
AINE@pdb1>

接続先データベースの環境確認

aineユーザーとkuraineにオブジェクト投入してみる

作成ユーザーの確認


[oracle@46ba47fe0739 ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 12:01:20 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SYS@pdb1> select username from dba_users order by created desc fetch first 4 rows only;

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
NAHATO
MUJIKU
KURAINE
AINE

4 rows selected.

Elapsed: 00:00:00.01
SYS@pdb1>

aineユーザーで実行するsqlスクリプト


CREATE TABLE warehouse_mst AS
SELECT DISTINCT
    rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
	,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
FROM
	dual
CONNECT BY
	LEVEL <= &1
;
CREATE TABLE item_mst AS
SELECT DISTINCT
    s1.warehouse_code
    ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
    ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
FROM
    warehouse_mst s1
CONNECT BY
	LEVEL <= &1
;
CREATE TABLE loc_mst AS 
SELECT DISTINCT
	warehouse_code
    ,main_loc AS loc
	,lpad(ROWNUM,2,'0') AS ZONE
	,substr(main_loc,1,2) AS BLOCK
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
	,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
FROM
    item_mst
;
CREATE TABLE zone_mst AS 
SELECT DISTINCT
    warehouse_code
	,ZONE
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
FROM
    loc_mst
;

aineユーザーからsqlスクリプトを実行


[oracle@46ba47fe0739 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 12:01:47 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 10:23:24 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select table_name from user_tables;

no rows selected

Elapsed: 00:00:00.11
AINE@pdb1> CREATE TABLE warehouse_mst AS
  2  SELECT DISTINCT
  3      rpad('w_',6,dbms_random.STRING('X',4)) AS warehouse_code
  4  ,TRUNC(ABS(dbms_random.VALUE(10,20)),0) AS shipping_permission_over_days
  5  FROM
  6  dual
  7  CONNECT BY
  8  LEVEL <= &1
  9  ;
Enter value for 1: 3
old   8: LEVEL <= &1
new   8: LEVEL <= 3

Table created.

Elapsed: 00:00:01.53
AINE@pdb1> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
WAREHOUSE_MST

1 row selected.

Elapsed: 00:00:00.02
AINE@pdb1> CREATE TABLE item_mst AS
  2  SELECT DISTINCT
  3      s1.warehouse_code
  4      ,rpad('p_',6,dbms_random.STRING('X',4)) AS item
  5      ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
  6      ,dbms_random.STRING('U',2) || lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),5,'0') AS main_loc
  7  FROM
  8      warehouse_mst s1
  9  CONNECT BY
 10  LEVEL <= &1
 11  ;
Enter value for 1: 3
old  10: LEVEL <= &1
new  10: LEVEL <= 3

Table created.

Elapsed: 00:00:00.04
AINE@pdb1> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
WAREHOUSE_MST
ITEM_MST

2 rows selected.

Elapsed: 00:00:00.01
AINE@pdb1> CREATE TABLE loc_mst AS
  2  SELECT DISTINCT
  3  warehouse_code
  4      ,main_loc AS loc
  5  ,lpad(ROWNUM,2,'0') AS ZONE
  6  ,substr(main_loc,1,2) AS BLOCK
  7  ,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),3,'0') AS area
  8  ,lpad(TRUNC(ABS(dbms_random.VALUE(1,10)),0),2,'0') AS LINE
  9  FROM
 10      item_mst
 11  ;

Table created.

Elapsed: 00:00:00.04
AINE@pdb1> CREATE TABLE zone_mst AS
  2  SELECT DISTINCT
  3      warehouse_code
  4  ,ZONE
  5  ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS zone_kbn
  6  FROM
  7      loc_mst
  8  ;

Table created.

Elapsed: 00:00:00.02

INE@pdb1> select * from warehouse_mst;

WAREHOUSE_CODE           SHIPPING_PERMISSION_OVER_DAYS
------------------------ -----------------------------
w_C95K                                              18
w_E3L4                                              10
w_S1VQ                                              14

3 rows selected.

Elapsed: 00:00:00.03
AINE@pdb1> col main_loc for a24
AINE@pdb1> select * from item_mst;

WAREHOUSE_CODE           ITEM                     ITEM_KBN                                 MAIN_LOC
------------------------ ------------------------ ---------------------------------------- ------------------------
w_E3L4                   p_LHEX                   5                                        GF00002
w_E3L4                   p_MUAA                   3                                        XN00003
w_E3L4                   p_F0ZP                   4                                        CK00004
w_C95K                   p_2GHP                   7                                        SB00003
w_S1VQ                   p_0324                   7                                        QM00006
w_C95K                   p_ODXG                   3                                        IK00007
w_C95K                   p_FUVO                   9                                        AX00008
w_E3L4                   p_VVYX                   8                                        JB00005
w_E3L4                   p_Q6MU                   2                                        VW00001
w_S1VQ                   p_GVHE                   4                                        MN00005
w_E3L4                   p_HGG5                   2                                        FQ00004
w_S1VQ                   p_66V0                   8                                        WS00004
w_C95K                   p_4FLH                   9                                        QQ00003
w_C95K                   p_RHMC                   6                                        JP00009
w_E3L4                   p_02B9                   4                                        FV00003
w_S1VQ                   p_RYSS                   7                                        MU00002
w_E3L4                   p_XQEH                   7                                        QC00008
w_C95K                   p_SFWR                   9                                        IC00002
w_E3L4                   p_40RU                   2                                        WK00007
w_E3L4                   p_CXJK                   7                                        DY00003
w_C95K                   p_X2IQ                   6                                        FH00005
w_S1VQ                   p_HWQD                   8                                        MR00008
w_C95K                   p_PQJG                   1                                        DF00007
w_S1VQ                   p_45E6                   5                                        VI00005
w_S1VQ                   p_GE23                   4                                        WX00003
w_E3L4                   p_S966                   9                                        KG00003
w_C95K                   p_74DK                   9                                        QK00006
w_S1VQ                   p_7NUZ                   7                                        DC00007
w_E3L4                   p_N8AD                   3                                        EM00008
w_C95K                   p_PNWQ                   2                                        HN00005
w_S1VQ                   p_J5FD                   8                                        ZR00003
w_C95K                   p_YXHY                   8                                        QG00006
w_S1VQ                   p_JGXF                   9                                        ZE00009
w_C95K                   p_XAPH                   6                                        II00002
w_C95K                   p_LB0V                   6                                        PO00004
w_S1VQ                   p_VOG1                   3                                        NE00003
w_S1VQ                   p_ICT3                   3                                        CC00002
w_S1VQ                   p_0HJ1                   8                                        TW00008
w_E3L4                   p_YGHQ                   7                                        AV00003

39 rows selected.
AINE@pdb1> col LOC for a24
AINE@pdb1> select * from loc_mst;

WAREHOUSE_CODE           LOC                      ZONE     BLOCK    AREA         LINE
------------------------ ------------------------ -------- -------- ------------ --------
w_E3L4                   XN00003                  02       XN       003          08
w_C95K                   AX00008                  07       AX       002          04
w_E3L4                   VW00001                  09       VW       005          06
w_E3L4                   DY00003                  20       DY       004          04
w_S1VQ                   MR00008                  22       MR       007          03
w_S1VQ                   WX00003                  25       WX       001          07
w_C95K                   HN00005                  30       HN       003          05
w_C95K                   SB00003                  04       SB       009          04
w_C95K                   JP00009                  14       JP       002          04
w_C95K                   II00002                  34       II       003          07
w_C95K                   PO00004                  35       PO       005          03
w_E3L4                   FQ00004                  11       FQ       008          03
w_E3L4                   FV00003                  15       FV       001          03
w_S1VQ                   VI00005                  24       VI       008          08
w_S1VQ                   ZE00009                  33       ZE       001          01
w_C95K                   IK00007                  06       IK       008          09
w_S1VQ                   MU00002                  16       MU       006          05
w_E3L4                   WK00007                  19       WK       006          02
w_E3L4                   EM00008                  29       EM       002          04
w_S1VQ                   ZR00003                  31       ZR       008          05
w_S1VQ                   NE00003                  36       NE       004          04
w_E3L4                   GF00002                  01       GF       007          07
w_C95K                   QK00006                  27       QK       004          05
w_E3L4                   JB00005                  08       JB       009          09
w_E3L4                   QC00008                  17       QC       001          01
w_S1VQ                   CC00002                  37       CC       007          07
w_S1VQ                   MN00005                  10       MN       009          01
w_S1VQ                   WS00004                  12       WS       001          01
w_C95K                   IC00002                  18       IC       002          04
w_S1VQ                   DC00007                  28       DC       005          06
w_C95K                   QG00006                  32       QG       009          01
w_S1VQ                   TW00008                  38       TW       003          07
w_E3L4                   CK00004                  03       CK       005          05
w_S1VQ                   QM00006                  05       QM       009          09
w_C95K                   QQ00003                  13       QQ       009          05
w_C95K                   FH00005                  21       FH       009          02
w_C95K                   DF00007                  23       DF       008          02
w_E3L4                   KG00003                  26       KG       005          04
w_E3L4                   AV00003                  39       AV       004          09

39 rows selected.

Elapsed: 00:00:00.00
AINE@pdb1> select * from zone_mst;

WAREHOUSE_CODE           ZONE     ZONE_KBN
------------------------ -------- ----------------------------------------
w_E3L4                   09       3
w_E3L4                   20       6
w_C95K                   04       1
w_E3L4                   39       6
w_S1VQ                   25       4
w_C95K                   06       3
w_C95K                   21       1
w_S1VQ                   24       5
w_E3L4                   29       2
w_E3L4                   03       7
w_E3L4                   26       6
w_E3L4                   02       9
w_S1VQ                   22       8
w_E3L4                   11       5
w_S1VQ                   36       1
w_E3L4                   08       3
w_C95K                   07       1
w_C95K                   14       2
w_C95K                   35       5
w_E3L4                   19       1
w_S1VQ                   31       9
w_S1VQ                   37       8
w_S1VQ                   10       5
w_C95K                   18       1
w_S1VQ                   28       8
w_C95K                   23       9
w_S1VQ                   16       5
w_C95K                   32       4
w_S1VQ                   05       5
w_S1VQ                   33       6
w_E3L4                   01       4
w_C95K                   27       4
w_E3L4                   17       3
w_S1VQ                   12       8
w_S1VQ                   38       5
w_C95K                   30       7
w_C95K                   34       3
w_E3L4                   15       9
w_C95K                   13       1

39 rows selected.

Elapsed: 00:00:00.02

kuraineユーザーで実行するsqlスクリプト


CREATE TABLE cust_mst AS 
SELECT
    rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
	,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
	,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
FROM
	dual
CONNECT BY
	LEVEL <= &1
;
CREATE TABLE basket_mst AS 
SELECT DISTINCT
    basket_no
	,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
FROM
    cust_mst
;
CREATE TABLE cart_mst AS 
SELECT DISTINCT
    cart_no
	,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
	,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
FROM
    cust_mst
;
CREATE TABLE route_mst AS 
SELECT DISTINCT
	warehouse_code
	,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
	,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
FROM
    aine.warehouse_mst
;

kuraineユーザーからsqlスクリプトを実行


[oracle@46ba47fe0739 ~]$ sqlplus kuraine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 12:19:45 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

KURAINE@pdb1> select table_name from user_tables;

no rows selected

Elapsed: 00:00:00.02
KURAINE@pdb1> CREATE TABLE cust_mst AS
  2  SELECT
  3      rpad('c_',6,dbms_random.STRING('X',4)) AS cust_id
  4  ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
  5  ,'bas' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS basket_no
  6  ,'car' || lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS cart_no
  7  FROM
  8  dual
  9  CONNECT BY
 10  LEVEL <= &1
 11  ;
Enter value for 1: 3
old  10: LEVEL <= &1
new  10: LEVEL <= 3

Table created.

Elapsed: 00:00:00.04
KURAINE@pdb1> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
CUST_MST

1 row selected.

Elapsed: 00:00:00.01
KURAINE@pdb1> CREATE TABLE basket_mst AS
  2  SELECT DISTINCT
  3      basket_no
  4  ,TRUNC(ABS(dbms_random.VALUE(2000,5000)),0) AS upper_limit_weight
  5  ,TRUNC(ABS(dbms_random.VALUE(2000,4000)),0) AS upper_limit_volume
  6  ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS basket_kbn
  7  FROM
  8      cust_mst
  9  ;

Table created.

Elapsed: 00:00:00.04
KURAINE@pdb1> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
CUST_MST
BASKET_MST

2 rows selected.

Elapsed: 00:00:00.01

KURAINE@pdb1> CREATE TABLE cart_mst AS
  2  SELECT DISTINCT
  3      cart_no
  4  ,TRUNC(ABS(dbms_random.VALUE(1,20)),0) AS upper_stack_cnt
  5  ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cart_kbn
  6  FROM
  7      cust_mst
  8  ;

Table created.

Elapsed: 00:00:00.02

KURAINE@pdb1> CREATE TABLE route_mst AS
  2  SELECT DISTINCT
  3  warehouse_code
  4  ,rpad('r_',6,dbms_random.STRING('X',4)) AS route_no
  5  ,lpad(TRUNC(dbms_random.VALUE(1,99),0),2,'0') AS barth_no
  6  FROM
  7      aine.warehouse_mst
  8  ;

Table created.

Elapsed: 00:00:00.03

KURAINE@pdb1> CREATE TABLE store_mst AS
  2  SELECT DISTINCT
  3  rpad('s_',6,dbms_random.STRING('X',4)) AS store_no
  4  ,s1.route_no AS route_no
  5      ,lpad(TRUNC(ABS(dbms_random.VALUE(1,99)),0),2,'0') AS route_order
  6      ,s2.cust_id AS cust_id
  7  ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS store_kbn
  8  FROM
  9      route_mst s1
 10  ,cust_mst s2
 11  ;

Table created.

Elapsed: 00:00:00.03
KURAINE@pdb1> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
CUST_MST
BASKET_MST
CART_MST
ROUTE_MST
STORE_MST

5 rows selected.

Elapsed: 00:00:00.01

KURAINE@pdb1> select * from cust_mst;

CUST_ID                  CUST_KBN                                 BASKET_NO   CART_NO
------------------------ ---------------------------------------- ----------- -----------
c_8OL5                   2                                        bas98       car47
c_Q2DS                   1                                        bas21       car57
c_OQMJ                   6                                        bas18       car86

3 rows selected.

Elapsed: 00:00:00.02
KURAINE@pdb1> select * from basket_mst;

BASKET_NO   UPPER_LIMIT_WEIGHT UPPER_LIMIT_VOLUME BASKET_KBN
----------- ------------------ ------------------ ----------------------------------------
bas98                     4097               3454 3
bas18                     4793               3313 7
bas21                     3287               2757 6

3 rows selected.

Elapsed: 00:00:00.02

KURAINE@pdb1> select * from cart_mst;

CART_NO     UPPER_STACK_CNT CART_KBN
----------- --------------- ----------------------------------------
car47                     1 7
car86                     2 5
car57                    16 3

3 rows selected.

Elapsed: 00:00:00.02
KURAINE@pdb1> select * from route_mst;

WAREHOUSE_CODE           ROUTE_NO                 BARTH_NO
------------------------ ------------------------ --------
w_C95K                   r_9RFG                   69
w_E3L4                   r_Z5D1                   50
w_S1VQ                   r_JLI5                   50

3 rows selected.

Elapsed: 00:00:00.02
KURAINE@pdb1> select * from store_mst;

STORE_NO                 ROUTE_NO                 ROUTE_OR CUST_ID                  STORE_KBN
------------------------ ------------------------ -------- ------------------------ ----------------------------------------
s_OQFB                   r_9RFG                   70       c_OQMJ                   1
s_USYL                   r_9RFG                   07       c_8OL5                   7
s_K4EL                   r_JLI5                   39       c_8OL5                   2
s_W5FE                   r_JLI5                   47       c_Q2DS                   1
s_6F0A                   r_Z5D1                   52       c_8OL5                   5
s_VB44                   r_9RFG                   40       c_Q2DS                   5
s_3OZL                   r_JLI5                   80       c_OQMJ                   7
s_62XI                   r_Z5D1                   87       c_Q2DS                   6
s_E900                   r_Z5D1                   63       c_OQMJ                   8

9 rows selected.

Elapsed: 00:00:00.02

接続先情報のサマリ

ユーザ名 テーブル名
aine WAREHOUSE_MST
aine ITEM_MST
aine LOC_MST
aine ZONE_MST
kuraine CUST_MST
kuraine BASKET_MST
kuraine CART_MST
kuraine ROUTE_MST
kuraine STORE_MST

接続元から接続先のオブジェクトへdblinkする

接続元のdblink設定の確認


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 12:46:41 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 12:46:11 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col DB_LINK for a24
AINE@pdb1> col USERNAME for a24
AINE@pdb1> col VAL for a24
AINE@pdb1> select DB_LINK,USERNAME,VALID from user_db_links;

DB_LINK                  USERNAME                 VAL
------------------------ ------------------------ ---
TESTLINK                 AINE                     YES

1 row selected.

Elapsed: 00:00:00.00

接続先のaineユーザオブジェクトへのdblink


AINE@pdb1> select * from aine.warehouse_mst@testlink;

WAREHOUSE_CODE           SHIPPING_PERMISSION_OVER_DAYS
------------------------ -----------------------------
w_C95K                                              18
w_E3L4                                              10
w_S1VQ                                              14

3 rows selected.

Elapsed: 00:00:00.07
AINE@pdb1> col main_loc for a24
AINE@pdb1> select * from aine.item_mst@testlink;

WAREHOUSE_CODE           ITEM                     ITEM_KBN                                 MAIN_LOC
------------------------ ------------------------ ---------------------------------------- ------------------------
w_E3L4                   p_LHEX                   5                                        GF00002
w_E3L4                   p_MUAA                   3                                        XN00003
w_E3L4                   p_F0ZP                   4                                        CK00004
w_C95K                   p_2GHP                   7                                        SB00003
w_S1VQ                   p_0324                   7                                        QM00006
w_C95K                   p_ODXG                   3                                        IK00007
w_C95K                   p_FUVO                   9                                        AX00008
w_E3L4                   p_VVYX                   8                                        JB00005
w_E3L4                   p_Q6MU                   2                                        VW00001
w_S1VQ                   p_GVHE                   4                                        MN00005
w_E3L4                   p_HGG5                   2                                        FQ00004
w_S1VQ                   p_66V0                   8                                        WS00004
w_C95K                   p_4FLH                   9                                        QQ00003
w_C95K                   p_RHMC                   6                                        JP00009
w_E3L4                   p_02B9                   4                                        FV00003
w_S1VQ                   p_RYSS                   7                                        MU00002
w_E3L4                   p_XQEH                   7                                        QC00008
w_C95K                   p_SFWR                   9                                        IC00002
w_E3L4                   p_40RU                   2                                        WK00007
w_E3L4                   p_CXJK                   7                                        DY00003
w_C95K                   p_X2IQ                   6                                        FH00005
w_S1VQ                   p_HWQD                   8                                        MR00008
w_C95K                   p_PQJG                   1                                        DF00007
w_S1VQ                   p_45E6                   5                                        VI00005
w_S1VQ                   p_GE23                   4                                        WX00003
w_E3L4                   p_S966                   9                                        KG00003
w_C95K                   p_74DK                   9                                        QK00006
w_S1VQ                   p_7NUZ                   7                                        DC00007
w_E3L4                   p_N8AD                   3                                        EM00008
w_C95K                   p_PNWQ                   2                                        HN00005
w_S1VQ                   p_J5FD                   8                                        ZR00003
w_C95K                   p_YXHY                   8                                        QG00006
w_S1VQ                   p_JGXF                   9                                        ZE00009
w_C95K                   p_XAPH                   6                                        II00002
w_C95K                   p_LB0V                   6                                        PO00004
w_S1VQ                   p_VOG1                   3                                        NE00003
w_S1VQ                   p_ICT3                   3                                        CC00002
w_S1VQ                   p_0HJ1                   8                                        TW00008
w_E3L4                   p_YGHQ                   7                                        AV00003

39 rows selected.

Elapsed: 00:00:00.01

接続先のkuraineユーザオブジェクトへのdblink


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 12:52:11 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 12:46:41 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select * from kuraine.cust_mst@testlink;

CUST_ID                  CUST_KBN                                 BASKET_NO   CART_NO
------------------------ ---------------------------------------- ----------- -----------
c_8OL5                   2                                        bas98       car47
c_Q2DS                   1                                        bas21       car57
c_OQMJ                   6                                        bas18       car86

3 rows selected.

Elapsed: 00:00:00.09
AINE@pdb1> select * from kuraine.basket_mst@testlink;

BASKET_NO   UPPER_LIMIT_WEIGHT UPPER_LIMIT_VOLUME BASKET_KBN
----------- ------------------ ------------------ ----------------------------------------
bas98                     4097               3454 3
bas18                     4793               3313 7
bas21                     3287               2757 6

3 rows selected.

Elapsed: 00:00:00.01

接続元オブジェクトの確認


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 13:16:16 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 12:52:11 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> select TABLE_NAME from user_tables;

no rows selected

Elapsed: 00:00:00.02
AINE@pdb1>

接続先のオブジェクトを使用して、接続元にオブジェクト作成できるか試す。

接続元のaineユーザーで実行するsqlスクリプト


CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.warehouse_code
    ,s1.item
    ,s2.warehousing_date AS warehousing_date
    , s3.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    aine.warehouse_mst@testlink s0
    ,aine.item_mst@testlink s1
    ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s2
    , LATERAL(SELECT s3.std_date,to_char(TO_DATE(s3.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s3 WHERE s2.std_date <= s3.std_date) s3
;
CREATE TABLE sales_trn AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    row_number() over (partition by s0.warehouse_code order by rownum) AS seq
    ,s0.warehouse_code
    ,s1.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s2.cust_id
    ,s2.store_no
    ,s3.std_date AS arrive_date
FROM
    aine.warehouse_mst@testlink s0
    ,aine.item_mst@testlink s1
    ,kuraine.store_mst@testlink s2
    ,make_date s3
;

接続元のaineユーザーからsqlスクリプトを実行


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 14:45:43 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 14:44:01 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.00
AINE@pdb1> CREATE TABLE stock AS
  2  WITH make_date AS (
  3      SELECT
  4          to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
  5      FROM
  6          (
  7              SELECT
  8                  TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
  9                  , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
 10              FROM
 11                  dual
 12          ) A
 13      CONNECT BY
 14          LEVEL <= A.date_to - A.date_from + 1
 15  )
 16  SELECT
 17      s0.warehouse_code
 18      ,s1.item
 19      ,s2.warehousing_date AS warehousing_date
 20      , s3.expiration_date AS expiration_date
 21      , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
 22      , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
 23  FROM
 24      aine.warehouse_mst@testlink s0
 25      ,aine.item_mst@testlink s1
 26      ,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s2
 27      , LATERAL(SELECT s3.std_date,to_char(TO_DATE(s3.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s3 WHERE s2.std_date <= s3.std_date) s3
 28  ;

Table created.

Elapsed: 00:00:00.18
AINE@pdb1> CREATE TABLE sales_trn AS
  2  WITH make_date AS (
  3      SELECT
  4          to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
  5      FROM
  6          (
  7              SELECT
  8                  TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
  9                  , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
 10              FROM
 11                  dual
 12          ) A
 13      CONNECT BY
 14          LEVEL <= A.date_to - A.date_from + 1
 15  )
 16  SELECT
 17      row_number() over (partition by s0.warehouse_code order by rownum) AS seq
 18      ,s0.warehouse_code
 19      ,s1.item
 20      ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
 21      ,s2.cust_id
 22      ,s2.store_no
 23      ,s3.std_date AS arrive_date
 24  FROM
 25      aine.warehouse_mst@testlink s0
 26      ,aine.item_mst@testlink s1
 27      ,kuraine.store_mst@testlink s2
 28      ,make_date s3
 29  ;

Table created.

Elapsed: 00:00:00.08


接続先情報のサマリ(再掲)

ユーザ名 テーブル名
aine WAREHOUSE_MST
aine ITEM_MST
aine LOC_MST
aine ZONE_MST
kuraine CUST_MST
kuraine BASKET_MST
kuraine CART_MST
kuraine ROUTE_MST
kuraine STORE_MST

接続元情報のサマリ

ユーザ名 テーブル名
aine STOCK
aine SALES_TRN

接続元のaineユーザーオブジェクトと接続先のaineユーザーオブジェクト&kuraineユーザーオブジェクトを結合してみる。

インデックス(主キー)を追加する

接続先のaineユーザーに追加


ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);
ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);
ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);
ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_2 bash
[oracle@46ba47fe0739 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 14:21:01 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 14:13:28 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> ALTER TABLE warehouse_mst ADD CONSTRAINT warehouse_mst_pk PRIMARY KEY(warehouse_code);

Table altered.

Elapsed: 00:00:00.07
AINE@pdb1> ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(warehouse_code,item);

Table altered.

Elapsed: 00:00:00.02
AINE@pdb1> ALTER TABLE loc_mst ADD CONSTRAINT loc_mst_pk PRIMARY KEY(warehouse_code,loc);

Table altered.

Elapsed: 00:00:00.02
AINE@pdb1> ALTER TABLE zone_mst ADD CONSTRAINT zone_mst_pk PRIMARY KEY(warehouse_code,ZONE);

Table altered.

Elapsed: 00:00:00.02
AINE@pdb1>

接続先のkuraineユーザーに追加


ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;
ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;
ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;
ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);
ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_2 bash
[oracle@46ba47fe0739 ~]$ sqlplus kuraine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 14:22:44 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 13:01:24 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

KURAINE@pdb1> ALTER TABLE cust_mst ADD  CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id) ;

Table altered.

Elapsed: 00:00:00.03
KURAINE@pdb1> ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no) ;

Table altered.

Elapsed: 00:00:00.02
KURAINE@pdb1> ALTER TABLE cart_mst ADD  CONSTRAINT cart_mst_pk PRIMARY KEY(cart_no) ;

Table altered.

Elapsed: 00:00:00.03
KURAINE@pdb1> ALTER TABLE route_mst ADD CONSTRAINT route_mst_pk PRIMARY KEY(warehouse_code,route_no);

Table altered.

Elapsed: 00:00:00.01
KURAINE@pdb1> ALTER TABLE store_mst ADD CONSTRAINT store_mst_pk PRIMARY KEY(store_no);

Table altered.

Elapsed: 00:00:00.02
KURAINE@pdb1>

接続元のaineユーザーに追加


ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(warehouse_code,item,warehousing_date,expiration_date);
ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(warehouse_code,seq);

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 14:23:48 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 14:13:05 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY(warehouse_code,item,warehousing_date,expiration_date);

Table altered.

Elapsed: 00:00:00.04
AINE@pdb1> ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(warehouse_code,seq);

Table altered.

Elapsed: 00:00:00.02

リモートDBの単一スキーマへのアクセス

sqlスクリプト


SELECT
	s1.warehouse_code
	,s1.item
	,s1.item_kbn
	,s2.loc
	,s2.ZONE
	,s2.BLOCK
	,s2.area
	,s2.LINE
	,s3.zone_kbn
FROM
	aine.item_mst@testlink s1
	,aine.loc_mst@testlink s2
	,aine.zone_mst@testlink s3
WHERE
	s1.warehouse_code = s2.warehouse_code
AND s1.main_loc = s2.loc
AND s2.warehouse_code = s2.warehouse_code
AND s2.ZONE = s3.ZONE
;

sqlスクリプトの実行


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 15:02:17 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 15:02:03 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> SELECT
  2  s1.warehouse_code
  3  ,s1.item
  4  ,s1.item_kbn
  5  ,s2.loc
  6  ,s2.ZONE
  7  ,s2.BLOCK
  8  ,s2.area
  9  ,s2.LINE
 10  ,s3.zone_kbn
 11  FROM
 12  aine.item_mst@testlink s1
 13  ,aine.loc_mst@testlink s2
 14  ,aine.zone_mst@testlink s3
 15  WHERE
 16  s1.warehouse_code = s2.warehouse_code
 17  AND s1.main_loc = s2.loc
 18  AND s2.warehouse_code = s2.warehouse_code
 19  AND s2.ZONE = s3.ZONE
 20  ;

39 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3042657825

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |            |    39 |  2223 |     9  (12)| 00:00:01 |        |
|*  1 |  HASH JOIN                    |            |    39 |  2223 |     9  (12)| 00:00:01 |        |
|   2 |   MERGE JOIN                  |            |    39 |  2028 |     6  (17)| 00:00:01 |        |
|   3 |    TABLE ACCESS BY INDEX ROWID| LOC_MST    |    39 |  1092 |     2   (0)| 00:00:01 |   PDB1 |
|   4 |     INDEX FULL SCAN           | LOC_MST_PK |    39 |       |     1   (0)| 00:00:01 |   PDB1 |
|*  5 |    SORT JOIN                  |            |    39 |   936 |     4  (25)| 00:00:01 |        |
|   6 |     TABLE ACCESS FULL         | ITEM_MST   |    39 |   936 |     3   (0)| 00:00:01 |   PDB1 |
|   7 |   TABLE ACCESS FULL           | ZONE_MST   |    39 |   195 |     3   (0)| 00:00:01 |   PDB1 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A2"."ZONE"="A1"."ZONE")
   5 - access("A3"."WAREHOUSE_CODE"="A2"."WAREHOUSE_CODE" AND "A3"."MAIN_LOC"="A2"."LOC")
       filter("A3"."MAIN_LOC"="A2"."LOC" AND "A3"."WAREHOUSE_CODE"="A2"."WAREHOUSE_CODE")

Note
-----
   - fully remote statement


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          0  consistent gets
          0  physical reads
        280  redo size
       3206  bytes sent via SQL*Net to client
        646  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         39  rows processed

リモートDBの複数スキーマへのアクセス

sqlスクリプト


SELECT
	s1.warehouse_code
	,s1.item
	,s1.item_kbn
	,s2.loc
	,s2.ZONE
	,s2.BLOCK
	,s2.area
	,s2.LINE
	,s3.zone_kbn
	,s4.route_no
	,s4.barth_no
FROM
	aine.item_mst@testlink s1
	,aine.loc_mst@testlink s2
	,aine.zone_mst@testlink s3
	,kuraine.route_mst@testlink s4
WHERE
	s1.warehouse_code = s2.warehouse_code
AND s1.main_loc = s2.loc
AND s2.warehouse_code = s2.warehouse_code
AND s2.ZONE = s3.ZONE
AND s3.warehouse_code = s4.warehouse_code
;

sqlスクリプトの実行


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 15:06:55 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 15:02:17 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> SELECT
  2  s1.warehouse_code
  3  ,s1.item
  4  ,s1.item_kbn
  5  ,s2.loc
  6  ,s2.ZONE
  7  ,s2.BLOCK
  8  ,s2.area
  9  ,s2.LINE
 10  ,s3.zone_kbn
 11  ,s4.route_no
 12  ,s4.barth_no
 13  FROM
 14  aine.item_mst@testlink s1
 15  ,aine.loc_mst@testlink s2
 16  ,aine.zone_mst@testlink s3
 17  ,kuraine.route_mst@testlink s4
 18  WHERE
 19  s1.warehouse_code = s2.warehouse_code
 20  AND s1.main_loc = s2.loc
 21  AND s2.warehouse_code = s2.warehouse_code
 22  AND s2.ZONE = s3.ZONE
 23  AND s3.warehouse_code = s4.warehouse_code
 24  ;

39 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 3046062355

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE        |            |    39 |  3159 |    12   (9)| 00:00:01 |        |
|*  1 |  HASH JOIN                     |            |    39 |  3159 |    12   (9)| 00:00:01 |        |
|*  2 |   HASH JOIN                    |            |    39 |  2496 |     9  (12)| 00:00:01 |        |
|   3 |    MERGE JOIN                  |            |    39 |  2028 |     6  (17)| 00:00:01 |        |
|   4 |     TABLE ACCESS BY INDEX ROWID| LOC_MST    |    39 |  1092 |     2   (0)| 00:00:01 |   PDB1 |
|   5 |      INDEX FULL SCAN           | LOC_MST_PK |    39 |       |     1   (0)| 00:00:01 |   PDB1 |
|*  6 |     SORT JOIN                  |            |    39 |   936 |     4  (25)| 00:00:01 |        |
|   7 |      TABLE ACCESS FULL         | ITEM_MST   |    39 |   936 |     3   (0)| 00:00:01 |   PDB1 |
|   8 |    TABLE ACCESS FULL           | ZONE_MST   |    39 |   468 |     3   (0)| 00:00:01 |   PDB1 |
|   9 |   TABLE ACCESS FULL            | ROUTE_MST  |     3 |    51 |     3   (0)| 00:00:01 |   PDB1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A2"."WAREHOUSE_CODE"="A1"."WAREHOUSE_CODE")
   2 - access("A3"."ZONE"="A2"."ZONE")
   6 - access("A4"."WAREHOUSE_CODE"="A3"."WAREHOUSE_CODE" AND "A4"."MAIN_LOC"="A3"."LOC")
       filter("A4"."MAIN_LOC"="A3"."LOC" AND "A4"."WAREHOUSE_CODE"="A3"."WAREHOUSE_CODE")

Note
-----
   - fully remote statement
   - this is an adaptive plan


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          0  consistent gets
          0  physical reads
        280  redo size
       3486  bytes sent via SQL*Net to client
        646  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         39  rows processed

ローカルDBへのアクセスとリモートDBの複数スキーマへのアクセス

sqlスクリプト


SELECT
	s0.warehouse_code
	,s0.arrive_date
	,s0.cust_id
	,s5.cust_kbn
	,s0.store_no
	,s6.store_kbn
	,s0.prov_inst_qty
	,s1.item
	,s1.item_kbn
	,s2.loc
	,s2.ZONE
	,s2.BLOCK
	,s2.area
	,s2.LINE
	,s3.zone_kbn
	,s4.route_no
	,s4.barth_no
	,s7.cart_no
	,s8.basket_no
FROM
	aine.sales_trn s0
	,aine.item_mst@testlink s1
	,aine.loc_mst@testlink s2
	,aine.zone_mst@testlink s3
	,kuraine.route_mst@testlink s4
	,kuraine.cust_mst@testlink s5
	,kuraine.store_mst@testlink s6
	,kuraine.cart_mst@testlink s7
	,kuraine.basket_mst@testlink s8
WHERE
	s0.warehouse_code = s1.warehouse_code
AND s1.warehouse_code = s2.warehouse_code
AND s1.main_loc = s2.loc
AND s2.warehouse_code = s2.warehouse_code
AND s2.ZONE = s3.ZONE
AND s3.warehouse_code = s4.warehouse_code
AND s0.cust_id = s5.cust_id
AND s0.store_no = s6.store_no
AND s5.cart_no = s7.cart_no
AND s5.basket_no = s8.basket_no
;

sqlスクリプトの実行


[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@6898242bcdd5 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 20 15:20:45 2019
Version 18.3.0.0.0

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

Last Successful login time: Sun Jan 20 2019 15:06:55 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> SELECT
  2  s0.warehouse_code
  3  ,s0.arrive_date
  4  ,s0.cust_id
  5  ,s5.cust_kbn
  6  ,s0.store_no
  7  ,s6.store_kbn
  8  ,s0.prov_inst_qty
  9  ,s1.item
 10  ,s1.item_kbn
 11  ,s2.loc
 12  ,s2.ZONE
 13  ,s2.BLOCK
 14  ,s2.area
 15  ,s2.LINE
 16  ,s3.zone_kbn
 17  ,s4.route_no
 18  ,s4.barth_no
 19  ,s7.cart_no
 20  ,s8.basket_no
 21  FROM
 22  aine.sales_trn s0
 23  ,aine.item_mst@testlink s1
 24  ,aine.loc_mst@testlink s2
 25  ,aine.zone_mst@testlink s3
 26  ,kuraine.route_mst@testlink s4
 27  ,kuraine.cust_mst@testlink s5
 28  ,kuraine.store_mst@testlink s6
 29  ,kuraine.cart_mst@testlink s7
 30  ,kuraine.basket_mst@testlink s8
 31  WHERE
 32  s0.warehouse_code = s1.warehouse_code
 33  AND s1.warehouse_code = s2.warehouse_code
 34  AND s1.main_loc = s2.loc
 35  AND s2.warehouse_code = s2.warehouse_code
 36  AND s2.ZONE = s3.ZONE
 37  AND s3.warehouse_code = s4.warehouse_code
 38  AND s0.cust_id = s5.cust_id
 39  AND s0.store_no = s6.store_no
 40  AND s5.cart_no = s7.cart_no
 41  AND s5.basket_no = s8.basket_no
 42  ;

41067 rows selected.

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------
Plan hash value: 3744340248

--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |   175 |     0   (0)| 00:00:01 |        |      |
|*  1 |  HASH JOIN           |           | 41067 |  6456K|    22   (5)| 00:00:01 |        |      |
|   2 |   REMOTE             |           |     3 |    63 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|*  3 |   HASH JOIN          |           | 41067 |  5614K|    19   (0)| 00:00:01 |        |      |
|   4 |    REMOTE            | STORE_MST |     9 |   234 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|*  5 |    HASH JOIN         |           | 41067 |  4571K|    17   (0)| 00:00:01 |        |      |
|*  6 |     HASH JOIN        |           |    39 |  3159 |     8   (0)| 00:00:01 |        |      |
|*  7 |      HASH JOIN       |           |    39 |  2223 |     6   (0)| 00:00:01 |        |      |
|*  8 |       HASH JOIN      |           |    39 |  1131 |     4   (0)| 00:00:01 |        |      |
|   9 |        REMOTE        | ROUTE_MST |     3 |    51 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|  10 |        REMOTE        | ZONE_MST  |    39 |   468 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|  11 |       REMOTE         | LOC_MST   |    39 |  1092 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|  12 |      REMOTE          | ITEM_MST  |    39 |   936 |     2   (0)| 00:00:01 | TESTL~ | R->S |
|  13 |     TABLE ACCESS FULL| SALES_TRN |  3159 |   101K|     9   (0)| 00:00:01 |        |      |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S0"."CUST_ID"="S5"."CUST_ID")
   3 - access("S0"."STORE_NO"="S6"."STORE_NO")
   5 - access("S0"."WAREHOUSE_CODE"="S1"."WAREHOUSE_CODE")
   6 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."MAIN_LOC"="S2"."LOC")
   7 - access("S2"."ZONE"="S3"."ZONE")
   8 - access("S3"."WAREHOUSE_CODE"="S4"."WAREHOUSE_CODE")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "A1"."CUST_ID","A1"."CUST_KBN","A1"."BASKET_NO","A1"."CART_NO","A2"."CART_NO
       ","A3"."BASKET_NO" FROM "KURAINE"."CUST_MST" "A1","KURAINE"."CART_MST"
       "A2","KURAINE"."BASKET_MST" "A3" WHERE "A1"."BASKET_NO"="A3"."BASKET_NO" AND
       "A1"."CART_NO"="A2"."CART_NO" (accessing 'TESTLINK' )

   4 - SELECT "STORE_NO","STORE_KBN" FROM "KURAINE"."STORE_MST" "S6" (accessing
       'TESTLINK' )

   9 - SELECT "WAREHOUSE_CODE","ROUTE_NO","BARTH_NO" FROM "KURAINE"."ROUTE_MST" "S4"
       (accessing 'TESTLINK' )

  10 - SELECT "WAREHOUSE_CODE","ZONE","ZONE_KBN" FROM "AINE"."ZONE_MST" "S3" (accessing
        'TESTLINK' )

  11 - SELECT "WAREHOUSE_CODE","LOC","ZONE","BLOCK","AREA","LINE" FROM "AINE"."LOC_MST"
        "S2" (accessing 'TESTLINK' )

  12 - SELECT "WAREHOUSE_CODE","ITEM","ITEM_KBN","MAIN_LOC" FROM "AINE"."ITEM_MST" "S1"
        (accessing 'TESTLINK' )



Statistics
----------------------------------------------------------
         12  recursive calls
          1  db block gets
        407  consistent gets
          0  physical reads
        280  redo size
    2366464  bytes sent via SQL*Net to client
      30730  bytes received via SQL*Net from client
       2739  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      41067  rows processed

Leave a Reply

Your email address will not be published. Required fields are marked *