開発のあいまに

OracleのSYS.AUD$テーブルに残っている監査ログを、DBに負荷をかけずに削除するSQLスクリプト

1000行づつ削除してコミットし、1000行毎に10秒Waitします。

SET SERVEROUTPUT ON

declare
  v_max_row NUMBER;
BEGIN

  select max(rownum) into v_max_row from sys.aud$;

  while v_max_row > 0 loop

    DBMS_OUTPUT.PUT_LINE('残り' || v_max_row || '行');

    delete from sys.aud$ where rownum < 1000;
    commit;

    v_max_row := v_max_row - 1000;

    DBMS_LOCK.SLEEP(10);

  end loop;

end;
/

sqlplusから実行した場合の実行結果サンプル。

F:\work>sqlplus (ID)/(Password)@(ホスト名orIP):1521/(インスタンス名) @delet_aud_table.sql

SQL*Plus: Release 12.1.0.2.0 Production on 金 4月 27 11:55:10 2018

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

最終正常ログイン時間: 金 4月 27 2018 11:49:52 +09:00

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
残り6186行
残り5186行
残り4186行
残り3186行
残り2186行
残り1186行
残り186行

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

更新系SQLを実行したあとcommit文を実行し忘れると事故が起きる

SQLDeveloperやsqlplusで、更新系SQL(insert/update/delete)を実行したあと、commit文を実行しないと、対象レコードをロックしたままになり、他のアプリの処理が全く終わらない事象が起きる。他のデータベース管理アプリはオートコミットしてくれるのが多いので忘れがちになりますが、これを本番環境でやってしまうと、サクラエディタでOracleのログファイルを排他で開き、Oracleが停止するくらい致命的。

ロック状況は下記SQLで確認できる。
select * from v$lock;
select * from v$session;

Oracleインスタンスがコンテナデータベースかどうか確認するSQL

「sys」ユーザ「SYSDBA」権限で接続し、dba_pdbsビューをselectすることで確認できる。
実行結果が0件なら従来型DB、1件でもあればコンテナデータベース。

SELECT * FROM dba_pdbs;

PL-SQL Loopのサンプル

※事前に「SET SERVEROUTPUT ON」を実行しておかないと、DBMS_OUTPUT.PUT_LINE()の結果が表示されない。

SET SERVEROUTPUT ON

declare
  v_count NUMBER;
BEGIN

  v_count := 0;

  LOOP

    v_count := v_count + 1;
    DBMS_OUTPUT.PUT_LINE(v_count || '回');
   
    IF v_count >= 10 THEN
      EXIT;
    END IF;

  END LOOP;
END;
/

基礎からわかる PL/SQL

基礎からわかる PL/SQL が分かり易い。　久々にPL/SQLを扱わないといけなくなった際、PL/SQLの文法、プロシージャ、ファンクション、パッケージの作成をどうやるか、思い出すのに最適な本。

プロシージャのメリットや、SQLのパフォーマンスチューニングについても書かれているので、DB開発経験の浅い人や、SQLは分かるけどPL-SQLが良く分からないという人にもお勧めの本。