oracle sql 差分更新について考える話

まえがき

考えてみようかなと。

環境

コード表示

[oracle@centos ~]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                 PORTS                                            NAMES
42b3dd2da618        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   3 hours ago         Up 3 hours (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos ~]$ docker exec -it orcl_19cr3 /bin/bash
[oracle@42b3dd2da618 ~]$ sqlplus aine/ORACLE_PWD@pdb1 

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 25 15:59:05 2019
Version 19.3.0.0.0

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

Last Successful login time: Sat May 25 2019 15:17:56 +09:00

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

SQL> 

対象データ

コード表示

drop table src purge;
create table src as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
connect by
  level <= 10;

drop table tgt purge;
create table tgt as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
where
 level between 3 and 7
connect by
  level <= 10;

対象データ確認

コード表示

SQL> select * from src;

	RN STR
---------- ----
	 1 A
	 2 B
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G
	 8 H
	 9 I
	10 J

10 rows selected.

SQL> select * from tgt;

	RN STR
---------- ----
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G

chk.sql

これでなにも取れなかったら、差分更新できたことにする。

コード表示

WITH src_minus_tgt AS (
  SELECT
    *
  FROM
    src
  MINUS
  SELECT
    *
  FROM
    tgt
), tgt_minus_src AS (
  SELECT
    *
  FROM
    tgt
  MINUS
  SELECT
    *
  FROM
    src
)
SELECT
  *
FROM
  src_minus_tgt
UNION ALL
SELECT
  *
FROM
  tgt_minus_src;

ptn1

コード表示

insert into tgt
select
  s1.*
from
  src s1
    left outer join tgt s2
      on
        s1.rn=s2.rn
where
  s2.rn is null
;
commit;

ptn2

コード表示

insert into tgt
select
  s1.*
from
  src s1
where
  not exists(
          select
            1
          from
            tgt s2
          where
            s1.rn=s2.rn
          )
;
commit;

ptn3

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      where
        not exists(
                select
                  1
                from
                  tgt s2
                where
                  s1.rn=s2.rn
                )
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

ptn4

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      minus
      select
        s2.*
      from
        tgt s2
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

あとがき

んんん、もうちょい臨場感がほしいい。fin。

awkの範囲指定演算子を理解したかった話(理解できなかった。)

範囲指定演算子のうごき

内部的にはパタンの真偽を判定している。3になるまでパタンは偽で、3行目以上5行目以下を出力。組込変数NRが5より大きくなったら、偽0にして5より大きい値を出力できないように制御している。とかおもってたけどね。。

コード表示

[root@61eff46d2318 html]# seq 1 10 | awk 'NR==3,NR==5'
3
4
5

sql的には。。。

これを別の切り口で理解したいがために19cr3いんすとした。

コード表示

