ORACLE基礎知識

パーティショニングとは

大きな1つのテーブルを分割して複数のディスクに配置すること

通信ミドルウェアレベルでの通信確認

9.2

コマンドプロンプトより実行

C:\> tnsping サーバー名

SQL Plus

SQL Plus コマンド ([]内は省略文)

9.2

指定したテーブル、ビュー、PL/SQLなどの定義の表示

DESC[RIBE] オブジェクト名

DESC emp

ORACLEに接続

CONN[ECT] ユーザー名/パスワード@サービス名

CONNECT SCOTT/TIGER@TNS

システム変数情報の表示

SHO[W] システム変数名(ALL)

/* 現在使用しているユーザ名を表示 */

SHOW USER

/* PL/SQLのコンパイルエラーを表示 CREATE作成後にエラーがあった場合に */
/* 使用してエラー内容を見ることができる　　　　　　　　　　　　　　　*/

SHOW ERR[ORS]

/* SQL.SQLCODEの値を表示（直近オペレーションのSQLリターンコード） */

SHOW SQLCODE

最大表示長の変更　デフォルト：80

　SET LINES[IZE] xxx

SET LINESIZE 100

SQL文所要時間の表示

SET TIMING ON
SQL文

SET TIMING ON
SELECT * FROM table1

出力内容の表示
　　（ストアドプロシージャ、PL/SQLの出力（DBMS_OUTPUT.PUT_LINE）をSQL Plus で表示するかの制御　OFFで非表示）

SET SERVEROUTPUT ON

SET SERVEROUTPUT ON

ストアドプロシージャ内に DBMS_OUTPUT.PUT_LINE('ここに変数やコメント等を指定') を入力しストアドを実行した場合に SET SERVEROUTPUT を ON にしていると表示してくれる

指定したコマンド・ファイルの実行

@ ファイル名

@c:\file.sql

SQLPLUSの内容をファイルに出力する

SPOOL OFFを行うまで書き込む

SPOOL [出力先ファイルパス]
・
・
・
SPOOL OFF

直接入力ではなく @、@@、@? や START によるスクリプトファイルの実行時にコマンドの実行を表示する

SET ECHO ON

SET ECHO ON

毎回同じ設定でSQL Plusを起動する方法

サイトプロファイル[すべてのユーザーに有効]

下記のディレクトリにglogin.sqlを置く
- Windows環境
  
  %ORACLE_HOME%sqlplus\admin\glogin.sql
- UNIX環境
  
  $ORACLE_HOME/sqlplus/admin/glogin.sql
ユーザープロファイル[特定のユーザーに有効]

下記のいずれかのディレクトリにlogin.sqlを置く
- Windows環境
  1. SQL Plusを起動する作業フォルダ
  2. 環境変数SQLPATHに設定されているパス
  3. レジストリにSQLPATHとして設定されているパス
- UNIX環境
  1. SQL Plusを起動する作業フォルダ
  2. 環境変数SQLPATHに設定されているパス

SET LINESIZE 100
SET TIMING ON

データベースの起動と停止

9i以降

通常起動（インスタンス、データベースマウント、オープン）

STARTUP

nomountで起動(インスタンスのみ起動)

STARTUP NOMOUNT

mountで起動(インスタンス、データベースマウント)

STARTUP MOUNT

pfileを指定して起動

STARTUP PFILE='c:\initorcl.ora'

インスタンスをデータベースにマウント

ALTER DATABASE MOUNT

クローズしているデータベースをオープン

ALTER DATABASE OPEN

通常停止

SHUTDOWN IMMEDIATE

強制停止

SHUTDOWN ABORT

初期値パラメータの設定・変更方法

9i以降

パラメータ値の確認

SHOW PARAMETER LOG

pfileからspfileを作成

CREATE SPFILE=SPFILEファイルパス FROM PFILE=PFILEファイルパス

CREATE SPFILE='c:\oracle\database\spfileorcl.ora' FROM PFILE='c:\initorcl.ora'

spfileからpfileを作成

CREATE PFILE=PFILEファイルパス FROM SPFILE

CREATE PFILE='c:\initorcl.ora' FROM SPFILE

初期値設定変更(再起動あり)

ALTER SYSTEM SET パラメータ=値 SCOPE=SPFILE

ALTER SYSTEM SET パラメータ=値 SCOPE=SPFILE

初期値設定変更(再起動なし)

ALTER SYSTEM SET パラメータ=値 SCOPE=SPFILE

ALTER SYSTEM SET パラメータ=値

アーカイブログの設定方法

