2013/12/18

Oracle Databaseを扱う上で知っておきたい変数10選

本記事は JPOUG Advent Calendar 2013 の18日目です。17日目は mutatsu さんの 備忘録とか 12cのPL/SQL新機能:暗黙的結果セット(dbms_sql.return_result) でした。

ここでは Oracle Database を扱う上で設定しておくと便利だと思う変数を 10 個ほど紹介したいと思います。

1. SQLPATH 環境変数 - SQL*Plus

まずは DBA 1.0 の相棒 SQL*Plus を使う上で欠かせない SQLPATH 環境変数。

sqlplus から実行する SQL スクリプトの場所を指定する値です。マニュアルによると次の順番で SQL スクリプトを検索するとありますが、手元の Linux 版ではサブディレクトリは検索してくれないような気がします。

  1. カレントディレクトリ
  2. SQLPATH 環境変数で指定されたディレクトリ
  3. SQLPATH 環境変数で指定されたディレクトリのサブディレクトリ

PATH 環境変数と同じように : で区切った複数の場所を指定できますので、様々な環境で使う場合を想定して複数設定しておくのも便利かも知れません。その場合は意図しない場所にある同じ名前のスクリプトを呼び出してしまう可能性もありますので、何でもかんでも記述しておく場合は注意が必要とも言えます。

私はシェルのプロンプト変数 PS1 のように接続先に関する情報を SQLPROMPT に設定するようにした次のような login.sql を用意していますので、任意の場所に置いたそれを読み込ませるために使っています。

2. TNS_ADMIN 環境変数 - SQL*Plus

次は Oracle Net を使う上で欠かせない TNS_ADMIN 環境変数。

Oracle Database に接続する際の接続記述子を tnsnames.ora ファイルに記述しますが、そのファイルが存在するディレクトリを設定する値です。デフォルトでは $ORACLE_HOME/network/admin ですが、任意の場所をこの値に指定することで条件に応じた使い分けができます。

接続クライアントとして Instant Client を使う場合には $ORACLE_HOME の規定値らしきものがないので、よく自分の好きなところに設定します。個人的に最近はローカル・ネーミング・メソッドではなく簡易接続ネーミング・メソッドで手早く済ますことの方が多いです。

3. TWO_TASK 環境変数 - SQL*Plus

次は Oracle 接続文字列を指定する TWO_TASK 環境変数。

ここに接続記述子を設定しておくとデータベースを指定しない接続の場合にその値が使用されます。マニュアルのサンプルには次のようにあり、

$ TWO_TASK=ORCL; export TWO_TASK
$ sqlplus scott
これは以下と同じものとあります。
$ sqlplus scott@ORCL
この例だけだとイマイチ使いどころが分かりませんが、perl の DBD::Oracle モジュールのテストを通すときなど、ミドルウェアでの接続先データベースを指定する際に使うときがあります。Windows 環境だと LOCAL 環境変数を使うらしいです。

4. NLS_DATE_FORMAT 環境変数 - RMAN

次は TO_CHAR 関数や TO_DATE 関数などの日付書式を設定する NLS_DATE_FORMAT 環境変数。

この手の日付書式は sqlplus セッションの冒頭でよく

SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
などとしているファイルをよく見かけますが、処理の中で日付書式が重要な意味を持つ場合以外は環境変数に設定しておけばいいのになと思うことがよくあります。

それよりもむしろこの変数が役立つのは RMAN による操作を行うときです。

なぜか RMAN では DD-MM-YY という日付書式がデフォルトで使われます。これではバックアップファイルのタイムスタンプや処理開始時間、処理終了時間を時刻レベルまで把握できませんので、この環境変数は sqlplus を使うときと言うよりは RMAN を使うときには必須かなと思っています。

以下はデフォルトの例。

% rman target /
RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        18-AUG-13       1       1       NO         TAG20130818T032431

RMAN> backup spfile;

Starting backup at 21-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-DEC-13
channel ORA_DISK_1: finished piece 1 at 21-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_12_21/o1_mf_nnsnf_TAG20131221T150611_9cbcyn50_.bkp tag=TAG20131221T150611 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-13

