【コマンドラインメモ】ORACLE

---

sqlplus [ユーザー]/[パスワード]@[SID]
　sqlplusに接続する

---

sqlplus -s [ユーザー]/[パスワード]@[SID]
sqlplusに接続するがその際のインフォメーションメッセージなどを省略する

sqlplus [ユーザー]/[パスワード]@[SID] as sysdba
sqlplus / as sysdba
sysdba権限で接続する。後者の方はSIDが環境変数ORACLE_SIDに設定されてる前提

sqlplus [ユーザー]/[パスワード]@[SID] @[sqlファイル]
接続と同時に[sqlファイル]に指定したsqlを実行する。
[sqlファイル]の末尾にexitしておけば単発でsql実行するコマンドにできる。
SQLServerのsqlcmdみたいなもんか。

sqlplus [ユーザー]/[パスワード]@[SID] @[sqlファイル] “aaa”
[sqlファイル]に置換変数（’&1’等）がある場合は、
指定した値（この場合"aaa"）で書き換えて実行する
何も指定しないと置換前後の値が標準出力されてしまう。
これはset verify offで出力しないようにできる。

　
sqlplus内部コマンド
　set head off(on)
　select文実行後のカラムヘッダを出力しない
　set linesize 1000
　select実行後の標準出力の折り返し位置を変更する。
　大体1000くらいにしておけば事足りる。
　set pagesize 0
　select実行後のページ切り替わりを防止。
　set feedback off
　select実行後の結果件数出力をなくす。
　set verify off
　置換変数の置換前後（旧：○○　新：△△みたいなの）を出さなくする。
　set serveroutput on
　標準出力するかどうかを指定。確かデフォルトはoff。
　spool [ファイル名]
　sqlplusの実行操作結果を指定したファイル名に出力する。
　でもファイルに吐くときは毎回リダイレクトしてる。その程度のもん。
　spoolをやめたいときはspool off
　variable [変数名] [型]
　変数定義。
　たとえば文字列定義したいときはvariable a NVARCHAR2で
　NVARCHAR2という型の文字列を定義できる。
　引数有のプロシージャ呼び出すときなどに事前に実行する。
　　variable a NVARCHAR2;
　　exec PROC_TEST(:a);
　等。ここで定義した変数を使用する場合先頭にコロン（:）をつける。

exp [ユーザー]/[パスワード]@[SID] file=[出力ファイル名] log=[実行ログファイル名] tables=[テーブル名] grants=[y|n]
exp prafile=[パラメータファイル名]
DBに接続してテーブルデータをDMPで出力する。
　file=[出力ファイル名]
　出力するDMPファイル名を指定する。パス指定しないとカレントに吐かれる。
　DMPといってるが別に拡張子はなんでもいい（究極なくても良いと思う）
　慣例的に「DMP」にしてるのでDMPにしてる。
　log=[実行ログファイル名]
　logは指定しないと「export.log」とかになった気がする。必須ではない。
　tables=[テーブル名]
　複数のテーブルを取得したいときはtablesにカンマ区切りで指定。
　grants=[y|n]
　grantsはテーブルに付与された権限情報（selectやdelete等）も一緒に出力するかどうか。
　デフォルトはy（yes）。
　prafile=[パラメータファイル名]
　↑のあたりの実行オプションをいちいち全部指定するのが面倒な場合は
　パラメータファイルにしてparfileで指定してやる方が便利。
　parfileはjavaのpropertiesファイルみたいに書けばよい。

　詳細なオオプションはexp -helpで出すことが出来るが
　実際使ってるのは上に挙げた程度のものだった。
　しかし11g以降はexpdpの方が推奨されているという話も聞く
　（個人的にはこっちのほうが使いやすいんだが）。

imp [ユーザー]/[パスワード]@[SID] file=[入力ファイル名] log=[実行ログファイル名] tables=[テーブル名] ignore=[y|n]
imp parfile=[パラメータファイル名]
DBに接続してテーブルDMPファイルをIMPORTする。大量データのINSERTに使用。
　file=[入力ファイル名]
　log=[実行ログファイル名]
　fileとlogはexpと同じ。
　tables=[テーブル名]
　元DMPファイルに複数のテーブルデータが含まれている場合はtablesで必要な分を指定する。
　未指定なら全テーブル。
　ignore=[y|n>]
　登録先のテーブルに既存データがある場合、
　キー重複（一意制約違反）などでエラーが発生することがある。
　その場合ignore=yをつけていないとそこで処理が止まる。
　これはデフォルトでn（NO）なので意図的に指定しないとエラーを無視してくれない。
　不安なら一度登録先をtruncateしてからのほうがいい。

　これも11g以降はimpdpのほうが推奨されているらしい。
　ただ個人的にはデータの登録にはimpもimpdpも使わずsqlldrのほうをよく使う。
　ものすごい大量データならそうはいかないのだろうが…