9i以降

アーカイブログ出力のON、OFFの確認

ARCHIVELOG LIST

アーカイブログ出力をONにする

STARTUP MOUNTEXCLUSIVE
ALTER DATABASE ARCHIVELOG
ALTER DATABASE OPEN
ARCHIVELOG START

アーカイブログ出力をOFFにする

ALTER DATABASE NOARCHIVELOG

アーカイブログの出力ファイルを強制的に出力する

ALTER SYSTEM SWITCH LOGFILE

ARCserveのAgent for Oracle導入時のアーカイブログの設定

9i以降

アーカイブログモードへ変更

CONNECT SYS/パスワード AS SYSDBA
STARTUP MOUNTEXCLUSIVE
ALTER DATABASE ARCHIVELOG
ALTER DATABASE OPEN
ARCHIVELOG START

自動アーカイブ機能の有効化

ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE SCOPE=SPFILE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = アーカイブログ出力先フォルダパス名 SCOPE=SPFILE
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = "ARC%S_%R.%T" SCOPE = SPFILE

　 ※上記の設定を行わなければARCserveのAgent for Oracleが使用できない

SQL Plus からの PL/SQL実行方法

OUTの引数がない場合

EXEC[UTE] ストアドプロシージャ名(引数2,引数3,引数4,･･･)

EXECUTE SAMPLE_PLSQL('AAA',0,SYSDATE)

OUTの引数がある場合

VAR[IABLE] バインド変数名型
EXEC[UTE] ストアドプロシージャ名(引数2,引数3,引数4,･･･,:バインド変数名)

SQL> VARIABLE RETURN CHAR
SQL> EXECUTE SAMPLE_PLSQL('AAA',0,SYSDATE,:RETURN)

PL/SQLプロシージャが正常に完了しました。

SQL> PRINT RETURN

RETURN
--------------
0

バインド変数は前に":"を付ける事によりバインド変数として認識する
PRINT で OUTの引数の値を表示する

戻り値の引数がある場合

VAR[IABLE] バインド変数名1 型
VAR[IABLE] バインド変数名2 型
BEGIN
:バインド変数名1 := ストアドプロシージャ名(引数2,引数3,引数4,･･･,:バインド変数名2)
END;

SQL> VARIABLE RETURN1 CHAR
SQL> VARIABLE RETURN2 CHAR
SQL> BEGIN
　2> :RETURN1 := SAMPLE_PLSQL('AAA',0,SYSDATE,:RETURN2)
　3> END;
　4> /

PL/SQLプロシージャが正常に完了しました。

SQL> PRINT RETURN1

RETURN1
--------------
0

SQL Plus での置換変数の使用

&置換変数で指定する

SQL> SELECT * FROM table1 WHERE code = &code;
SQL> codeに値を入力してください: 1001
旧1: SELECT * FROM table1 WHERE code = &code
新1: SELECT * FROM table1 WHERE code = 1001

code
--------------
1001

&&置換変数を使用することで一度入力した値を保持することができる

1024バイト以上のINSERT文をSQLPlusに貼り付けて実行するとORA-00917が発生する

発生原因
1. 製品不具合
発生条件
1. INSERT INTO 以降に1024バイト以上ある場合
2. 改行が含まれていない場合
3. コマンドプロンプト上でSQLPlusにINSERT文を貼り付けた場合
回避策
1. GUI版 SQLPlus for Windows (sqlplusw.exe) を使用
2. 実行するINSERT文をファイルに保存後、SQLPlusにて@ファイル名で実行
3. 実行するINSERT文に改行を挿入し、貼り付け

パフォーマンス

オラクルクライアントの起動が遅い

9.2

クライアントのoracle\ora92\network\ADMIN\sqlnet.ora ファイルの
SQLNET.AUTHENTICATION_SERVICES= (NTS) の行を

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.AUTHENTICATION_SERVICES= (NONE)

に変更　ログイン認証時にかかっていた時間を短縮できます

AUTOTRACEを使用してインデックスの使用状況を確認する

9.2

SQL Plus を立ち上げ SET AUTOTRACE ON を入力

（オートトレース用コマンド）

SET AUTOTRACE ON	=	SQL文の結果＋実行結果＋統計　表示
SET AUTOTRACE TRACEONLY	=	実行結果＋統計　表示
SET AUTOTRACE EXPLAIN	=	実行結果のみ表示
SET AUTOTRACE STATISTICS	=	統計のみ表示
SET AUTOTRACE OFF	=	トレース終了

対象となるSQL文を入力

※PLAN_TABLEを作成しておく必要があります