以下は設定したときの例。

% export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
% rman target /
RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1       B  F  A DISK        2013/08/18 03:24:31 1       1       NO         TAG20130818T032431

RMAN> backup spfile;

Starting backup at 2013/12/21 15:11:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2013/12/21 15:11:09
channel ORA_DISK_1: finished piece 1 at 2013/12/21 15:11:10
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_12_21/o1_mf_nnsnf_TAG20131221T151109_9cbd7xs8_.bkp tag=TAG20131221T151109 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013/12/21 15:11:10

しばちょう先生も "意外と知られていないワザ" としてオススメされています。

5. EDITOR 環境変数 - SQL*Plus

次はみんな気になる EDITOR 環境変数。

sqlplus の edit コマンドでバッファを編集する際にデフォルトで使用されるテキストエディタは UNIX 系だとラインエディタ(ed)、Windows 系だとメモ帳になりますが、お気に入りのエディタを EDITOR 環境変数として宣言しておくことで作業が捗ります。

ちなみに私は Oracle Database とは関係なしにこの環境変数には vim を指定します。

6. _EDITOR 事前定義変数 - SQL*Plus

次は SQL*Plus の _EDITOR 事前定義変数。

これも先の EDITOR 環境変数と同じように edit コマンドでバッファを編集する際のエディタを指定します。

使いどころとしては、現場で急な対応をしなければいけないときとか、EDITOR 環境変数をいじる暇も無く sqlplus で作業を始めていて、バッファを編集したくなったけど ed はちょっとなぁ、というときにすかさず

SQL> define _editor=vim
SQL> ed
とすれば豊富なシンタックスハイライトをもつ素敵なフルスクリーンエディタが立ち上がって作業が捗ります。

ちなみに _EDITOR 事前定義変数は EDITOR 環境変数が初期値として設定されますが、login.sql で指定している場合はそちらが使われます。

% cat login.sql
define _editor=vim
% EDITOR=emacs; export EDITOR
% sqlplus /nolog
SQL> define _editor
DEFINE _EDITOR         = "vim" (CHAR)

7. TAB システム変数 - SQL*Plus

次は SQL*Plus の SET システム変数 TAB

sqlplus の出力をファイルに spool した後に眺めてみると、画面上では空白だと思っていたものが実は TAB 文字でした残念!という事が嫌なときには OFF に設定します。私は固定長が好きなので大抵 OFF にしています。

8. TRIMSPOOL システム変数 - SQL*Plus

次は SQL*Plus の SET システム変数 TRIMSPOOL

sqlplus の出力はデフォルトでは LINESIZE の値が 80 であるため、幾重にも折り返された列が表示されて切ない思いをすることが多く、その経験から予防線として 9999 とか大きな値にすることがあると思います。

ですがそのような大きな値を設定しているときに spool でファイル出力する際には注意が必要です。SQL*Plus はレポーティングツールでもあるため、列幅に合わせた固定長の空白も出力します。そのため表示される文字数が 10 でも桁が 9999 になるまで空白でパディングされますので、もの凄く大きな出力ファイルができあがっていることがたまにあります。

この TRIMSPOOL の値を ON にしておくとストレージやネットワーク帯域に優しくなれると思います。このあたりは "SQL*Plusスクリプトのチューニング" の項目に参考になるものがあります。

9. LONG システム変数 - SQL*Plus

次は SQL*Plus の SET システム変数 LONG

XML 文書などの大きな文字列は CLOB 型や XMLType 型などに格納するケースも多いですが、sqlplus からそれらの値を表示しようとすると一部しか表示されず悲しい思いをするときがあります。そのようなときはこの LONG の値をメモリが許す限りの大きな値に設定しておきます。といっても最大値は 2GB (2 000 000 000)のようです。

SQL> col schema for a80
SQL> select schema from all_xml_schemas where schema_url = 'http://www.w3.org/2001/xml.xsd';