expdp [ユーザー]/[パスワード]@[SID] file=[出力ファイル名] directory=[ディレクトリオブジェクト名] log_file=[ログファイル名]
expとは別のファイル出力コマンド。
　file=[出力ファイル名]
　出力するダンプファイル名を指定。
　ここではファイルだけでいい。出力先パスはdirectoryで指定する。
　directory=[ディレクトリオブジェクト名]
　出力先のパスをORACLEのディレクトリオブジェクトで指定する。
　/home/user/aaa/みたいなパスを指定するんではなく、
　そのパス情報を保持したORACLEのオブジェクトを事前に作成しておいてそいつを指定する。
　（これが面倒だから個人的には使いづらい。）
　　create or replace directory aaa as ‘/home/user/aaa/’;
　　grants write on aaa to test;　– ←こっちはsysdbaとかでやらないとだめかも
　みたいなかんじでsqlplus等から事前に作成しておく。
　後者は権限付与。write（書込み）権限がないと出力できずに失敗する。
　log_file=[ログファイル名]
　ログファイル名。やはりディレクトリオブジェクト配下につくられる。
　parfile=[パラメータファイル名]
　↑はやはりparfileで指定できる。

　詳細なオプションはexpdp -helpで表示できるがやはり使ってるのは上に挙げた程度。
　たまにestimate（出力にかかる時間を推定）を使うくらい。

impdp [ユーザー]/[パスワード]@[SID]
impとは別のファイル登録コマンド。
　file=[出力ファイル名]
　directory=[ディレクトリオブジェクト名]
　log_file=[ログファイル名]
　parfile=[パラメータファイル名]
　expdpと同じ。
　ただしディレクトリオブジェクトにはread権限がないとだめだと思われる。
　　grants read on aaa to test;　– ←こっちはsysdbaとかでやらないとだめかも
　remap_schema=aaa:bbb
　出力元（expdp元）と登録先（impdp先）でスキーマ名が異なる場合、
　これを指定してやらないとエラーになる。
　この指定はスキーマaaaから出力したDMPはスキーマbbbにいれてくれ、というもの。
　remap_table=aaa.ccc:bbb.ddd
　こっちはテーブル名。
　この指定はスキーマaaaから出力したcccというテーブルDMPは
　スキーマbbbのdddというテーブルにいれてくれ、というもの。

　テーブル名が違うのはそうないだろうけど、
　開発環境と本番環境でスキーマが違くてデータ移送するときにエラーが発生、
　というケースでremap_schemaが必須になったりする。実際これで何度か悩まされた。
　詳細なオプションはimpdp -helpで出てくる。
　やはり上に挙げた程度しか使わない。こっちもたまにestimate使うくらいかな

sqlldr [ユーザー名]/[パスワード]@[SID] control=[コントロールファイル名]
テキストデータ（CSVやTSV）をテーブルに一括登録する。
細かいオプションは指定できるが面倒なのでコントロールファイルに一括記述するほうが楽。
↓の順に記述する。
　OPTIONS(ROWS=-1,errors=-1,skip=1,bindsize=10485760,readsize=10485760)
　ROWSはCOMMIT単位。-1だと一括。ただし後述するbindsizeとかのほうが優先される。
　errorsはエラー閾値。-1だと完全無視。
　bindsize、readsizeは取り込むデータを一度メモリにためこむサイズ。
　デフォルトがめちゃくちゃ小さいのでROWSを10000とかに指定しても
　元データの具合によっては2行や3行ですぐCOMMITかかってしまい効率悪い。
　指定はバイト。この指定値は10[MB]。
　bindisizeとreadsizeの違いは忘れた…
　LOAD DATA
　なんだか知らないがつけないとダメな一文
　INFILE ‘./aaa/testdata.txt’
　入力ファイルを指定する。
　なお、デフォルトの改行コードはシステム依存。
　たとえばWindows上で落として（CRLF）UNIX上で読み込む（LF）場合、
　改行コードを指定しないと一文とみなされてしまいエラーになる。
　改行コードの指定(CRLFの場合)は
　　INFILE ‘./aaa/testdata.txt’ “str ‘\r\n’"　とする。
　BADFILE ‘./aaa/bad/testdata.bad’
　取り込めなかった（登録時エラーになった）データを書き出すファイル。
　省略できる。その場合入力ファイルと同階層に拡張子badで出来上がる。
　エラーがなければ作られない。
　APPEND
　INSERTする指示。
　INTO TABLE [テーブル名]
　登録先のテーブル名
　FIELDS TERMINATED BY X'09’
　入力データの項目区切り位置を指定する。
　この指定はTAB（元データがTSVであることを意味する）。
　TABみたいな特殊な制御文字だとこういう指定の仕方をしないといけないが
　CSV（カンマ）ならFIELDS TERMINATED BY ‘,‘で良い。
　ただ項目内にカンマがあるようなケースではこれだとうまくいかないので
　個人的にはよくTABを使ってる。
　※これは項目にカンマを持つよりTABを持つ方が少ないという
　　担当システムのデータ内容に依存するものであって、実際は使い分けが必要
　TRAILING NULLCOLS
　入力データのNULL値を許容する。
　(
　 カラム１
　 ,カラム２
　 …
　)
　入力データと登録先のテーブルのカラム名をマッピングする。
　なおDATEやTIMESTAMP型は入力データのフォーマットをもとに
　下記のような指定をしないと失敗する。
　　TESTDATE “TO_DATE(:HYOJUNSOHINDT,‘yyyy/MM/dd HH24:mi:ss’)"
　　TESTTIMESTAMP “TO_TIMESTAMP(:KOSINTD,‘yyyy/MM/dd HH24:mi:ss.FF3’)"
　なのでDATEやTIMESTAMPをもつ入力データは、
　作成時にきちんとフォーマットを意識して指定する必要がある。

---