本記事は JPOUG Advent Calendar 2013 の１８日目です。１７日目は 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 版ではサブディレクトリは検索してくれないような気がします。
- カレントディレクトリ
SQLPATH環境変数で指定されたディレクトリ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 件のコメント:
コメントを投稿