[oracle@7347fe12d03f ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 1 22:12:09 2019
Version 19.3.0.0.0

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

Last Successful login time: Wed May 01 2019 21:56:10 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select level from dual connect by level <= 10;

     LEVEL
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10

10 rows selected.

SQL> select level as rn,case when level between 3 and 5 then 1 else 0 end as bol from dual connect by level <= 10;

	RN	  BOL
---------- ----------
	 1	    0
	 2	    0
	 3	    1
	 4	    1
	 5	    1
	 6	    0
	 7	    0
	 8	    0
	 9	    0
	10	    0

10 rows selected.
SQL> with sub as ( 
  2  select level as rn,case when level between 3 and 5 then 1 else 0 end as bol from dual connect by level <= 10)select * from sub where bol = 1;

	RN	  BOL
---------- ----------
	 3	    1
	 4	    1
	 5	    1

範囲指定演算子のうごき

内部的にはパタンの真偽を判定している。

コード表示

[root@61eff46d2318 html]# seq 1 10 | awk 'NR%3==0,NR%2==0'
3
4
6
9
10
[root@61eff46d2318 html]# seq 1 10 | awk 'NR%2==0,NR%3==0'
2
3
4
5
6
8
9
10
[root@61eff46d2318 html]# seq 1 10 | awk 'NR%2==0'
2
4
6
8
10
[root@61eff46d2318 html]# seq 1 10 | awk 'NR%3==0'
3
6
9

sql的には。。。

コード表示

SQL> select level as rn
  2  ,case when mod(level,3)=0 then 1 else 0 end as div3
  3  ,case when mod(level,2)=0 then 1 else 0 end as div2
  4  from dual connect by level <= 10;

	RN	 DIV3	    DIV2
---------- ---------- ----------
	 1	    0	       0
	 2	    0	       1
	 3	    1	       0
	 4	    0	       1
	 5	    0	       0
	 6	    1	       1
	 7	    0	       0
	 8	    0	       1
	 9	    1	       0
	10	    0	       1

10 rows selected.

sql的には。。。とかやってみたけど、理解できなかった。

コード表示

SQL> select level as rn
  2  ,case when greatest(2,3)<=level then 1 else 0 end as liner
  3  ,case when mod(level,3)=0 then 1 else 0 end as div3
  4  ,case when mod(level,2)=0 then 1 else 0 end as div2
  5  from dual connect by level <= 10;

	RN	LINER	    DIV3       DIV2
---------- ---------- ---------- ----------
	 1	    0	       0	  0
	 2	    0	       0	  1
	 3	    1	       1	  0
	 4	    1	       0	  1
	 5	    1	       0	  0
	 6	    1	       1	  1
	 7	    1	       0	  0
	 8	    1	       0	  1
	 9	    1	       1	  0
	10	    1	       0	  1

10 rows selected.

コンマ演算子と関係あると思ったけど、

seq 1 10 | awk ‘NR%2==0,NR%3==0’の結果で5が出力されて7が出力されない理由がわかれば、理解できるとは思った。awkってパタン真の奴出力されるんだよね。アクション省略されたら。

この範囲指定演算子は,カンマの前後がスイッチのように働きます.つまり,上記の場合には「組込変数 NR が 10 から組込変数 NR が 20 まで」という意味ではなく,「組込変数 NR が 10 になったらパターンを真にして,組込変数 NR が 20 になったらパターンを偽にする」という意味になるので注意しましょう。

とはあるけど、分からなかった。

ソースコードも見てみたけど分からなかった。なにで検索したらいいか分からなかった。またあとでしらべたい。

コード表示

[root@61eff46d2318 html]# cat /etc/red*
CentOS Linux release 7.6.1810 (Core) 
[root@61eff46d2318 gawk-4.0.2]# rpm -qfi `which awk`
Name        : gawk
Version     : 4.0.2
Release     : 4.el7_3.1
Architecture: x86_64
Install Date: Wed Mar  6 02:34:52 2019
Group       : Applications/Text
Size        : 2435978
License     : GPLv3+ and GPL and LGPLv3+ and LGPL and BSD
Signature   : RSA/SHA256, Thu Jun 29 21:40:38 2017, Key ID 24c6a8a7f4a80eb5
Source RPM  : gawk-4.0.2-4.el7_3.1.src.rpm
Build Date  : Thu Jun 29 06:52:50 2017
Build Host  : c1bm.rdu2.centos.org
Relocations : (not relocatable)
Packager    : CentOS BuildSystem 
Vendor      : CentOS
URL         : http://www.gnu.org/software/gawk/gawk.html
Summary     : The GNU version of the awk text processing utility
Description :
The gawk package contains the GNU version of awk, a text processing
utility. Awk interprets a special-purpose programming language to do
quick and easy text pattern matching and reformatting jobs.

Install the gawk package if you need a text processing utility. Gawk is
considered to be a standard Linux tool for processing text.
[root@61eff46d2318 html]# curl -OL http://vault.centos.org/7.6.1810/os/Source/SPackages/gawk-4.0.2-4.el7_3.1.src.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 2696k  100 2696k    0     0   927k      0  0:00:02  0:00:02 --:--:--  927k
[root@61eff46d2318 html]# ll
total 2700
-rw-r--r--. 1 root root 2761533 May  2 12:11 gawk-4.0.2-4.el7_3.1.src.rpm
[root@61eff46d2318 html]# rpm2cpio *rpm | cpio -id
5388 blocks
[root@61eff46d2318 html]# ll
total 5400
-rw-r--r--. 1 root root 2761533 May  2 12:11 gawk-4.0.2-4.el7_3.1.src.rpm
-rw-rw-r--. 1 root root     583 May  2 12:11 gawk-4.0.2-fix-chksize-calls.patch
-rw-rw-r--. 1 root root 2742713 May  2 12:11 gawk-4.0.2.tar.gz
-rw-rw-r--. 1 root root   14753 May  2 12:11 gawk.spec
[root@61eff46d2318 html]# tar -zxvf gawk-4.0.2.tar.gz
gawk-4.0.2/
gawk-4.0.2/version.in
.
.
.
[root@61eff46d2318 html]# ll
total 5408
drwxrwxr-x. 13 1000 1000    4096 Dec 26  2012 gawk-4.0.2
-rw-r--r--.  1 root root 2761533 May  2 12:11 gawk-4.0.2-4.el7_3.1.src.rpm
-rw-rw-r--.  1 root root     583 May  2 12:11 gawk-4.0.2-fix-chksize-calls.patch
-rw-rw-r--.  1 root root 2742713 May  2 12:11 gawk-4.0.2.tar.gz
-rw-rw-r--.  1 root root   14753 May  2 12:11 gawk.spec
[root@61eff46d2318 gawk-4.0.2]# find . -name "*cnm*"
[root@61eff46d2318 gawk-4.0.2]# find . -name "*comma*"
./test/addcomma.ok
./test/addcomma.awk
./test/addcomma.in
./command.y
./command.c
[root@61eff46d2318 gawk-4.0.2]# find . -name "*ope*"
./vms/vms_popen.c
./pc/popen.c
./pc/popen.h

また戻ってこよう。これにてfin

finしてない。範囲演算子は‖とか&&とか<=,<,>,>=で書いていくのが読みやすいいよなきっと。

LOB型の削除に関して

参考文献

How to drop Oracle LOB  

今の状況

拙作スキーマクリーニングスクリプトですとLOBオブジェクトが残っている。。消さなくてもいいと思うけど、気になったから、調べた。

EXECUTE IMMEDIATE をはじめて書いた。スキーマ内クリーニングスクリプト  

結論

パージすればいい

コード表示

purge recyclebin;

dmpとかするシェルスクリプト

ディレクトリマウント

OSとORACLEをつなぐ。

コード表示

cd /mnt/18cr3/share/dump/ORCL/aine/in
cd /mnt/18cr3/share/dump/ORCL/aine/in/liz
cd /mnt/18cr3/share/dump/ORCL/aine/out
cd /mnt/18cr3/share/dump/ORCL/aine/out/liz
cd /mnt/18cr3/share/query
cd /mnt/18cr3/share/sh
--sys session
CREATE OR REPLACE DIRECTORY dp_out_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/out';
GRANT READ ON DIRECTORY dp_out_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_out_aine_dir TO aine;
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

フォルダ構成

RACできたら、かえると思う

コード表示

[oracle@f285aba0589a mnt]$ pwd
/mnt
[oracle@f285aba0589a mnt]$ tree
.
|-- 11gr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- xe
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
|-- 12cr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- ORCL
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
`-- 18cr3
    |-- init
    |   |-- create_user.sql
    |   `-- startup.sh
    |-- oradata
    `-- share
        |-- dump
        |   `-- ORCL
        |       |-- aine
        |       |   |-- in
        |       |   |   |-- dup___test_01___.png
        |       |   |   |-- dup___test_02___.png
        |       |   |   |-- dup___test_03___.png
        |       |   |   |-- tbl___loader___.dat
        |       |   |   |-- tbl___loader___.log
        |       |   |   |-- tbl___loader____18130.log_xt
        |       |   |   `-- test.png
        |       |   `-- out
        |       |       |-- aine_user_scheduler_job_log_20190406111520.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111520.log
        |       |       |-- aine_user_scheduler_job_log_20190406111533.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111533.log
        |       |       |-- aine_user_scheduler_job_log_20190406111929.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111929.log
        |       |       |-- aine_user_scheduler_job_log_20190406113707.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406113707.log
        |       |       |-- aine_user_scheduler_job_log_20190406114236.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406114236.log
        |       |       |-- aine_user_scheduler_job_log_20190406115931.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406115931.log
        |       |       `-- liz
        |       |           `-- tar_tbl
        |       |-- kuraine
        |       |   |-- in
        |       |   `-- out
        |       |-- mujiku
        |       |   |-- in
        |       |   `-- out
        |       `-- nahato
        |           |-- in
        |           `-- out
        |-- login
        |-- query
        |   `-- drop_create.sql
        |-- sh
        |   `-- exe.sh
        |-- spool
        |   `-- test.html
        `-- zip
            `-- v18c.zip

68 directories, 30 files

シェルスクリプト

雰囲気でつくった。imp、query、spoolの場合はまだ。bash再入門してるから、もっとスマートかきたいものだ。ひっさしぶりにかいた。

コード表示

#!/bin/bash

echo "enter username number."
echo "-->1:aine 2:kuraine 3:nahato 4:mujiku"
read USR_PAT
echo "enter password number."
echo "-->1:ORACLE_PWD"
read PWD_PAT
echo "enter database connect identifer number."
echo "-->1:@pdb1 2:@pdb2 3:@pdb3 4:@pdb4"
read DB_PAT
echo "enter target table liz file name number."
echo "-->1:tar_tbl"
read FNM_PAT
echo "enter version number."
echo "-->3:18cr3 2:12cr2 1:11gr2"
read VRN_PAT
echo "enter ORACLE_SID number."
echo "-->1:ORCL"
read SID_PAT
echo 'enter usage number'
echo '1:dump_out 2:dump_in 3:spool 4:query'
read USE_PAT

case ${USR_PAT} in
    1)
        USR=aine
        ;;
    2)
        USR=kuraine
        ;;
    3)
        USR=nahato
        ;;
    4)
        USR=mujiku
        ;;
    *)
        ;;
esac

case ${PWD_PAT} in
    1)
        PWD=ORACLE_PWD
        ;;
    *)
        ;;
esac

case ${DB_PAT} in
    1)
        DB=@pdb1
        ;;
    2)
        DB=@pdb2
        ;;
    3)
        DB=@pdb3
        ;;
    4)
        DB=@pdb4
        ;;
    *)
        ;;
esac

case ${FNM_PAT} in
    1)
        FNM=tar_tbl
        ;;
    *)
        ;;
esac

case ${VRN_PAT} in
    1)
        VRN=11gr2
        ;;
    2)
        VRN=12cr2
        ;;
    3)
        VRN=18cr3
        ;;
    *)
        ;;
esac

case ${SID_PAT} in
    1)
        SID=ORCL
        ;;
    *)
        ;;
esac

USC=_
SLH=/
PFX=tmp_

MNT_DIR=/mnt/${VRN}/share

LGN_INFO=${USR}${SLH}${PWD}${DB}
SYS_DATE=`date '+%Y%m%d%H%M%S'`
DP_EXT=.dmp
LOG_EXT=.log
QRY_DIR=${MNT_DIR}${SLH}query${SLH}

case ${USE_PAT} in
    1)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}out${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        while read LN
        do
            sqlplus ${LGN_INFO} @${QRY_DIR}drop_create.sql ${PFX} ${LN} <<EOF
EOF
            LOG_NM=${USR}${USC}${LN}${USC}
            expdp ${LGN_INFO} DIRECTORY=dp_out_${USR}_dir DUMPFILE=${LOG_NM}${SYS_DATE}${DP_EXT} LOG=${LOG_NM}${SYS_DATE}${LOG_EXT} TABLES=${PFX}${LN}
        done < ${LZ_DIR}${FNM}
        ;;
    2)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}in${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        ;;
    *)
        ;;
esac

sqlスクリプト

シェルスクリプトのなかで@${QRY_DIR}drop_create.sqlとかしてるやつ

コード表示

drop table &1&2;
create table &1&2 nologging parallel as select * from &2;

tar_tblリスト

エキスポート対象のテーブルリスト。

コード表示

user_scheduler_job_log

実行結果

tar_tblリストはすこし増やして4つにした。できてそう。


コード表示

[oracle@f285aba0589a out]$ pwd
/mnt/18cr3/share/dump/ORCL/aine/out
[oracle@f285aba0589a out]$ ll -lt | head -9
total 4224
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___RCP____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___RCP____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___MST____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___MST____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___AVL____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___AVL____20190407175116.log
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___STF____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___STF____20190407175116.dmp

hotなディクショナリビュー

user_source

行番号でるの!ってなった。

コード表示

select * from user_source;

user_dependencies

loopで階層構造を表現できそう

コード表示

select * from user_dependencies;

user_arguments

ファンクションやプロシージャの引数がわかる!!!

コード表示

select * from user_arguments;

user_tab_privs

オブジェクトに対する権限わかる

コード表示

select * from user_tab_privs;

dba_directories

オラクルにマウントしたディレクトリオブジェクトの一覧分かる

コード表示

select * from dba_directories;

dictionary

未知の開拓するときとりあえずぐぐる

コード表示

select * from dictionary;

dict_columns

未知の開拓するときとりあえずぐぐる。カラムの情報手に入る。

コード表示

select * from dict_columns;

recyclebin

ゴミ箱。

コード表示

select * from recyclebin;
purge recyclebin;

user_objects

クリーニングするときとか

コード表示

select * from user_objects;

コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


blob バイナリ複製&バイナリ取込 with plsql

まえがき

おもしろそうだったので触れてみた!!!面白かった!!仕事疲れたけど、気晴らしに触れてみた!まぁ余計に疲れるけどね、よいのです、そんなことは。

参考文献

事の発端

UTL_FILE.FCOPY()とかFRENAME()とか  

clobだったら、LOADCLOBFROMFILE

LOADBLOBFROMFILEプロシージャ  

オープンする

FILEOPENプロシージャ  

クローズする

FILECLOSEプロシージャ  

ファイルをインスタンス化するてきな感じ。メモリ上にオブジェクト作るかんじかな。

BFILENAME  

DBMSLOB.SQLパッケージで定義される定数の一つlobmaxsize INTEGER 18446744073709551615

DBMS_LOB.LOBMAXSIZE  

コストラクタ的なやつ

EMPTY_BLOB、EMPTY_CLOB  

clob型にぶち込むときに文字コード指定したりするやつ。

NLS_CHARSET_ID  

バッファにためて

GET_RAWファンクション  

ためたバッファをファイルに吐く。んで、バッファフラッシュ。ファイルから読み込んで取得したバイナリデータをメモリにのせてファイルに吐いた後、バッファをクリーンするイメージかな。

PUT_RAWプロシージャ  

事前準備

OSのディレクトリをオラクルアプリが認識できるようにマウントさせる。sqlでそういうことできるんだもんな。べんりだな。

コード表示

--sys session
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

--aine session with dba role

select * from dba_directories;
select * from dba_tab_privs where table_name = 'DP_IN_AINE_DIR';

コンスト定義

定義したらいい

コード表示

create or replace package const___symbolic_character___
as
	comma___ constant varchar2(100) := ',';
	coron___ constant varchar2(100) := ':';
    dot___ constant varchar2(100) := '.';
	equal___ constant varchar2(100) := '=';
	left_parentheses___ constant varchar2(100) := '(';
	right_parentheses___ constant varchar2(100) := ')';
	semi_coron___ constant varchar2(100) := ';';
	single_quote___ constant varchar2(100) := '''';
	space___ constant varchar2(100) := ' ';
    under_score___ constant varchar2(100) := '_';
    function of___comma___(p_comma___ varchar2 := comma___) return varchar2;
    function of___coron___(p_coron___ varchar2 := coron___) return varchar2;
	function of___dot___(p_dot___ varchar2 := dot___) return varchar2;
    function of___equal___(p_equal___ varchar2 := equal___) return varchar2;
    function of___left_parentheses___(p_left_parentheses___ varchar2 := left_parentheses___) return varchar2;
    function of___right_parentheses___(p_right_parentheses___ varchar2 := right_parentheses___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2 := semi_coron___) return varchar2;
    function of___single_quote___(p_single_quote___ varchar2 := single_quote___) return varchar2;
    function of___space___(p_space___ varchar2 := space___) return varchar2;
    function of___under_score___(p_under_score___ varchar2 := under_score___) return varchar2;
end;
/
create or replace package body const___symbolic_character___
is
    function of___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100) := p_comma___; begin return rt; end; 
    function of___coron___(p_coron___ varchar2) return varchar2 is rt varchar2(100) := p_coron___; begin return rt; end; 
    function of___dot___(p_dot___ varchar2) return varchar2 is rt varchar2(100)  :=  dot___; begin return rt; end; 
    function of___equal___(p_equal___ varchar2) return varchar2 is rt varchar2(100) := p_equal___; begin return rt; end; 
    function of___left_parentheses___(p_left_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_left_parentheses___; begin return rt; end; 
    function of___right_parentheses___(p_right_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_right_parentheses___; begin return rt; end; 
    function of___semi_coron___(p_semi_coron___ varchar2) return varchar2 is rt varchar2(100) := p_semi_coron___; begin return rt; end; 
    function of___single_quote___(p_single_quote___ varchar2) return varchar2 is rt varchar2(100) := p_single_quote___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100) := p_space___; begin return rt; end;
    function of___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100)  :=  under_score___; begin return rt; end; 
end;
/

ファイル系のコンスト定義

定義したらいい

コード表示

create or replace package const___file_usage___
is
    prefix_file_name___ constant varchar2(100):='dup';
    file_png_extension___ constant varchar2(100):='png';
    file_gif_extension___ constant varchar2(100):='gif';
    file_jpg_extension___ constant varchar2(100):='jpg';
    file_jpeg_extension___ constant varchar2(100):='jpeg';
    file_mp3_extension___ constant varchar2(100):='mp3';
    function of___prefix_file_name___(p_prefix_file_name___ varchar2 := prefix_file_name___) return varchar2;
    function of___file_png_extension___(p_file_png_extension___ varchar2 := file_png_extension___) return varchar2;
    function of___file_gif_extension___(p_file_gif_extension___ varchar2 := file_gif_extension___) return varchar2;
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2 := file_jpg_extension___) return varchar2;
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2 := file_jpeg_extension___) return varchar2;
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2 := file_mp3_extension___) return varchar2;
end;
/
create or replace package body const___file_usage___
is
    function of___prefix_file_name___(p_prefix_file_name___ varchar2) return varchar2 is rt varchar2(100) := prefix_file_name___; begin return rt; end; 
    function of___file_png_extension___(p_file_png_extension___ varchar2) return varchar2 is rt varchar2(100) := file_png_extension___; begin return rt; end; 
    function of___file_gif_extension___(p_file_gif_extension___ varchar2) return varchar2 is rt varchar2(100) := file_gif_extension___; begin return rt; end; 
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpg_extension___; begin return rt; end; 
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpeg_extension___; begin return rt; end; 
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2) return varchar2 is rt varchar2(100) := file_mp3_extension___; begin return rt; end; 
end;
/

スクリプト

ファイルのバイナリは吐き出すと、マウントしたファイルのバイナリーデータはメモリからなくなるから、毎回オープンしてメモリにのせないといけないとおもう。そういうもんじゃないかな。パッケージプロシージャにしました。引数に複製する個数と複製元ファイル名(拡張子除く)と拡張子を指定してgo。ちなみに同じファイル名存在するとora errorなので、take care。ユーザー定義型で例外きってもいいと思う。

コード表示

create or replace package pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number);
end;
/

create or replace package body pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number)
	is
		src_file   utl_file.file_type;
		dst_file   utl_file.file_type;
		buffer     raw(32767);
		dup_cnt number := p_dup_cnt;
		src_fnm varchar2(100) := '';
		dup_fnm varchar2(100) := '';
	begin
		loop
			exit when dup_cnt = 0;
			src_fnm := p_src_file
			|| const___symbolic_character___.of___dot___
			|| const___file_usage___.of___file_png_extension___;
			dup_fnm := const___file_usage___.of___prefix_file_name___ 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| p_src_file
			|| const___symbolic_character___.of___under_score___
			|| lpad(dup_cnt,2,0) 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___dot___
			|| case
					when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
					when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
					when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
					when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
					when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
					else null
				end
			;
			src_file := utl_file.fopen('DP_IN_AINE_DIR', src_fnm, 'rb', 32767);
			dst_file := utl_file.fopen('DP_IN_AINE_DIR', dup_fnm, 'wb', 32767);
			loop
				begin
					utl_file.get_raw(src_file, buffer, 32767);
					utl_file.put_raw(dst_file, buffer, true);
				exception
					when no_data_found then
						exit;
				end;
			end loop;
			utl_file.fclose(dst_file);
			utl_file.fclose(src_file);
			dup_cnt := dup_cnt - 1;
		end loop;
	end;
end;
/

exec pkg___fio_usage___.proc___dup___(3,'test',1);
/

結果

コピーされたバイナリファイルは644でつかられるぽい。

コード表示

[oracle@f285aba0589a in]$ ll
total 24
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png
[oracle@f285aba0589a in]$ ll
total 60
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_01___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_02___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

作成したファイル取り込もうとおもう

EMPTY_BLOB()してるから必ず、blb_locは0となってポインタは先頭から処理開始するイメージかな。ただ、複製したファイルだとちゃんと取り込めてるかわからんから、異なるバイナリデータを3つ用意してみる。

コード表示

[oracle@f285aba0589a in]$ ll
total 52
-rw-r--r--. 1   1000     1001 4287 Feb 24 13:09 dup___test_01___.png
-rw-r--r--. 1   1000     1001 5221 Feb 25 08:18 dup___test_02___.png
-rw-r--r--. 1   1000     1001 9611 Mar 22 06:46 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

取込スクリプト

苦戦したのが、dst_oftとsrc_oftの2つには常に1を設定しておきたいが、コンストできないから、変数無駄に増えてしまったこと。うまくできないかなー。

コード表示

drop table tbl___blob___ purge;
create table tbl___blob___ (rn number,blb blob);
select * from tbl___blob___;
desc tbl___blob___;

create or replace procedure proc___imp___(p_dup_cnt number,p_src_file varchar2,p_ext number)
as
	bfl_pnt bfile;
	blb_loc blob;
	rn number;
	dst_oft number := 1;--not allowable constant variable
	src_oft number := 1;--not allowable constant variable
	tmp_dst_oft constant number := 1;--error escape
	tmp_src_oft constant number := 1;--error escape
	dup_cnt number := p_dup_cnt;
	src_fnm varchar2(100) := '';
begin
	loop
		exit when dup_cnt = 0;
		src_fnm := const___file_usage___.of___prefix_file_name___ 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| p_src_file
		|| const___symbolic_character___.of___under_score___
		|| lpad(dup_cnt,2,0) 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___dot___
		|| case
				when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
				when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
				when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
				when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
				when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
				else null
			end
		;
		dbms_output.put_line(src_fnm);
		bfl_pnt := bfilename('DP_IN_AINE_DIR', src_fnm);
		select nvl(max(rn), 0) + 1 into rn from tbl___blob___;
		insert into tbl___blob___ values (rn, empty_blob()) returning blb into blb_loc;
		begin
			dbms_lob.fileopen(bfl_pnt, dbms_lob.file_readonly);
		exception when utl_file.invalid_operation then
			rollback;
			exit;
		end;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.loadblobfromfile (
		   blb_loc
		   ,bfl_pnt
		   ,dbms_lob.lobmaxsize
		   ,dst_oft
		   ,src_oft);
		commit;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.fileclose(bfl_pnt);
		dup_cnt := dup_cnt - 1;
		src_oft := tmp_src_oft;
		dst_oft := tmp_dst_oft;
	end loop;
end;
/

exec proc___imp___(3,'test',1);
/

実行ログ

できてそう

コード表示

dup___test_03___.png
writable size : 1bytes
writable size : 9612bytes
dup___test_02___.png
writable size : 1bytes
writable size : 5222bytes
dup___test_01___.png
writable size : 1bytes
writable size : 4288bytes


PL/SQLプロシージャが正常に完了しました。


結果

はじめてつくったけど、面白いねgif。ちょっとゆがんでるけど
。ご愛嬌ってことで。

sleepとかの話

参考文献

ここに書いてあることを理解できるようになる。まだできていない。

DBMS_LOCK.SLEEPの代替SQLをいろいろ考えてみよう(Oracle)  

コード表示

declare
	SRT_TP timestamp;
	END_TP timestamp;
begin
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(1);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(5);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
end;
/

+000000000 00:00:00.999889000
+000000000 00:00:04.999971000


PL/SQLプロシージャが正常に完了しました。

appendヒントとかbulk collectとかcollectionとかconnect byとかの話(nologgingパターンは忘れた)

はじめに

plsqlにdml文書いてあそんでいただけの話です。ほんとうにとりとめのない話です。

小ネタ

connect by level <= 10000000はoraerror。1000万件エラー。100万件はOK。connect byの限界(デフォルト)。ただメモリいじればいけると思う。ただマウントからやり直してインスタンス再起動はめんどい。結合で回避。

コード表示

create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000
エラー・レポート -
ORA-30009: CONNECT BY操作のためのメモリーが足りません
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.

select * from V$PARAMETER where NAME in ('workarea_size_policy','pga_aggregate_target');

drop table tbl___src___ purge;
--limit
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 1000000;
--ORA-30009
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000;
--join
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

小ネタ

正規表現つかうとオシャンにいろいろできたりする。スクリプトをコールする前で前処理を組み込みたいとき、どうしたらいいかと思う。正規表現使ってハンディにできないかと考えたら、できるという話。

まず、コンパイルする。コンソールにはかれたプロシージャ名をコピってexecなんとかに書き換える。

コード表示

exec PROC___SELECT_INSERT___
exec PROC___DIRECT_PATH_INSERT___
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___

--置換前:$
--置換後:;\r\n/

exec PROC___SELECT_INSERT___;
/
exec PROC___DIRECT_PATH_INSERT___;
/
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

必要な前処理が以下であるとする。

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

前処理を組み込むまえにonelinerにしておく。

コード表示

--置換前:$
--置換後:\\r\\n

drop table tbl___src___ purge;\r\n
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\n
drop table tbl___tar___ purge;\r\n
create table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\n
alter system checkpoint;\r\n
--alter system switch logfile;\r\n
alter system flush shared_pool;\r\n
alter system flush buffer_cache;\r\n

--置換前:\r\n
--置換後:

drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

onelinerにした後のスニペットをexecの前にぴろっと入れる。

コード表示

--置換前:^(?=exec)
--置換後:drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 <> 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/


コンパイルスクリプト

まぁあそびです。

コード表示

create or replace procedure proc___select_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___select_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___direct_path_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___direct_path_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

小ネタからできた実行スクリプト

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

実行結果コンソールログ

ここから必要な情報を抜き取る。そのままべと。

コード表示


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___select_insert___
+000000000 00:00:01.451000000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___direct_path_insert___
+000000000 00:00:01.549663000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


PL/SQLプロシージャが正常に完了しました。

--置換前:^(?!.*(proc|\+)).*$
--置換後:

--置換前:^\r\n
--置換後:

proc___select_insert___
+000000000 00:00:01.451000000
proc___direct_path_insert___
+000000000 00:00:01.549663000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000
proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000
proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000
proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000
proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000
proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000
proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000
proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000
proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


--置換前:\r\n
--置換後:\t

proc___select_insert___	+000000000 00:00:01.451000000	proc___direct_path_insert___	+000000000 00:00:01.549663000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	


--置換前:proc
--置換後:\r\nproc


proc___select_insert___	+000000000 00:00:01.451000000	
proc___direct_path_insert___	+000000000 00:00:01.549663000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	
proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	
proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	
proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	
proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	
proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	
proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	
proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	
proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	

測定結果

普通にcollectionとかでこねくりまわさんほうがよさげ。limit句で処理量を多くすると早くなるのはほぉーとなった。

おまけ

こういうのって追加パターンあるとメンテめんどってなるから、なるたけハンディにやりたい。そういった話。最初に思いついたやつ。

コード表示

declare
	cursor csr is
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select * from sub;

proc clob;

begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

うえのだとあるスクリプトだけ実行したいってなったときコメントアウトするけど、アウトするところによってはコンパイルエラーとなってめんど。なので、実行スクリプト名を格納するテーブル適当につくる。こんな感じ。

コード表示

drop table exec_script purge;
create table exec_script as
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select row_number() over (order by rownum) as seq,proc from sub;

select * from exec_script;

んで、cursorのところちょちょっとなおす。こんな感じ。where句に実行したいseqを指定できるようになるから、何番目以降とか指定できたり、特定の処理だけとかもできる。

コード表示

declare
	cursor csr is select proc from exec_script where seq = 1;
--	cursor csr is select proc from exec_script where seq >= 1;
--	cursor csr is select proc from exec_script where seq between 3 and 5;
	proc clob;
begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

前処理とかもテーブル入れておけばいいですかね。後処理とかも同様なノリでできるでしょう。

コード表示

drop table pre_script purge;
create table pre_script as
with sub as(
select 'drop table tbl___src___ purge' as proc from dual union all
select 'create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7' as proc from dual union all
select 'drop table tbl___tar___ purge' as proc from dual union all
select 'create table tbl___tar___ as select * from tbl___src___ where 1 <> 1' as proc from dual union all
select 'alter system checkpoint' as proc from dual union all
select '--alter system switch logfile' as proc from dual union all
select 'alter system flush shared_pool' as proc from dual union all
select 'alter system flush buffer_cache' as proc from dual
)select row_number() over (order by rownum) as seq,proc from sub;
select * from pre_script;

まぁ、なんでこの話したかっていうと、merge文のパターン忘れたなとおもったからで、実行するプロシージャと処理するプロシージャは切り離しておけばいいかなーとめんどくならないかなーとおもっただけのことです。実行するプロシージャもオブジェクトにしておけば実行もそんなにかかなくていいですね。こんな感じ。

コード表示

create or replace procedure script_run(p_pre_from_seq number:=1,p_pre_to_seq number:=1,p_exec_from_seq number:=1,p_exec_to_seq number:=1)
authid current_user
as
	cursor pre_csr is select proc from pre_script where seq between p_pre_from_seq and p_pre_to_seq;
	cursor exec_csr is select proc from exec_script where seq between p_exec_from_seq and p_exec_to_seq;
	pre_proc clob;
	exec_proc clob;
begin
	open exec_csr;
	loop
		begin
		fetch exec_csr into exec_proc;
		exit when exec_csr%notfound;
			open pre_csr;
			loop
				begin
				fetch pre_csr into pre_proc;
				exit when pre_csr%notfound;
				dbms_output.put_line(pre_proc);
				execute immediate pre_proc;
				exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
				end;
			end loop;
			close pre_csr;
		dbms_output.put_line('begin '|| exec_proc || '; end;');
		execute immediate 'begin '|| exec_proc || '; end;';
		dbms_output.put_line(lpad('_',60,'_'));
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close exec_csr;
end;
/

ついでに忘れた、merge文のパターンも

コード表示

create or replace procedure proc___merge_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	merge into tbl___tar___ tar
	using(select * from tbl___src___) src
	on(tar.rn = src.rn and tar.str = src.str)
	when not matched then insert values(src.rn,src.str);
    commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___merge_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

うえでつくったプロシージャ名をテーブルに投入。

コード表示

select * from exec_script;

merge into exec_script tar
using (
	with sub as(
	select 'PROC___MERGE_INSERT___' as proc from dual
	)select max(s2.seq) + 1 as seq,max(s1.proc) as proc from sub s1,exec_script s2
)src
on(tar.proc = src.proc)
when not matched then insert values(src.seq,src.proc);
commit;

select * from exec_script;

実行例。コメントとしてテーブル格納しておけば、無視してくれてるポイから、いけてる雰囲気。

コード表示

set serveroutput on;
exec script_run(1,8,1,2);
/
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___SELECT_INSERT___; end;
proc___select_insert___
+000000000 00:00:01.351316000
____________________________________________________________
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___DIRECT_PATH_INSERT___; end;
proc___direct_path_insert___
+000000000 00:00:01.449993000
____________________________________________________________


PL/SQLプロシージャが正常に完了しました。


おわりに

plsqlたのしいな!もっと楽しい方法あったら、教えてください。以上、ありがとうございました。

sourceテーブルからオブジェクト型でデータ取得し、bulk collectでcollection変換したあとtable()かましてtargetテーブルに登録した話

移行元と移行先テーブル作る

クエリ

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 3;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

select item(rn,str) from tbl___src___;
select cast(collect(item(rn,str)) as liz) from tbl___src___;

表題のプロシージャ

クエリ

コード表示

set serveroutput on;

create or replace procedure proc___liz___ as
	cnt number;
	lz liz;
	cursor csr is select item(rn,str) from tbl___src___;
begin
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	dbms_output.put_line('ele_cnt:' || lz.count);
	dbms_output.put_line('ele_fst:' || lz.first);
	dbms_output.put_line('ele_lst:' || lz.last);
	dbms_output.put_line('________________________________________');
	for i in lz.first..lz.last loop
		dbms_output.put_line('rn:' || lz(i).rn || ',str:' || lz(i).str);
	end loop;
	close csr;
	insert into tbl___tar___ select * from table(lz);
	commit;
end;
/

移行されたことを確認

クエリ

コード表示

select * from tbl___tar___;
exec proc___liz___;

実行者権限(authid current_user)と即時実行(execute immediate)のコラボがいいんじゃないか??

参考文献

なんども読み返している

で結局、BEQUEATH VIEWってなんなのさ?(Oracle)  
定義者権限および実行者権限のセキュリティの管理  

ユーザー作成

POSSESSORユーザーとINVOKERユーザーのアカウントを作成。

コード表示

--sys session
drop user POSSESSOR cascade;
drop user INVOKER cascade;
create user POSSESSOR identified by test default tablespace users quota unlimited on users;
create user INVOKER identified by test default tablespace users quota unlimited on users; 

実行例

コード表示

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

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:46:22 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> drop user POSSESSOR cascade;
drop user INVOKER cascade;
drop user POSSESSOR cascade
          *
ERROR at line 1:
ORA-01918: user 'POSSESSOR' does not exist


Elapsed: 00:00:00.02
SYS@pdb1> create user POSSESSOR identified by test default tablespace users quota unlimited on users;
create user INVOKER identified by test default tablespace users quota unlimited on users;

User dropped.

Elapsed: 00:00:00.06
SYS@pdb1>
User created.

Elapsed: 00:00:00.02
SYS@pdb1>
User created.

Elapsed: 00:00:00.02

オブジェクト作成

SYSスキーマにdual以外のオブジェクトを作成

コード表示

--sys session
drop table tbl___privilege___ purge;
create table tbl___privilege___ as select level as rn from dual connect by level <= 1; 

実行例

コード表示

SYS@pdb1> drop table tbl___privilege___ purge;

Table dropped.

Elapsed: 00:00:00.04
SYS@pdb1> create table tbl___privilege___ as select level as rn from dual connect by level <= 1;

Table created.

Elapsed: 00:00:00.03

コンパイルするファンクション

dual表とdual表以外。所有者権限と実行者権限。即時実行と即時実行以外。これらの組み合わせ。

コード表示

--possessor session
drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;
drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual___;
drop function func___definer_dual_execute_immediate___;
drop function func___definer_dual___;
drop function func___definer_except_dual_execute_immediate___;
drop function func___definer_except_dual___;

create or replace function func___current_user_dual_execute_immediate___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
    return rt;
end;
/
create or replace function func___current_user_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	select 'hello world' into rt from dual;
    return rt;
end;
/
create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
	execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___current_user_except_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
create or replace function func___definer_dual_execute_immediate___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
    return rt;
end;
/
create or replace function func___definer_dual___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	select 'hello world' into rt from dual;
    return rt;
end;
/
create or replace function func___definer_except_dual_execute_immediate___ return varchar2
authid definer
as
    rt varchar2(30);
begin
	execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___definer_except_dual___ return varchar2
authid definer
as
    rt varchar2(30);
begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
select authid,object_name from user_procedures order by authid,object_name;

実行するファンクションクエリ

クエリ

コード表示

col rt for a30
select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_DUAL___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

コンパイル時(possessorユーザー)

他人の持ち物を使って自分の持ち物を作成することはできないよって感じだな。

直接権限付与

クエリ

コード表示

--sys session
grant create session to POSSESSOR;
grant create table to POSSESSOR;
grant create view to POSSESSOR;
grant create procedure to POSSESSOR;
grant select on sys.tbl___privilege___ to POSSESSOR;

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba
SYS@pdb1> grant create session to POSSESSOR;
grant create table to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.03
SYS@pdb1> grant create view to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create procedure to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant select on sys.tbl___privilege___ to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.02

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:49:31 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

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_dual___;
drop function func___current_user_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___current_user_except_dual___;
drop function func___current_user_except_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___CURRENT_USER_EXCEPT_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;
drop function func___definer_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual___;
drop function func___definer_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;
drop function func___definer_except_dual_execute_immediate___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ does not exist


Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_except_dual___;
drop function func___definer_except_dual___
*
ERROR at line 1:
ORA-04043: object FUNC___DEFINER_EXCEPT_DUAL___ does not exist


Elapsed: 00:00:00.00
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
end;
/
create or replace function func___current_user_dual___ return varchar2
authid current_user
as
    rt varchar2(30);
begin

Function created.

Elapsed: 00:00:00.05
POSSESSOR@pdb1>   2    3    4    5    6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
  2  authid current_user
as
    rt varchar2(30);
begin
execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
    select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
create or replace function func___definer_dual_execute_immediate___ return varchar2
authid definer
as

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2    3    4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___definer_dual___ return varchar2
authid definer
as

Function created.

Elapsed: 00:00:00.03
    rt varchar2(30);
begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
    rt varchar2(30);
begin
execute immediate 'select rn from sys.tbl___privilege___' into rt;
    return rt;
end;
/
create or replace function func___definer_except_dual___ return varchar2

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>   2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
    from sys.tbl___privilege___;
    return rt;
end;
/
select authid,object_name from user_procedures order by authid,object_name;

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL___

8 rows selected.

Elapsed: 00:00:00.12

ロール経由権限付与

4つの権限(システム権限)+1つの権限(オブジェクト権限)をロールにくるんで付与してみる。

コード表示

--sys session
revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;
revoke create procedure from POSSESSOR;
revoke select on sys.tbl___privilege___ from POSSESSOR;
drop role role_test;
create role role_test;
grant create session to role_test;
grant create table to role_test;
grant create view to role_test;
grant create procedure to role_test;
grant select on sys.tbl___privilege___ to role_test;
grant role_test to possessor;

col ROLE for a30
col PRIVILEGE for a30
col ADMIN_OPTION for a30
col COMMON for a30
col INHERITED for a30
select * from role_sys_privs where role = 'ROLE_TEST';

col ROLE for a20
col OWNER for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col PRIVILEGE for a20
col GRANTABLE for a20
col COMMON for a20
col INHERITED for a20
select * from role_tab_privs where role = 'ROLE_TEST';

実行例

コード表示

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

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:52:22 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> --sys session
SYS@pdb1> revoke create session from POSSESSOR;
revoke create table from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> revoke create view from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> revoke create procedure from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke select on sys.tbl___privilege___ from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.02
SYS@pdb1> drop role role_test;
create role role_test;
grant create session to role_test;

Role dropped.

Elapsed: 00:00:00.03
SYS@pdb1>
Role created.

Elapsed: 00:00:00.01
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create table to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create procedure to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to possessor;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
SYS@pdb1> col ROLE for a30
SYS@pdb1> col PRIVILEGE for a30
SYS@pdb1> col ADMIN_OPTION for a30
SYS@pdb1> col COMMON for a30
SYS@pdb1> col INHERITED for a30
SYS@pdb1> select * from role_sys_privs where role = 'ROLE_TEST';

ROLE                           PRIVILEGE                      ADMIN_OPTION                   COMMON                         INHERITED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ROLE_TEST                      CREATE SESSION                 NO                             NO                             NO
ROLE_TEST                      CREATE PROCEDURE               NO                             NO                             NO
ROLE_TEST                      CREATE VIEW                    NO                             NO                             NO
ROLE_TEST                      CREATE TABLE                   NO                             NO                             NO

4 rows selected.

Elapsed: 00:00:00.02
SYS@pdb1>
SYS@pdb1> col ROLE for a20
SYS@pdb1> col OWNER for a20
SYS@pdb1> col TABLE_NAME for a20
SYS@pdb1> col COLUMN_NAME for a20
SYS@pdb1> col PRIVILEGE for a20
SYS@pdb1> col GRANTABLE for a20
SYS@pdb1> col COMMON for a20
SYS@pdb1> col INHERITED for a20
SYS@pdb1> select * from role_tab_privs where role = 'ROLE_TEST';

ROLE                 OWNER                TABLE_NAME           COLUMN_NAME          PRIVILEGE            GRANTABLE            COMMON               INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ROLE_TEST            SYS                  TBL___PRIVILEGE___                        SELECT               NO                   NO                   NO

1 row selected.

Elapsed: 00:00:00.09

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:53:21 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Mar 29 2019 18:49:31 +09:00

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

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;

Function dropped.

Elapsed: 00:00:00.03
POSSESSOR@pdb1> drop function func___current_user_except_dual_execute_immediate___;
drop function func___current_user_except_dual___;

Function dropped.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_dual___;

Function dropped.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_dual___ return varchar2
authid current_user

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2    3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___definer_dual___ return varchar2

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>   2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /
select a
uthid,object_name from user_procedures ordeWarning: Function created with compilation errors.r by auth
id,ob
ject_name;
Elapsed: 00:00:00.01
POSSESSOR@pdb1>
AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___

6 rows selected.

Elapsed: 00:00:00.02

実行時(invokerユーザー)

あたりまえだけど、コンパイルできたものが実行できる。

直接権限付与

クエリ

コード表示

--sys session
drop role role_test;
grant create session to POSSESSOR;
grant create table to POSSESSOR;
grant create view to POSSESSOR;
grant create procedure to POSSESSOR;
grant select on sys.tbl___privilege___ to POSSESSOR;
grant create session to INVOKER;

--possessor session
grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

col GRANTEE for a10
col TABLE_NAME for a55
col GRANTOR for a10
col PRIVILEGE for a25
col GRANTABLE for a10
col HIERARCHY for a10
col COMMON for a10
col TYPE for a10
col INHERITED for a10
select * from USER_TAB_PRIVS_MADE;


--invoker session
col OWNER for a10
col TABLE_NAME for a55
col GRANTOR for a10
col PRIVILEGE for a25
col GRANTABLE for a10
col HIERARCHY for a10
col COMMON for a10
col TYPE for a10
col INHERITED for a10
select * from USER_TAB_PRIVS_RECD ;

実行例

コード表示

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

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:55:22 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> drop role role_test;
grant create session to POSSESSOR;

Role dropped.

Elapsed: 00:00:00.02
SYS@pdb1> grant create table to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create procedure to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to POSSESSOR;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant create session to INVOKER;

Grant succeeded.

Elapsed: 00:00:00.02

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 18:55:52 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Mar 29 2019 18:53:21 +09:00

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

POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.05
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

Grant succeeded.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00
POSSESSOR@pdb1>
Grant succeeded.

Elapsed: 00:00:00.00

POSSESSOR@pdb1> col GRANTEE for a10
POSSESSOR@pdb1> col TABLE_NAME for a55
POSSESSOR@pdb1> col GRANTOR for a10
POSSESSOR@pdb1> col PRIVILEGE for a25
POSSESSOR@pdb1> col GRANTABLE for a10
POSSESSOR@pdb1> col HIERARCHY for a10
POSSESSOR@pdb1> col COMMON for a10
POSSESSOR@pdb1> col TYPE for a10
POSSESSOR@pdb1> col INHERITED for a10
POSSESSOR@pdb1> select * from USER_TAB_PRIVS_MADE;

GRANTEE    TABLE_NAME                                              GRANTOR    PRIVILEGE                 GRANTABLE  HIERARCHY  COMMON     TYPE       INHERITED
---------- ------------------------------------------------------- ---------- ------------------------- ---------- ---------- ---------- ---------- ----------
INVOKER    FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_DUAL___                             POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___    POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___CURRENT_USER_EXCEPT_DUAL___                      POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___                POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_DUAL___                                  POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___         POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
INVOKER    FUNC___DEFINER_EXCEPT_DUAL___                           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
PUBLIC     POSSESSOR                                               POSSESSOR  INHERIT PRIVILEGES        NO         NO         NO         USER       NO

9 rows selected.

Elapsed: 00:00:00.01

[oracle@f285aba0589a ~]$ sqlplus invoker/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:09:52 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Mar 29 2019 19:08:35 +09:00

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

INVOKER@pdb1> col OWNER for a10
INVOKER@pdb1> col TABLE_NAME for a55
INVOKER@pdb1> col GRANTOR for a10
INVOKER@pdb1> col PRIVILEGE for a25
INVOKER@pdb1> col GRANTABLE for a10
INVOKER@pdb1> col HIERARCHY for a10
INVOKER@pdb1> col COMMON for a10
INVOKER@pdb1> col TYPE for a10
INVOKER@pdb1> col INHERITED for a10
INVOKER@pdb1> select * from USER_TAB_PRIVS_RECD ;

OWNER      TABLE_NAME                                              GRANTOR    PRIVILEGE                 GRANTABLE  HIERARCHY  COMMON     TYPE       INHERITED
---------- ------------------------------------------------------- ---------- ------------------------- ---------- ---------- ---------- ---------- ----------
POSSESSOR  FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_DUAL___                             POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___    POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___CURRENT_USER_EXCEPT_DUAL___                      POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___                POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_DUAL___                                  POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___         POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO
POSSESSOR  FUNC___DEFINER_EXCEPT_DUAL___                           POSSESSOR  EXECUTE                   NO         NO         NO         FUNCTION   NO

8 rows selected.

Elapsed: 00:00:00.00

INVOKER@pdb1> col rt for a30
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___", line 6


Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___CURRENT_USER_EXCEPT_DUAL___", line 6


Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.01

ロール経由権限付与

4つの権限(システム権限)+1つの権限(オブジェクト権限)をロールにくるんで付与してみる。

コード表示

--sys session
revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;
revoke create procedure from POSSESSOR;
revoke select on sys.tbl___privilege___ from POSSESSOR;
drop role role_test;
create role role_test;
grant create session to role_test;
grant create table to role_test;
grant create view to role_test;
grant create procedure to role_test;
grant select on sys.tbl___privilege___ to role_test;
grant role_test to possessor;
grant role_test to invoker;

col ROLE for a30
col PRIVILEGE for a30
col ADMIN_OPTION for a30
col COMMON for a30
col INHERITED for a30
select * from role_sys_privs where role = 'ROLE_TEST';

col ROLE for a20
col OWNER for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col PRIVILEGE for a20
col GRANTABLE for a20
col COMMON for a20
col INHERITED for a20
select * from role_tab_privs where role = 'ROLE_TEST';

--possessor session
grant execute on FUNC___CURRENT_USER_DUAL___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___CURRENT_USER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL___ to invoker;
grant execute on FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ to invoker;
grant execute on FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ to invoker;

実行例

コード表示

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

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:12:17 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> revoke create session from POSSESSOR;
revoke create table from POSSESSOR;
revoke create view from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.02
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke create procedure from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> revoke select on sys.tbl___privilege___ from POSSESSOR;

Revoke succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> drop role role_test;
drop role role_test
          *
ERROR at line 1:
ORA-01919: role 'ROLE_TEST' does not exist


Elapsed: 00:00:00.01
SYS@pdb1> create role role_test;

Role created.

Elapsed: 00:00:00.02
SYS@pdb1> grant create session to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create table to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create view to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant create procedure to role_test;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1> grant select on sys.tbl___privilege___ to role_test;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to possessor;

Grant succeeded.

Elapsed: 00:00:00.01
SYS@pdb1> grant role_test to invoker;

Grant succeeded.

Elapsed: 00:00:00.00
SYS@pdb1>
SYS@pdb1> col ROLE for a30
SYS@pdb1> col PRIVILEGE for a30
SYS@pdb1> col ADMIN_OPTION for a30
SYS@pdb1> col COMMON for a30
SYS@pdb1> col INHERITED for a30
SYS@pdb1> select * from role_sys_privs where role = 'ROLE_TEST';


ROLE                           PRIVILEGE                      ADMIN_OPTION                   COMMON                         INHERITED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ROLE_TEST                      CREATE SESSION                 NO                             NO                             NO
ROLE_TEST                      CREATE PROCEDURE               NO                             NO                             NO
ROLE_TEST                      CREATE VIEW                    NO                             NO                             NO
ROLE_TEST                      CREATE TABLE                   NO                             NO                             NO

4 rows selected.

Elapsed: 00:00:00.01
SYS@pdb1> SYS@pdb1> col ROLE for a20
SYS@pdb1> col OWNER for a20
SYS@pdb1> col TABLE_NAME for a20
SYS@pdb1> col COLUMN_NAME for a20
SYS@pdb1> col PRIVILEGE for a20
SYS@pdb1> col GRANTABLE for a20
SYS@pdb1> col COMMON for a20
SYS@pdb1> col INHERITED for a20
SYS@pdb1> select * from role_tab_privs where role = 'ROLE_TEST';

ROLE                 OWNER                TABLE_NAME           COLUMN_NAME          PRIVILEGE            GRANTABLE            COMMON               INHERITED
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ROLE_TEST            SYS                  TBL___PRIVILEGE___                        SELECT               NO                   NO                   NO

1 row selected.

Elapsed: 00:00:00.00

[oracle@f285aba0589a ~]$ sqlplus possessor/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:14:10 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Mar 29 2019 19:09:33 +09:00

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

POSSESSOR@pdb1>
POSSESSOR@pdb1> --possessor session
POSSESSOR@pdb1> drop function func___current_user_dual_execute_immediate___;
drop function func___current_user_dual___;
drop function func___current_user_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.04
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___current_user_except_dual___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> drop function func___definer_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.01
POSSESSOR@pdb1>
Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_dual___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual_execute_immediate___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1> drop function func___definer_except_dual___;

Function dropped.

Elapsed: 00:00:00.00
POSSESSOR@pdb1>
POSSESSOR@pdb1> create or replace function func___current_user_dual_execute_immediate___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_dual___ return varchar2

Function created.

Elapsed: 00:00:00.03
POSSESSOR@pdb1>   2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /
create or replace function func___current_user_except_dual_execute_immediate___ return varchar2

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1>   2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___current_user_except_dual___ return varchar2
  2  authid current_user
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select' || ''' hello world ''' || 'from dual' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.01
POSSESSOR@pdb1> create or replace function func___definer_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  select 'hello world' into rt from dual;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual_execute_immediate___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6  execute immediate 'select rn from sys.tbl___privilege___' into rt;
  7      return rt;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> create or replace function func___definer_except_dual___ return varchar2
  2  authid definer
  3  as
  4      rt varchar2(30);
  5  begin
  6      select rn into rt
  7      from sys.tbl___privilege___;
  8      return rt;
  9  end;
 10  /

Warning: Function created with compilation errors.

Elapsed: 00:00:00.02
POSSESSOR@pdb1> select authid,object_name from user_procedures order by authid,object_name;

AUTHID       OBJECT_NAME
------------ --------------------------------------------------------------------------------------------------------------------------------
CURRENT_USER FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___
CURRENT_USER FUNC___CURRENT_USER_DUAL___
CURRENT_USER FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___
DEFINER      FUNC___DEFINER_DUAL___
DEFINER      FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___

6 rows selected.

Elapsed: 00:00:00.04

[oracle@f285aba0589a ~]$ sqlplus invoker/test@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 29 19:19:14 2019
Version 18.3.0.0.0

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

Last Successful login time: Fri Mar 29 2019 19:17:42 +09:00

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

INVOKER@pdb1>
INVOKER@pdb1>
INVOKER@pdb1> col rt for a30
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
1

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual;
select possessor.FUNC___CURRENT_USER_EXCEPT_DUAL___ as rt from dual
                 *
ERROR at line 1:
ORA-06575: Package or function FUNC___CURRENT_USER_EXCEPT_DUAL___ is in an invalid state


Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;

RT
------------------------------
 hello world

1 row selected.

Elapsed: 00:00:00.00
INVOKER@pdb1> select possessor.FUNC___DEFINER_DUAL___ as rt from dual;

RT
------------------------------
hello world

1 row selected.

Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual;
select possessor.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___ as rt from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "POSSESSOR.FUNC___DEFINER_EXCEPT_DUAL_EXECUTE_IMMEDIATE___", line 6


Elapsed: 00:00:00.01
INVOKER@pdb1> select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual;

select possessor.FUNC___DEFINER_EXCEPT_DUAL___ as rt from dual
                 *
ERROR at line 1:
ORA-06575: Package or function FUNC___DEFINER_EXCEPT_DUAL___ is in an invalid state


Elapsed: 00:00:00.01

結果

たぶんこんな感じ

考察

パターン3はロール経由権限はコンパイル時無効となるオラクルの仕様の為、コンパイルができない。しかし、コンパイル時に直接権限があれば、コンパイルは可能となり、実行時に直接権限を有していれば実行可能となるため、△。ロール経由権限が今回の場合、存在していないので、実行できなかったが、実行者権限ではロール経由権限が有効になるので、実行できる。よって△。パターン4は直接権限を有していれば、実行できたので、△。パターン7はパターン3とほぼ同じだが、所有者権限の場合、実行時、ロール経由権限が無効となるので、直接権限を与えるほか実行の術はない。パターン8も所有者権限の場合で、実行時、ロール経由権限が無効となるので、直接権限を与えるほか実行の術はない。

まとめ

ハンディなのはパターン2と4かなー。実行者権限にして、即時実行すれば、コンパイルエラーも回避できて、権限もロール管理できるから。実行者権限プログラムであれば、ログイン時に権限チェックが済んでいるので、プログラム実行時に所有者権限プログラムのように毎回権限チェックしなくて済む。パフォーマンスUPするぽい。スカラ値返すだけのファンクションとかも基本は実行者権限でいい気がする。

権限の弱いユーザーが作成した実行者権限ストアドプログラムを権限の強いユーザーが実行すると、実行するユーザーのすべての権限を一時的に権限の弱いユーザーが継承するそうw。ということは弱い権限のユーザーが一時的に最強の権限になるので、その強い権限のまま、もともと弱い権限であったはずのユーザーが実行者権限ストアドプログラムをコールしたりするとよからぬことが起こるリスクが発生する。ということで、INHERIT PRIVILEGES権限なるものができた。これはもともと権限の弱かったユーザーが強い権限になすまして、本来アクセスできないはずのオブジェクトに対して処理をしようとしたときに、権限チェックするもの。実行者権限ストアドプログラムの所有者にこのINHERIT PRIVILEGES権限を付与しておくと、信頼できる人というお墨付きをもらえるみたいな仮札みたいな感じかな。職権乱用はしなさそうだから、権限強いユーザーによって自分の実行者権限プログラムの実行をさせてもいいよね見たいな感覚。自分の強い権限を与える代償に。。とんち効いている。。。

ストアドプログラムを所有しているユーザの権限を持って実行するのが、所有者権限実行プログラム。ストアドプログラムを実行するユーザの権限を持って実行するのが、実行者権限実行プログラム。ストアド作成時はデフォで定義者権限(所有者権限)で実行される。

たとえば、dual表以外のテーブルを参照するような所有者権限ストアドプログラムと実行者権限ストアドプログラムがあったとする。ともにテーブルの値を取得して何かしらの処理をおこなうプログラム。ストアドプログラムを実行するユーザに対して、自スキーマのテーブルへのオブジェクト権限を付与しない限りは、実行者権限ストアドプログラムは実行されることはない。ストアドプログラム実行者へ自分の持ち物であるオブジェクトを不必要に公開されることがない。データセキュリティの側面でのメリット。所有者権限プログラムに関しては、所有者が自スキーマのテーブルに対するオブジェクト権限を保持していれば、プログラムは実行できる。ユーザはストアドプログラムを通してでしか、他人の持ち物であるオブジェクトにアクセスする術がない。入力値が限定さているみたいな感覚かな。干渉されたくない感じが伝わる。所有者がストアドプログラムに記載されているオブジェクトへのオブジェクト権限を保有しているかどうかストアドプログラムが実行されるたびに確認する。所有者権限はパフォーマンスの面でチェック処理時間が懸念される。dual表ならその心配はない。オブジェクト管理されていないから。

メモ

セッションしくったら、これでいい感じにする。

コード表示

select sid, serial#, username, program from v$session where username = 'POSSESSOR';
select sid, serial#, username, program from v$session where username = 'INVOKER';

alter system kill session 'sid, serial#';