SCHEMA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<xs:schema targetNamespace="http://www.w3.org/XML/1998/nam


SQL> set long 99999
SQL> /

SCHEMA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<xs:schema targetNamespace="http://www.w3.org/XML/1998/namespace" xmlns:xs="http
://www.w3.org/2001/XMLSchema" xml:lang="en" xmlns:oraxdb="http://xmlns.oracle.co
m/xdb" oraxdb:storeVarrayAsTable="true" oraxdb:flags="2105655" oraxdb:schemaURL=
"http://www.w3.org/2001/xml.xsd" oraxdb:schemaOwner="XDB" oraxdb:numProps="6">
  <xs:annotation>
    <xs:documentation>
          See http://www.w3.org/XML/1998/namespace.html and
          http://www.w3.org/TR/REC-xml for information about this namespace.
      </xs:documentation>
(省略)

10. NUMFORMAT システム変数 - SQL*Plus

最後は SQL*Plus の SET システム変数 NUMFORMAT

sqlplus で SELECT 実行結果の数値をカンマ区切りにしたいときは、特定の列について COLUMN 句で FORMAT を指定することが多いですが、カジュアルに検索したい出力についてまで個別に列を指定するのは面倒です。そのようなときには NUMFORMAT で数値のデフォルト書式を指定すると捗ります。

SQL> set lines 9999 trims on tab off
SQL> col name for a50
SQL> select name, blocks, bytes from v$datafile;

NAME                                                   BLOCKS      BYTES
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf              101120  828375040
/u01/app/oracle/oradata/orcl/sysaux01.dbf              102400  838860800
/u01/app/oracle/oradata/orcl/undotbs01.dbf              29440  241172480
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf       33280  272629760
/u01/app/oracle/oradata/orcl/users01.dbf                  640    5242880
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf       81920  671088640
/u01/app/oracle/oradata/orcl/pdb1/system01.dbf          34560  283115520
/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf          87040  713031680
/u01/app/oracle/oradata/orcl/pdb1/pdb1_users01.dbf        640    5242880

9 rows selected.

SQL> set numf 999,999,999,999
SQL> /

NAME                                                         BLOCKS            BYTES
-------------------------------------------------- ---------------- ----------------
/u01/app/oracle/oradata/orcl/system01.dbf                   101,120      828,375,040
/u01/app/oracle/oradata/orcl/sysaux01.dbf                   102,400      838,860,800
/u01/app/oracle/oradata/orcl/undotbs01.dbf                   29,440      241,172,480
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf            33,280      272,629,760
/u01/app/oracle/oradata/orcl/users01.dbf                        640        5,242,880
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf            81,920      671,088,640
/u01/app/oracle/oradata/orcl/pdb1/system01.dbf               34,560      283,115,520
/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf               87,040      713,031,680
/u01/app/oracle/oradata/orcl/pdb1/pdb1_users01.dbf              640        5,242,880

9 rows selected.

おまけ

そういえば sqlplus で SQL(バッファにあるコード)を再実行するときの R とか / は非常によく使います。

ついでに言うと EXECUTE コマンドは SQL バッファには記録されないので、次のようにバインド変数を EXECUTE で設定して RUN でバインド変数を持つ SQL を(再)実行する、とすると捗る場合もあります。

SQL> var sqlid varchar2(13)
SQL> exec :sqlid := 'hogehoge13'

PL/SQL procedure successfully completed。

SQL> select * from table(dbms_xplan.display_cursor(:sqlid));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  hogehoge13, child number 0
-------------------------------------
...

SQL> exec :sqlid := 'fugafuga39'

PL/SQL procedure successfully completed。

SQL> r
  1* select * from table(dbms_xplan.display_cursor(:sqlid))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fugafuga39, child number 0
-------------------------------------
...

おわりに

変数 10 選といっておいて後半は SET システム変数で水増しした感がありますが、私にとってはどれも日々の作業に欠かせないものです。ここで紹介した変数が何らかのヒントになれば幸いです。

明日は yoheia さんです。楽しみですね。

0 件のコメント:

コメントを投稿