権限付与・シノニム
--権限付与--
ロール付与
CONNECT データベースへの接続や表などの作成をする権限
RESOURCE 表領域への無制限アクセスやプロシージャ、トリガーなどの作成をする権限
DBA データベースに対するすべてのシステム権限
GRANT CONNECT,RESOURCE TO XXXUSER;
オブジェクト権限付与
SELECT,INSERT,UPDATE,DETELEなど
GRANT SELECT ON TABLE_NAME TO XXXUSER
--シノニム作成--
CREATE PUBLIC SYNONYM TBL_A FOR USER.TBL_A;
CURSOR
カーソルの大体の流れ。
DECLARE
cursor c1 is select ...
cnt number :=0;
BEGIN
FOR rec1 IN c1 LOOP
update table
set xx = '1'
where xx = rec1.xx;
cnt := cnt + 1;
if mod(cnt,100000) = 0 then
commit;
dbms_output.put_line(cnt || '件更新しました');
end if;
END LOOP;
commit;
dbms_output.put_line(cnt || '件更新しました');
END;
/
SQL*Loader
SQL*Loaderはこんな感じで書くと良い
LOAD DATA
INFILE 'ファイル名1'
INFILE 'ファイル名2'
INFILE 'ファイル名3'
（INSERT｜APPEND｜REPLACE｜TRUNCATE）INTO TABLE テーブル名
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
TRAILING NULLCOLS
(項目名1 "NVL(:項目名1,0)",
CREATE_DATE "NVL(:CREATE_DATE,SYSDATE)",
UPDATE_DATE "NVL(:UPDATE_DATE,SYSDATE)",
項目名4,項目名5)
---------
INFILE
・複数指定可能
・相対パスも可
INSERT
・テーブルが空である必要あり
APPEND
・テーブルにデータを追加する場合
REPLACE
・DELETEしてデータ投入
TRUNCATE
・TRUNCATEしてデータ投入。権限必要。
FIELDS TERMINATED BY
・ ',' → カンマ区切り
・ X'09' → タブ区切り
ENCLOSED BY
・ '"' → "aaaa","bb","ccc"
・'(' AND ')' → (a data string)
TRAILING NULLCOLS
・値の無い項目はNULLを格納
その他
・NOTNULL項目にNULLが入ってしまわないようにNVL関数使用
SQL*Loader
こんな感じで
コントロールファイル xx.ctl
LOAD DATA INFILE 'c:\..................\xxx.csv'(パス指定)
INTO TABLE TBL_KABU_PREF_MST（テーブル名指定）
FIELDS TERMINATED BY X'09'（タブ区切り カンマ区切りの場合は「','」）
(カラム1,カラム2,・・・・・)
コマンドプロンプトから sqlldr user名/パス@識別子 control="C:\・・・\xx.ctl" かな
glogin
GLOGIN.SQL絡み。SQLPLUSにインスタンス名表示される。
\ora92\sqlplus\admin\glogin.sql
set term off
column name new_value name1
select rtrim(instance_name) name from v$instance;
set sqlprompt '&name1>'
set term on
ftpによるファイル取得
ftpによるファイル取得DOSコマンドより
ftp -s:sample.txt>>sample.log
sample.txt -----------
open 接続アドレス
USERNAME
PASSWORD
asc
cd /folderA/folderB
get xxxxxxx.txt
disconnect
bye
---------------------
プロキシサーバやFirewall経由の場合は
ftp [ftpプロキシやFirewallのアドレス]
User USERNAME@接続アドレス
Password [任意のパスワード]
export/import
■■EXPORT/IMPORT
EXPORT/IMPORTはOracleデータベース間のデータの移動や、バックアップに利用されるユーティリティ。
相互に補足しあっており、 EXPORTユーティリティは、Oracleデータベースのデータを、オペレーティング・システムのファイルに書き込み、IMPORTユーテリティは、EXPORTファイルを読み、Oracleデータベースに書き戻す。
EXPORT/IMPORTユーテリティは、一般的に、次のような用途に利用される。
Oracleデータベース間で、データを移動する。
異なるバージョン間やリリース間で、データを移動する。
領域の断片化を解消する。
データのバックアップをとる。
■EXPORT
EXPORTユーティリティは、Oracle Enterprise Manager を使用することができるが、一般的にはEXPコマンドを使用する。
■EXPORT 代表的なパラメータ
・USERID エクスポート処理を実行するユーザー名
/ の後に、パスワードも記述する。
・BUFFER データを取り出すために使用するバッファのバイト数
・FILE エクスポート・ユーティリティで作成するファイル名
デフォルト：EXPDAT.DMP
・GRANTS 権限をエクスポートするかどうか（ Y / N )
デフォルト：Y
・INDEXES 索引をエクスポートするかどうか（ Y / N )
デフォルト：Y
・ROWS データ行をエクスポートするかどうか（ Y / N )
デフォルト：Y
・CONSTRAINTS 制約をエクスポートするかどうか（ Y / N )
デフォルト：Y
・COMPRESS インポート処理中に、データを 1エクステントにまとめるかどうか（ Y / N )
デフォルト：Y
・DIRECT ダイレクトパスエクスポートをするかどうか（ Y / N )
デフォルト：N
データを直接エクスポートクライアントに転送するため、従来型よりも高速
■EXPORTの選択モード
・表モード 指定した表だけエクスポートする。
パラメータ ： TABLES = 表名
・ユーザー・モード 指定したユーザーが所有するすべてのオブジェクト（表、データ、権限付与、索引など）をエクスポートする。
パラメータ ： OWNER = ユーザー名
・全データベース・モード SYSのスキーマにあるオブジェクト以外、すべてのデータベース中のオブジェクトをエクスポートする。
パラメータ ： FULL = Y
全データベース・モードは、一般的に、データベースのバージョンアップやリリースアップに使われる。
EXP_FULL_DATABASEロールを持つユーザーだけが、このモードを実行できる。
例1） ・全データベースのエクスポート
ユーザーsystem でエクスポートを実行。データベース全体をエクスポートし、ファイルora8all.dmp に出力。
exp system/manager FULL=Y FILE=ora8all.dmp
例2） 特定表のエクスポート
ユーザーmate01 が所有する表 empとdept をエクスポート。権限と索引もエクスポートする。
exp mate01/oracle8 TABLES=emp,dept GRANTS=Y INDEXS=Y
■EXPORTユーティリティは、FULL=Yを指定して、データベース全体のバックアップとして利用することもできる。
バックアップを効率的に行うために、バックアップ後に変更された差分だけを取得することもできる。
これらはEXP実行時に、INCTYPEパラメータで指定する。
ただし、全データベース・モード（FULL=Y）でのみ、増分、累積、全エクスポートを実行することができる。
・増分エクスポート 前回エクスポートした以降に更新されたオブジェクトだけエクスポートする。
パラメータ ： INCTYPE = INCREMENTAL
・累積エクスポート 前回、全エクスポートまたは累積エクスポートした以降に更新されたオブジェクトをエクスポートする。
パラメータ ： INCTYPE = CUMULATIVE
・全エクスポート 増分と累積エクスポートを実現するために使用する。全データベースをエクスポートする。
パラメータ ： INCTYPE = COMPLETE
例3） 増分エクスポート
ユーザーsystem で、増分エクスポート
exp system/manager INCTYPE=INCREMENTAL
■IMPORT
IMPORTユーティリティは、Oracle Enterprise Manager を使用することができるが、一般的にはIMPコマンドを使用する。
■代表的なパラメータ
・USERID インポート処理を実行するユーザー名
/ の後に、パスワードも記述する。
・BUFFER データを取り出すために使用するバッファのバイト数
・FILE インポート・ユーティリティで使用するファイル名
デフォルト：EXPDAT.DMP
・SHOW エクスポート・ファイルの内容をリストするだけかどうか（ Y / N )
デフォルト：N
・IGNORE インポートしているオブジェクトが既に存在していた場合の対応方法
Y :行が既存の表にインポートされる。
N :エラーがレポートされ、スキップされる。（デフォルト）
・GRANTS 権限をインポートするかどうか（ Y / N )
デフォルト：Y
・INDEXES 索引をインポートするかどうか（ Y / N )
デフォルト：Y
・ROWS データ行をインポートするかどうか（ Y / N )
デフォルト：Y
・FULL インポート時に取り出すオブジェクトの単位を指定する。
全データベース・モードのインポート
・FROMUSER インポートされるオブジェクトのスキーマ
ユーザー・モードのインポート
・TOUSER インポートするオブジェクトのスキーマ
・TABLES 表モードのインポート
例1） 特定ユーザーのインポート
ユーザーsystem でscottのオブジェクトを、mate01のスキーマにインポート
imp system/manager FROMUSER=scott TOUSER=mate01
■IMPORTユーティリティは、障害発生時のデータ回復方法として利用することもでき、増分、累積、全エクスポートされたファイルを、効果的に取り込むことができる。
IMP実行時に、INCTYPEパラメータで指定。
・SYSTEM 前回の増分エクスポート・ファイルを使用して、最新のシステム・オブジェクトをインポートする。
・RESTORE 時系列順に、エクスポート・ファイルを適用する。
■バックアップ
DOSコマンド
$> exp user/pass@dbname
file=c:\backup.dmp log=c:\backup_exp.log
復元方法
DOSコマンドより
$> imp user/pass@dbname
file=c:\backup.dmp log=c:\backup_imp.log
・前環境と同一のスキーマ
・前環境と同一の表領域
などは事前に作成しておく必要なし
・ひとつのテーブルだけ復元したいとき
imp user/pass@dbname
file=c:\backup.dmp tables=hogehoge log=c:\backup_imp.log
・違うスキーマにテーブル環境やインデックス情報等の復元をしたいとき
imp system/manager@dbname
file=c:\backup.dmp tables=hogehoge log=c:\backup_imp.log fromuser=fromname touser=toname
・既存するテーブル等の復元をする(上書き)
exp username/password@ORACLE_SID
file=wlcs.dmp owner=HOGEHOGE log=backup_exp.log 'COMPRESS=Y' ignore=y destroy=y
Oracle10g import/exportユーティリティ
Oracle10gからimport/exportユーティリティが変更。
DataDumpなるもの。コマンドラインはimpdp/expdp。
ただし9iなどの旧バージョンからは通常のimpを使用。