ああああ

DBとかOSとか仮想マシンとか。

SQLコマンド集

SQL関連のメモ
  1. ファイル名に日付を付けて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
  2. 複数表の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が発生する。
  3. 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;
    /
  4. シェルスクリプト内でsqlplusを起動する
    sqlplus -s /nolog << EOF >/dev/null 
    conn ユーザー/パスワード
    --コード
    exit
    EOF
  5. 文字化けするときの対応
    ⇒.bash_profile内のLANGにあわせて.oraenv内でNLS_LANGをexportすると楽
    例:LANG=”ja_JP.UTF-8”の場合
    $ vi .oraenv
    ...
    export NLS_LANG=Japanese_Japan.AL32UTF8
  6. バイナリ形式で引数を出力。以下の例では16進数と文字コードを表示する
    select dump('引数',1016)
  7. OPTIMIZER_MAX_PERMUTATIONS
    • 10g以降は2000で固定
    • 7! = 5000なので、7表以上は結合しないほうがベター
  8. バインド変数の定義
    variable x number
    execute :x := 2
    
    --ちなみにexecute :x := 2は内部的に以下と同じ
    BEGIN
     :x := 2;
    END;
    /
  9. パーティションごとのサイズ(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;