SQL Loader

SQL*Loaderとは

9.2

SQLLoader はテキストファイル等の外部データをOracleデータベースにロードするためのユーティリティです
特徴：
1. 様々な形式（固定長や可変長）、様々な文字コード（SJISやEUC）の外部データを取り込める
2. 同時に複数の表へロードが可能
3. 複数のデータファイルから一度に同じ表へロードが可能
4. ロードした順に連番をセットすることができる
5. 大量データから必要なものだけを絞り込める
6. SQL関数で多彩な加工が行える
7. 実行結果をログに出力
8. 柔軟性／高速性に応じた２つのモードを用意（従来型パス・モードとダイレクト・パス・モード）

CSVファイルの取込

9.2

コントロールファイルの作成

　CSVファイルの各項目とテーブルの項目との関連付け等を指定するファイル

　例）emp.ctl

OPTIONS(LOAD=1000,SKIP=1,ERRORS=-1,ROWS=100)
LOAD DATA
INFILE 'data/emp.csv'
BADFILE 'emp.bad'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
EMPNO,
EMPNAME,
EMPDATE,
)

各項目の説明

OPTIONS	=	sqlldrに渡す引数を、コントロールファイルの中に記述
LOAD	=	ロードするレコード件数 -1の場合は全て
SKIP	=	スキップするレコード件数
ERRORS	=	許容するエラーの件数 -1の場合は全て
ROWS	=	コミット位置
INFILE	=	入力データであるCSVファイルや固定長ファイル ※相対パスでディレクトリを指定可能
BADFILE	=	エラーが発生しDBに取り込めないデータがあった場合にそのデータがこのファイルに出力
INSERT	=	新規にデータをロードテーブルは空である必要がある既にデータがある場合はエラー
APPEND	=	データを追加する既にデータがある場合はduplicateしないデータだけが追加
REPLACE	=	テーブルの内容を全て削除し新規にデータをロード削除はDELETEに相当
TRUNCATE	=	削除はTRUNCATEに相当 truncateできる権限が必要
INTO TABLE	=	データを入れるテーブル
FIELDS	=	項目の区切り方の指定
TERMINATED BY	=	データを区切る文字を指定カンマ区切りは　TERMINATED BY "," タブ区切りは　　TERMINATED BY X'09' 固定長ファイルは不要
OPTIONALLY ENCLOSED BY	=	データを囲む文字を指定囲まない場合は不要ダブルクォーテーションで囲む場合は　OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS	=	この指定があるとデータの無い項目にNULLを入れる

備考

コントロールファイルの中で「--」で始まっている行はコメント
各項目の後ろに関数で演算 EMPDATE "TO_DATE(:EMPDATE,'YYYY/MM/DD HH24:MI:SS')",
　※関数の引数項目名は「:（コロン）」を付ける事
空白のみの項目は空文字列として扱われNULLにはならない
　※NULLにする例
　　　FLD1 POSITION( 1 : 7 ) ZONED(7,2),
　　　FLD2 POSITION( 8 : 14 ) CHAR "decode(:FLD2,'',null,to_number(:FLD2))",
　　　FLD3 POSITION( 15 : 16 ) DECIMAL EXTERNAL

データファイル（固定長ファイル、CSV形式のファイル）

データファイル名はコントロールファイル名に記述
※Windowsのテキストファイルの場合ファイルの最後にEOFのコードが付いている場合があり、この行は（属性が不一致であれば）
　エラーになりロードされない（badファイルに出力される）
データファイル名はコントロールファイル名に記述

SQL*Loader の実行

sqlldrの引数にコントロールファイル名を指定して実行する
※Windowsの場合バッチファイルを作成すると便利

SQLLDR ユーザー/パスワード@SID control=コントロールファイルのディレクトリ/emp.ctl

ファイル名、ログファイル、バッドファイルをコントロールファイル内ではなくバッチに記述した場合

SQLLDR ユーザー/パスワード@SID control=コントロールファイルフルパス名 data=ファイルフルパス名
log=ログファイルフルパス名 bad=バッドファイルフルパス名 silent=all

　　※silent=all をつけると画面からはメッセージが表示されない

条件に一致した場合のみローダーで取り込む方法

9.2

コントロール内でWHENを記述し条件分岐させる

EMP表に対して empno が AAAA と BBBB のものを Load する方法

例）emp.ctl

LOAD DATA
INFILE 'data/emp.csv'
REPLACE

