SQLコマンド集

SQL関連のメモ

ファイル名に日付を付けてspoolするSQL

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS';
 
col spool_file_name new_value spool_file_name for a100
select 'ファイル名_'|| to_char(sysdate,'YYYY-MM-DD_HH24MISS') || '.csv' spool_file_name from dual;
spool &spool_file_name
--spoolするSQLを実行
spool off

複数表のDBMS_STATSプロシージャをまとめて作成する

set pages 0
select 'BEGIN 
 DBMS_STATS.GATHER_TABLE_STATS (
    ownname           => ''スキーマ名'', 
    tabname           => ''' || table_name || ''', 
    --以下の条件は自由に設定
    estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE, 
    block_sample      => TRUE,
    method_opt        => ''FOR ALL COLUMNS SIZE AUTO'', 
    degree            => 0,
    no_invalidate     => DBMS_STATS.AUTO_INVALIDATE, 
    force             => TRUE
  )' || chr(59) ||
'
END' || chr(59) ||
'
/' from all_tables where ...--プロシージャを作成する表の条件;

シングルクォートを使用するにはシングルクォートを二つ打つ('')かchr(27)を使用する。
セミコロンを使用するにはchr(59)を使用する。
ちなみにmethod_optで複数列を指定するときにシングルクォート内で改行するとORA-20001が発生する。

FOR LOOPをつかってサンプル表にデータを挿入する

create table test1(c1 number, c2 number, c3 number);

DECLARE
 v_result number :=0;
BEGIN
  FOR v_count IN 1..1000 LOOP
    DBMS_OUTPUT.PUT_LINE(v_count);
    v_result := v_result + v_count;
    insert into test1(c1,c2,c3) values(v_result,v_count,v_result-v_count);
  END LOOP;
END;
/

シェルスクリプト内でsqlplusを起動する

sqlplus -s /nolog << EOF >/dev/null 
conn ユーザー/パスワード
--コード
exit
EOF

文字化けするときの対応
⇒.bash_profile内のLANGにあわせて.oraenv内でNLS_LANGをexportすると楽
例：LANG=”ja_JP.UTF-8”の場合
```
$ vi .oraenv
...
export NLS_LANG=Japanese_Japan.AL32UTF8
```
バイナリ形式で引数を出力。以下の例では16進数と文字コードを表示する
```
select dump('引数',1016)
```
OPTIMIZER_MAX_PERMUTATIONS
- 10g以降は2000で固定
- 7! = 5000なので、7表以上は結合しないほうがベター

バインド変数の定義

variable x number
execute :x := 2

--ちなみにexecute :x := 2は内部的に以下と同じ
BEGIN
 :x := 2;
END;
/

パーティションごとのサイズ(GB)を出力する

set pages 100
column segment_name format a15
column partition_name format a25

select segment_name,partition_name,bytes/1024/1024/1024 GB from user_segments
where segment_name = '表名'
order by segment_name,partition_name;