Oracle PL/SQL 中のカーソルの読み取り一貫性
PL/SQL の読取り一貫性を確認
script 1 (cursorでselect)
t2の全行を 1秒おきに1行 fetchすることを、3回ループする
[oracle@db01 20220702]$ cat loop_in_cursor.sql set line 200 pages 1000 tab off time on timing on serveroutput on drop table t2 purge; create table t2 (c1 number(10), c2 varchar2(40)); insert into t2 select level, to_char(sysdate , 'yyyy/mm/dd hh24:mi:ss') from dual connect by level <= 10; commit; declare vSQL varchar2(200); vC1 number(19); vC2 varchar2(40); cursor c is select c1, c2 from t2 ; begin for i in 1..3 loop dbms_output.put_line('---- loop ' || to_char(i)); open c; loop fetch c into vC1, vC2; exit when c%notfound; dbms_output.put_line(to_char(vC1) || ', ' || vC2); dbms_session.sleep(1); end loop; close c; end loop; end; / quit;
script 2 (update)
t2 を1行ずつ、1秒間隔で、全行更新するまでループする
[oracle@db01 20220702]$ cat loop_update.sql set line 200 pages 1000 tab off time on timing on serveroutput on begin for i in 1..20 loop update t2 set c2 = to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') where c1 = i; commit; dbms_session.sleep(1); end loop; end; /
結果
script 実行中に、script 2を実行する。 script 1 のloop 1回目は、最初に投入したデータを表示 script 1 のloop 2回目は、script 2 が t2 を途中まで更新したデータを表示 script 1 のloop 3回目は、script 2 が t2 を最後まで表示したデータを表示
→ カーソルがオープンしたタイミングのデータが表示される
- script 1 結果
[oracle@db01 20220702]$ sqlplus inserter/oracle@pdb1 @loop_in_cursor.sql SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 2 06:21:12 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sat Jul 02 2022 06:20:11 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Table dropped. Elapsed: 00:00:00.01 Table created. Elapsed: 00:00:00.01 10 rows created. Elapsed: 00:00:00.00 Commit complete. Elapsed: 00:00:00.00 ---- loop 1 1, 2022/07/02 06:21:12 2, 2022/07/02 06:21:12 3, 2022/07/02 06:21:12 4, 2022/07/02 06:21:12 5, 2022/07/02 06:21:12 6, 2022/07/02 06:21:12 7, 2022/07/02 06:21:12 8, 2022/07/02 06:21:12 9, 2022/07/02 06:21:12 10, 2022/07/02 06:21:12 ---- loop 2 1, 2022/07/02 06:21:16 2, 2022/07/02 06:21:17 3, 2022/07/02 06:21:18 4, 2022/07/02 06:21:19 5, 2022/07/02 06:21:20 6, 2022/07/02 06:21:21 7, 2022/07/02 06:21:22 8, 2022/07/02 06:21:12 9, 2022/07/02 06:21:12 10, 2022/07/02 06:21:12 ---- loop 3 1, 2022/07/02 06:21:16 2, 2022/07/02 06:21:17 3, 2022/07/02 06:21:18 4, 2022/07/02 06:21:19 5, 2022/07/02 06:21:20 6, 2022/07/02 06:21:21 7, 2022/07/02 06:21:22 8, 2022/07/02 06:21:23 9, 2022/07/02 06:21:24 10, 2022/07/02 06:21:25 PL/SQL procedure successfully completed. Elapsed: 00:00:30.74 Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@db01 20220702]$
- script 2 結果
[oracle@db01 20220702]$ sqlplus inserter/oracle@pdb1 @loop_update.sql SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 2 06:21:16 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sat Jul 02 2022 06:21:12 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 PL/SQL procedure successfully completed. Elapsed: 00:00:20.48 06:21:36 SQL>
PL/SQL のエラーハンドリング
エラー発生方法
PL/SQL 実行中に、参照する表を更新することで ORA-1555 を発生させエラーとする
ORA-1555 の再現ケース - tomy_125: Note
EXCEPTION なし
15:18:15 SQL> set serveroutput on 15:21:15 SQL> declare 15:21:15 2 vC1 number(10); 15:21:15 3 vC2 varchar(500); 15:21:15 4 begin 15:21:15 5 select 15:21:15 6 /*+ LEADING(a b) USE_NL(b) */ 15:21:15 7 a.c1, a.c2 15:21:15 8 into vC1, vC2 15:21:15 9 from t1 a, t1 b 15:21:15 10 order by a.c1, b.c1 15:21:15 11 ; 15:21:15 12 end; 15:21:15 13 / declare * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9_3581032004$" too small ORA-06512: at line 5 Elapsed: 00:00:09.24 15:21:26 SQL>
EXCEPTION あり、OTHER でハンドリング
15:21:26 SQL> set serveroutput on 15:22:09 SQL> declare 15:22:09 2 vC1 number(10); 15:22:09 3 vC2 varchar(500); 15:22:09 4 begin 15:22:09 5 select 15:22:09 6 /*+ LEADING(a b) USE_NL(b) */ 15:22:09 7 a.c1, a.c2 15:22:09 8 into vC1, vC2 15:22:09 9 from t1 a, t1 b 15:22:09 10 order by a.c1, b.c1 15:22:09 11 ; 15:22:09 12 exception 15:22:09 13 when others then 15:22:09 14 dbms_output.put_line('-- other error received.'); 15:22:09 15 end; 15:22:09 16 / -- other error received. PL/SQL procedure successfully completed. Elapsed: 00:00:09.67 15:22:19 SQL>
EXCEPTION あり、ユーザー定義例外
15:23:00 SQL> set serveroutput on 15:23:01 SQL> declare 15:23:01 2 vC1 number(10); 15:23:01 3 vC2 varchar(500); 15:23:01 4 snapshot_too_old exception; 15:23:01 5 pragma exception_init(snapshot_too_old, -1555); 15:23:01 6 begin 15:23:01 7 select 15:23:01 8 /*+ LEADING(a b) USE_NL(b) */ 15:23:01 9 a.c1, a.c2 15:23:01 10 into vC1, vC2 15:23:01 11 from t1 a, t1 b 15:23:01 12 order by a.c1, b.c1 15:23:01 13 ; 15:23:01 14 exception 15:23:01 15 when snapshot_too_old then 15:23:01 16 dbms_output.put_line('-- ORA-1555 received.'); 15:23:01 17 when others then 15:23:01 18 dbms_output.put_line('-- other error received.'); 15:23:01 19 end; 15:23:01 20 / -- ORA-1555 received. PL/SQL procedure successfully completed. Elapsed: 00:00:11.77 15:23:13 SQL>
ORA-1555 の再現ケース
事前準備
自動拡張 OFF の10MB のUNDO表領域を予め作成しておく
データ作成
SQL> drop table t1 purge; SQL> create table t1 (c1 number(10), c2 char(500)); SQL> insert into t1 select level, 'AAA' from dual connect by level <=10000; SQL> commit; SQL> select segment_name, bytes/1024/1024 MB from user_segments; SEGMENT_NA MB ---------- ---------- T1 6
1つめのセッションから 時間のかかる select 発行
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ;
select 実行中に2つめのセッションからT1 を更新
SQL> update t1 set c2 = 'XXX'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'YYY'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'ZZZ'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; 10000 rows updated. Elapsed: 00:00:00.74 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .125 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:56 SQL> Commit complete. Elapsed: 00:00:00.00 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 8.6875 EXPIRED .125 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:56 SQL> 10000 rows updated. Elapsed: 00:00:00.87 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .0625 ACTIVE 6.5625 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 8.75 EXPIRED .0625 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:57 SQL> 10000 rows updated. Elapsed: 00:00:00.50 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.4375 EXPIRED .0625 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL>
1つめのセッションにエラーが返る
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ; from t1 a, t1 b * ERROR at line 4: ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2_2715918981$" too small Elapsed: 00:00:13.03 14:57:57 SQL> 14:57:57 SQL>
参考
Atcoderを解く環境のためのDockerfile
Atcoderを解く環境のためのDockerfile。 コードは VS Code の remote container を使って書く。
参考
- Windows 10 上で atcoder-cli を online-judge-tools と連携させて使う：導入からテストと提出までの基本的操作 - はむ吉（のんびり）の練習ノート
- atcoder-cli インストールガイド | わたしろぐ
- そもそもnpmからわからない
- Linux Prerequisites for Visual Studio Code Remote Development
- nvmとは何か？Node.jsのインストールと最新の安定版にアップデートする方法｜バージョン指定と変更（npm）
- リリース一覧 | Node.js
- Node.js Packages for Oracle Linux | Integrated Cloud. Applications & Platform Services.
Dockerコンテナにssh接続
とりあえずメモ。ssh ノンパスを設定したコンテナへの接続。
ホストのssh鍵作成
ssh-keygen
コンテナのホスト鍵作成
ssh-keygen -t rsa -N "" -f ./ssh_host_rsa_key ssh-keygen -t dsa -N "" -f ./ssh_host_dsa_key ssh-keygen -t ecdsa -N "" -f ./ssh_host_ecdsa_key ssh-keygen -t ed25519 -N "" -f ./ssh_host_ed25519_key tar zvcf ssh_host_keys.tar.gz ssh* ls -l ssh*
Dockerfile
FROM oraclelinux:7 RUN yum install -y openssh-server RUN echo "#PermitRootLogin yes" >> /etc/ssh/sshd_config #### root のログインを許可 RUN mkdir -p /root/.ssh/ RUN echo "ssh-rsa ********" >> /root/.ssh/authorized_keys #### ホストの公開鍵 COPY ./ssh_host_keys.tar.gz /etc/ssh/ RUN tar zxf /etc/ssh/ssh_host_keys.tar.gz -C /etc/ssh/ #### 事前にコンテナのホスト鍵を作成しておき展開 CMD /usr/sbin/sshd && tail -f /dev/null
Build
docker build ./ --tag ol7ssh
RUN
docker run -d --rm -p 127.0.0.1:10022:22 ol7ssh
ssh接続
ssh root@127.0.0.1 -p 10022
参考
sshd の常駐方法については、こちらを参考にしました。
Oracle Linux にpyenv をいれるDockerfile
とりあえずメモ。
FROM oraclelinux:7 RUN yum install -y gcc zlib-devel bzip2 bzip2-devel readline readline-devel sqlite sqlite-devel openssl openssl-devel git libffi-devel RUN git clone https://github.com/pyenv/pyenv.git ~/.pyenv RUN echo 'export PYENV_ROOT="$HOME/.pyenv"' >> ~/.bashrc RUN echo 'export PATH="$PYENV_ROOT/bin:$PATH"' >> ~/.bashrc RUN echo 'eval "$(pyenv init --path)"' >> ~/.bashrc RUN echo 'eval "$(pyenv init -)"' >> ~/.bashrc RUN $HOME/.pyenv/bin/pyenv install pypy3.8-7.3.7 RUN $HOME/.pyenv/bin/pyenv global pypy3.8-7.3.7 RUN $HOME/.pyenv/bin/pyenv rehash
Oracle SQLモニターの確認方法
対象SQLの確認
SQL> select sid, sql_id, status, to_char(sql_exec_start, 'yyyy/mm/dd hh24:mi:ss') start_time from v$sql_monitor order by status, sql_exec_start ; SID SQL_ID STATUS START_TIME ---------- ------------- ------------------- ------------------- 150 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 148 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 404 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 393 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 276 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 28 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 30 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 391 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 275 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 152 g3bca740t9wjp EXECUTING 2022/02/16 15:27:18 10 rows selected.
レポートの表示
SQL> set long 10000 line 400 pages 1000 tab off col data for a400 select dbms_sqltune.report_sql_monitor( session_id=> 152, sql_id=> 'g3bca740t9wjp', type=>'TEXT' ) data from dual; DATA ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL Monitoring Report SQL Text ------------------------------ select * from t01, t02 order by 1,2,3 Global Information ------------------------------ Status : EXECUTING Instance ID : 1 Session : USER01 (152:63240) SQL ID : g3bca740t9wjp SQL Execution ID : 16777220 Execution Started : 02/16/2022 15:41:12 First Refresh Time : 02/16/2022 15:41:16 Last Refresh Time : 02/16/2022 15:41:24 Duration : 13s Module/Action : SQL*Plus/- Service : pdb1 Program : sqlplus@db01 (TNS V1-V3) Global Stats ========================================================= | Elapsed | Cpu | IO | Buffer | Write | Write | | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | ========================================================= | 12 | 7.21 | 4.50 | 38 | 3076 | 617MB | ========================================================= SQL Plan Monitoring Details (Plan Hash Value=1870084974) ========================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | | (%) | (# samples) | ========================================================================================================================================================================== | -> 0 | SELECT STATEMENT | | | | 9 | +4 | 1 | 0 | | | . | . | | | | -> 1 | SORT ORDER BY | | 1G | 6M | 11 | +2 | 1 | 0 | 3076 | 617MB | 745KB | 618MB | 90.91 | Cpu (2) | | | | | | | | | | | | | | | | direct path write temp (8) | | -> 2 | MERGE JOIN CARTESIAN | | 1G | 548K | 9 | +4 | 1 | 27M | | | . | . | | | | -> 3 | TABLE ACCESS FULL | T02 | 100K | 69 | 9 | +4 | 1 | 2656 | | | . | . | | | | -> 4 | BUFFER SORT | | 10000 | 6M | 9 | +4 | 2656 | 27M | | | 424KB | . | 9.09 | Cpu (1) | | 5 | TABLE ACCESS FULL | T01 | 10000 | 5 | 1 | +4 | 1 | 10000 | | | . | . | | | ==========================================================================================================================================================================