INTO TABLE EMP
WHEN EMPNO = 'AAAA'
(EMPNO POSITION(14) INTEGER EXTERNAL,
ENAME POSITION(615) CHAR,
DEPTNO POSITION(1718) CHAR,
MGR POSITION(2023) INTEGER EXTERNAL)

INTO TABLE EMP
WHEN EMPNO = 'BBBB'
(EMPNO POSITION(14) INTEGER EXTERNAL,
ENAME POSITION(615) CHAR,
DEPTNO POSITION(1718) CHAR,
MGR POSITION(2023) INTEGER EXTERNAL)

ダイレクト・パス・インサート（ダイレクト・ロード・インサート）

9.2

特徴：高速に大量データをインサート
代償：テーブルが排他ロックされる
　　　　NOLOGGING状態の場合、REDOログが出力されない
　　　　HWM(ハイウォータマーク)の後ろの連続領域にデータが入る
　　　　インデックスの再構築などにより、多くの一時表領域を消費
　　　　VALUES句は使用不可
　　　　HWM(ハイウォータマーク)の管理を誤るとセグメントの領域が肥大化して格納効率が悪化し、検索速度が低下
制限：参照整合性制約、トリガが定義されている表
　　　　索引構成表、クラスタ表
　　　　オブジェクト型を使用している表
　　　　レプリケート、分散トランザクション

使用方法：SQL*LoaderのOptionsで指定

direct=TRUE	ダイレクトモードを使用
columnarrayrows=5000	列配列に対する行数 (5000)
streamsize=256000	ストリーム・バッファのバイト数 (256000)
multithreading=TRUE	マルチスレッドの使用
parallel=TRUE	パラレル・ロードの実行

SQL*Loaderで「xxxxxバイトは単一行に対してyyyyyバイトでなければなりません。」エラー対処方法

9.2

原因：一行のバイト数が大きいテーブルに対してロードすると発生

対処：SQL*Loaderで起動時のパラメータにバインド配列のサイズ指定を追加（bindsize=）

例） ※yyyyyは整数値

SQLLDR ユーザー/パスワード@SID control=コントロールファイルのディレクトリ/emp.ctl bindsize=yyyyy

インポート/エクスポート

インポート／エクスポート

コマンドプロンプトで実行

フルエクスポート

EXP [ユーザ名]/[パスワード]@[TNS名] file=[ファイル名パス(C:\dmpfile.dmp)] log=[ログファイルパス(C:\dmpfile.log)] FULL=y

テーブルスペースの移動

EXP system/manager@[TNS名] file=[ファイル名パス(C:\dmpfile.dmp)] log=[ログファイルパス(C:\dmpfile.log)] owner=[ユーザ名] grants=n indexes=y
copress=y rows=y

IMP system/manager@[TNS名] file=[ファイル名パス(C:\dmpfile.dmp)] fromuser=[変更前ユーザ名]
touser=[変更後ユーザ名] rows=y indexes=y

テーブルの移動

EXP [ユーザ名]/[パスワード]@[TNS名] file=[ファイル名パス(C:\dmpfile.dmp)] TABLES=([テーブル1名],[テーブル2名],,) copress=y

IMP [ユーザ名]/[パスワード]@[TNS名] file=[ファイル名パス(C:\dmpfile.dmp)] indexes=y rows=y

　※インポートするテーブルは消しておく

各項目の説明

ignore	=	表領域違いを無視するかどうか
rows	=	データ行のエクスポート・インポート

データのエクスポートとインポート

8i以降

データのエクスポート

/* field1がSから始まるデータを抽出 */
EXP [ユーザ名]/[パスワード]@[TNS名] compress=y indexes=n constraints=n grants=n rows=y statistics=none tables=table1 query=\"where field1 \> 0 \" file=c:\exp.dmp log=c:\exp.log

データのインポート

/* エクスポートしたデータのインポート */
IMP [ユーザ名]/[パスワード]@[TNS名] ignore=y indexes=n constraints=n grants=n rows=y tables=table1 file=c.\exp.dmp log=c:\imp.log

Enterprise Manager

オラクルのリポジトリ再作成方法

10g

リポジトリの削除

emca -deconfig dbcontrol db -repos drop

リポジトリの作成

emca -config dbcontrol db -repos create

うまくいかない場合はDBAユーザでSQLPLUSから下記内容を実行しその後リポジトリの削除、作成を行う

ALTER SYSTEM FLUSH SHARED_POOL;
DROP USER SYSMAN CASCADE;
DROP USER mgmt_view CASCADE;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP PUBLIC SYNONYM setemviewusercontext;

バックアップとリカバリ

オフラインバックアップ

