Un Known…(跡地) 技術メモ

バッチファイルからSQLPlusを起動しデータ取得を行います。
主にエビデンス取得で利用します。
テスト対象の実施前後で取得し、結果ファイルを比較します。

• 起動バッチ

  @echo off
  set DATE_TIME=%date:~0, 4%%date:~5, 2%%date:~2, 2%%time:~0, 2%%time:~3, 2%%time:~6, 2%
  
  set IP_ADDRESS=192.168.XX.XX
  set PORT_NO=1521
  set SID=XXX
  set USER_ID=XXX
  set PASSWORD=XXX
  set SQL_FILE=outputCsvAllData.sql
  set SPOOL_FILE=select_AllData.csv
  
  rem -s サイレントモード
  rem    プロンプトやログインメッセージを表示しなくなる。
  rem -L 1度だけログイン
  rem    ログインに失敗すると3回まで再試行するが、1回で終了する。
  sqlplus -s -L %USER_ID%/%PASSWORD%@%IP_ADDRESS%:%PORT_NO%/%SID% @%SQL_FILE%
  
  move %SPOOL_FILE% %DATE_TIME%_%SPOOL_FILE%
  
  echo %DATE_TIME%_%SPOOL_FILE%を出力しました。
  pause
  

• SQLファイル(outputCsvAllData.sql)

  set echo off
  set linesize 9999
  set pagesize 0
  set trimspool on
  set feedback off
  
  spool select_AllData.csv
  @@select_AllData.sql
  spool off
  

「@@」はパス名を含まないファイル名の場合、「呼び出し元のスクリプトのパス」を補完する。

例)
以下のフォルダ構成の場合、
「@@」をつけると呼び出せるが、「@」の場合、エラーとなる。
・C:/起動バッチ
・C:/test/outputCsvAllData.sql
・C:/test/select_AllData.sql

• SQLファイル(select_AllData.sql)

  prompt テーブル名
  SELECT 項目1 || 't' || 項目2 || 't' || 項目3 FROM テーブル名;
  

文字列結合している理由は「【Oracle】SQLPlusで空白除去」を参照。

データファイルとコントロールファイルを利用して
Oracleにデータをインポートします。

• バッチファイル

@echo off
REM == バッチファイル起動ディレクトリ取得 ==
SET BAT_PASS=%~dp0

REM == データファイルパス設定 ==
SET DATA_PASS=%BAT_PASS%\data\

REM == コントロールファイルパス設定 ==
SET CTL_PASS=%BAT_PASS%\ctl\

REM == データファイル名 ==
SET CSVEXT=.csv
SET CTLEXT=.ctl
SET LOGEXT=.log
SET DATA_ANKEN_KANRI=t_anken_kanri

echo ==============================
echo 案件管理 データファイル取込
echo ==============================
sqlldr USERID='TESTUSR/PASSWORD@サービス名' CONTROL='%CTL_PASS%%DATA_ANKEN_KANRI%%CTLEXT%' DATA='%DATA_PASS%%DATA_ANKEN_KANRI%%CSVEXT%' LOG='%DATA_PASS%%DATA_ANKEN_KANRI%%LOGEXT%'

pause

• コントロールファイル

OPTIONS (ERRORS=0, ROWS=10000, DIRECT=TRUE)
LOAD DATA
INFILE './t_anken_kanri*.csv' "str '\r\n'"
APPEND
INTO TABLE "TESTUSR"."T_ANKEN_KANRI"
FILEDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
DATE FORMAT "YYYY/MM/DD hh24:mi:ss"
TRAILING NULLCOLS
(
    項目名１
    項目名２
    項目名３
)

• データファイル

"aaaa","bbbb","cccc"

統計情報に関するSQLのメモです。

• テーブルの統計情報を取得

  EXEC DBMS_STATS.GATHER_TABLE_STATS('スキーマ名', 'テーブル名');
  

• インデックスの統計情報を取得

  EXEC DBMS_STATS.GATHER_INDEX_STATS('スキーマ名', 'インデックス名');
  

• テーブルの統計情報の状態確認

  SELECT UT.TABLE_NAME, UT.NUM_ROWS, UT.BLOCKS, UT.AVG_ROW_LEN, UT.SAMPLE_SIZE, UT.LAST_ANALYZED FROM USER_TABLES UT WHERE UT.TABLE_NAME NOT IN ('CHAINED_ROWS', 'CREATE$JAVA$LOB$TABLE', 'EXCEPTIONS', 'PLAN_TABLE', 'PROF$PLAN_TABLE') AND UT.TABLE_NAME NOT LIKE 'BIN$%' ORDER BY UT.TABLE_NAME; 
  

• インデックスの統計情報の状態確認

  SELECT UI.TABLE_NAME, UI.INDEX_NAME, UI.NUM_ROWS, UI.SAMPLE_SIZE, UI.LAST_ANALYZED, UI.STATUS FROM USER_INDEXES UI WHERE UI.TABLE_NAME NOT IN ('CHAINED_ROWS', 'CREATE$JAVA$LOB$TABLE', 'EXCEPTIONS', 'PLAN_TABLE', 'PROF$PLAN_TABLE') AND UI.TABLE_NAME NOT LIKE 'BIN$%' ORDER BY UI.TABLE_NAME, UI.INDEX_NAME;
  

VMwareにOracle11gをインストールしているのですが、
久しぶりにsqlplusにログインしたところ以下のエラーが発生しました。

ERROR:
ORA-28002: the password will expire within 7 days

Oracle10gまではパスワードは無期限でしたが、
Oracle11gからはパスワードの有効期限があるようです。
パスワードの期限を無期限に変更し、
パスワードを再設定します。

sqlplusに管理者権限を持つユーザかsysユーザでログインします。

sqlplus sys/管理者パスワード as sysdba

以下のSQLを実行します。

alter profile default limit password_life_time unlimited;
alter user ユーザ名 identified by "パスワード";

パスワードはダブルクォーテーションで括らないとエラーになります。
記号の区別がつかないためと思われます。