9i以降

オフラインバックアップ中はデータベース全体または一部を使用することはできない
バックアップを実施した時点までの復旧が可能
一貫性バックアップまたはコールドバックアップ、完全バックアップとも呼ばれている

データベースの停止は必ず正常停止させる必要がある

データベースの停止

SHUTDOWN IMMEDIATE

OSレベルで制御ファイル、データファイル、オンラインREDOログファイルを全てコピー

バックアップ対象は以下の方法で確認出来る。SYSユーザでログインして確認

制御ファイル

SELECT name FROM v$controlfile;

データファイル

SELECT name FROM v$datafile;

オンラインREDOログファイル

SELECT name FROM v$logfile;

※リカバリはコピーした全てのファイルを戻してやり、データベースを起動すればOK
　但し、オンラインバックアップとの併用で運用する場合(障害直前まで復旧する場合)オンラインREDOログファイルを
　戻してしまうと障害直前まで復旧できなくなるのでその場合、REDOログファイルは戻さないようにする

オンラインバックアップ

9i以降

オンラインバックアップ中でもデータベース全体または一部を使用することは可能
障害発生直前までの復旧が可能
非一貫性バックアップまたはホットバックアップ、不完全バックアップとも呼ばれている
アーカイブログを出力する必要がある為、容量肥大また、バックアップ中は表領域をバックアップモードでロックする為、パフォーマンスが低下する

データベースがアーカイブログモードで稼動している必要がある

制御ファイルのバックアップ

ALTER DATABASE BACKUP CONTROLFILE TO '[出力先ファイルパス]';

テーブルスペースのバックアップロック

ALTER TABLESPACE [テーブルスペース] BEGIN BACKUP;

ロックしたテーブルスペースに該当するデータファイルをOSレベルで物理コピーする

テーブルスペースのバックアップロック解除

ALTER TABLESPACE [テーブルスペース] END BACKUP;

上記２～４をデータベースが所有するテーブルスペースに対して行う

該当するテーブルスペースの確認

SELECT name FROM v$tablespace;

アーカイブログスイッチを行い最新のアーカイブログを出力する

ALTER SYSTEM SWITCH LOGFILE;

アーカイブログを全てOSレベルで物理コピーする

オンラインバックアップからのリカバリ

9i以降

バックアップした制御ファイル、データファイル、アーカイブログファイルを元の場所にＯＳレベルで物理コピーする

※制御ファイルは1ファイルしかバックアップをとっていないのでコピーして名前を変更し３ファイル用意する

　　・デフォルトの制御ファイル名
　　　　CONTROL01.CTL
　　　　CONTROL02.CTL
　　　　CONTROL03.CTL

SQL Plusにsystemユーザでログイン

sqlplus system/[パスワード]@[サービス名] AS SYSDBA

データベースをマウントする

STARTUP MOUNT

リカバリ処理を実行

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

※適用するアーカイブまでEnterキーを押下し適用していく。適用しないアーカイブログの適用メッセージが
　表示されたらcancelを入力。アーカイブログを全て適用する場合はautoと入力

データベースを開始

ALTER DATABASE OPEN RESETLOGS;

リカバリマネージャを使用したバックアップ

9i以降

リカバリマネージャへの接続

rman TARGET [ユーザ名]/[パスワード]@[TNS名]

データをイメージ・コピーとしてディスクにバックアップ

※アーカイブログモードでない場合はPLUS ARCHIVELOGは不要
※下記ファイルをバックアップ
　　・データファイル
　　・制御ファイル
　　・SPFILE
　　・アーカイブREDOログ（アーカイブログモードの場合のみ）

BACKUP DATABASE PLUS ARCHIVELOG;

リカバリマネージャを使用したリカバリ

9i以降

リカバリマネージャへの接続

rman TARGET [ユーザ名]/[パスワード]@[TNS名]

データベース全体のリカバリ

完全リカバリ

STARTUP FORCE MOUNT; RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

不完全リカバリ

※リカバリしたい時間を指定する

RUN {
　SET UNTIL TIME "TO_DATE('2009-01-01 00:0:00', 'YYYY-MM-DD HH24:MI:SS')";
　RESTORE DATABASE;
　RECOVER DATABASE;
}

ALTER DATABASE OPEN RESETLOGS;

リカバリマネージャの設定表示

9i以降

構成設定の表示

SHOW ALL;

バックアップ・セットのリスト表示

LIST BACKUP;

[トップページへ] [戻る]


	Copyright(c)2007-2008 Freedom.Net Co., Ltd. All rights reserved.