Oracle 11g SQL基礎復習２

◆GROUP BY
   ・GROUP BY句とHAVING句の順番は交換できる
　・ORDER BY句はGROUP BY句と一緒に使用する場合、ORDER BYではGROUP BY句で指定したいずれかの列を使用しなければならない。


◆関数
　・ADD_MONTH(日付,n)
　　指定した日付が月末の場合、nヶ月後の月末日を戻す。
　・LENGTH
　　スペースを含めた文字数が戻ります
LENGTH関数は、全角文字(漢字、ひらがな等)を１文字とします
・LENGTHB
　　バイト単位の文字列の長さを調べる 


◆SQLPLUSコマンド
　・CHANGEコマンド 
　機能：文字列を置換する（区切り文字は任意に設定可能）
構文：C[HANGE sep 置換対象文字列　[ sep [置換文字列　[sep]]
説明：
・SQLバッファ内の置換対象文字列を置換文字列で置き換えます
・sepは、区切り文字で、任意に指定することができます（半角文字）
・置換対象文字列が複数存在する場合には、先頭の１つのみを置換します
・置換文字列後の区切り文字の指定は、任意です 

◆制約

　・参照整合性制約

　　　親表の親キーには、主キー制約又は一意キー制約の設定が必須。

　　　親表の定義後に、子表の外部キーを定義することができる
　　　外部キーは１つまたは複数の列の組み合わせで指定する
　・副問い合わせによる表作成でコピー可能なものは、以下の通りです 
　　表定義、データ、NOT NULL制約 
　・制約のタイプ
　　制約タイプはビューUSER_CONSTRAINTSの列CONSTRAINT_TYPEの値による調べる
C...CHECKまたはNOT NULL  
P...主キー(PRIMARY KEY)
U...一意キー(UNIQUE)
R...参照整合性(REFERENCES)
V...WITH CHECK OPTION指定のビュー  
O...WITH READ ONLY指定のビュー 

◆ビュー

・複数の表からビューを作成できます 

・ビューをもとにビューを作成できます 
・ビューを使用して更新、追加、削除ができます（制限あり）
・ビューを書き換えることができます
・元表がない場合にもFORCE句を指定するとビューを作成できます 
・ビューの元表が削除されてもビューは削除されません（無効になる） 

◆データ型
　・ NUMBER型
　　指定桁数に収まらない数値は格納できません。エラーになります 
　　桁数を越えた整数部はエラーになります
桁数を越えた小数部は四捨五入されます
◆シノニム
　・パブリック・シノニムの作成
　　CREATE PUBLIC SYNONYM シノニム名 FOR オブジェクト名; 
　　パブリック・シノニムを作成するには、CREATE PUBLIC SYNONYM権限が必要 
　　パブリック・シノニムは、データベースのすべてのユーザーがアクセスできる 
　　シノニム名の最大長は32バイト（通常30バイトまで、例外は、Java の機能使用する場合） 
　　スキーマ・オブジェクトは、存在しなくてもシノニムを作成できる
　　スキーマ・オブジェクトにアクセスするには、別途アクセス権限が必要 

◆ロール・権限

・ユーザに付与されるロールの確認できるビュー

USER_ROLE_PRIVS	現行のユーザーに付与されたロールを示します
SESSION_ROLES	ユーザーが現在使用できるロールを示します
DBA_ROLE_PRIVS	データベース内のすべてのユーザーおよびロールに付与されたロールを示します

・ロールに含まれる権限の確認できるビュー

ROLE_SYS_PRIVS	ロールに付与されたシステム権限を示します
ROLE_TAB_PRIVS	ロールに付与された表権限を示します
ROLE_ROLE_PRIVS	別のロールに付与されたロールの情報を示します

*ユーザーがアクセス権限を持っているロールの情報のみが得られます

・権限の確認できるビュー

USER_SYS_PRIVS	現行のユーザーに付与されたシステム権限を示します
SESSION_PRIVS	ユーザーが現在使用できる権限を示します
DBA_SYS_PRIVS	ユーザーおよびロールに付与されたシステム権限を示します
USER_TAB_PRIVS	現行のユーザーがオブジェクト所有者、権限付与者または権限受領者であるオブジェクトの権限付与を示します
USER_TAB_PRIVS_MADE	現行のユーザーがオブジェクト所有者であるオブジェクトの権限付与を示します
USER_TAB_PRIVS_RECD	現行のユーザーが権限受領者であるオブジェクトの権限付与を示します
DBA_TAB_PRIVS	データベース内のすべてのオブジェクトについての権限付与を示します
ALL_TAB_PRIVS_MADE	現行のユーザーがオブジェクト所有者または権限付与者であるオブジェクトの権限付与を示します
ALL_TAB_PRIVS_RECD	・現行のユーザーが権限受領者であるオブジェクトについての権限付与 ・使用可能になっているロールまたはPUBLICが権限受領者であるオブジェクトについての権限付与

Oracle DBA11g Bronze復習２

◆制約
・主キー制約は、表構造を表示するDESCでは、NOT NULLと表示される（NOT NULLだけを削除することはできない）
◆バックアップ・リカバリ
・バックアップおよびリカバリを自動化するためには、以下の設定が必要です
 フラッシュ・リカバリ領域の作成(位置とサイズの指定)
 ARCHIVELOGモードでの稼動
 アーカイブ・ログの出力先をフラッシュ・リカバリ領域に設定
・フラッシュバック・テーブルの場合 
　・フラッシュバック・テーブルでは、参照整合性制約を維持したままリカバリします。

　　参照整合性制約に違反するリカバリはできません
・親表と子表を１度にフラッシュバック・テーブルをすると参照整合性制約を維持したままリカバリされる
・親表と子表を個別にフラッシュバック・テーブルを行っても参照整合性制約に違反しなければ可能です
・フラッシュバック・ドロップの場合
フラッシュバック・ドロップでは、表はリカバリされますが、参照整合性制約は削除されます。主キー制約は、維持されます 

◆リスナー
・リスナーは、Oracleインストール時に、デフォルトで構成され 、インストール後に、変更することができます
・リスナーの構成は、listener.oraファイルに格納されます
・Enterprise Managerからリスナーの開始又は停止できます 
◆セキュリティ
　・ユーザの削除
　　ユーザーを削除するには、DROP USER文を使用します
構文：DROP USER ユーザー名　[CASCADE];
ユーザーが作成したロールは削除されません
現在接続しているユーザーは削除できません
自分のスキーマ内に所有するオブジェクトがあると削除できません
CASCADE句を指定するとスキーマ内に所有するオブジェクトや表に関連する主キー、参照整合性制約等も削除されます
 ・外部認証
外部認証は、オペレーティング・システムまたはネットワーク・サー ビスによってユーザーを認証する方法
初期化パラメータOS_AUTHENT_PREFIXで、ユーザー名の接頭辞を指定する。デフォルト値は、OPS$である 
　・ロールのWITH ADMIN OPTION付き付与 
　　WITH ADMIN OPTION付きで権限を付与されるとロールの削除、ロールの更新、ロール権限の付与、ロール権限の取り消しが可能になる

　・システム権限とオブジェクト権限

　　システム権限の付与できるユーザ：DBA

　　オブジェクト権限の付与できるユーザ：

　　オブジェクト所有者、DBA、WITH GRANT OPTION付きで権限付与されたユーザ

◆DBCAのできること
　・データベースの作成、削除
　・データベースへのオプションの追加
　・テンプレート管理
　・自動ストレージ管理の構成
　＊データベースのアップデートはDBUAで行う。
◆メモリ
　・ラージプール
　　　ラージ・プールは、システム・グローバル領域(SGA)のオプションのメモリー領域です
ラージ・プールは、以下の目的で使用されます
セッション・メモリー
I/Oスレーブ
バックアップおよびリストア操作
パラレル問合せ
ラージ・プールは、LRU（Least Recently Used）アルゴリズムを使用しないので、古いメモリーを削除しません (共有プールの他のメモリーは、LRUリストで管理します) 
　　　自動共有メモリー管理の対象コンポーネントの１つである
初期化パラメータLARGE_POOL_SIZEで指定(バイト単位)
デフォルト値は、0。ALTER SYSTEM文で動的に変更できます
・データベース・バッファ・キャッシュ
　　データベース・バッファ・キャッシュは、データファイルから読み込んだデータ・ブロックのコピーが保持される領域です。修正済のブロックおよび未修正のブロックが含まれます
データベース・バッファ・キャッシュは、複数のバッファ・プールで構成されます。以下のようなバッファ・プールがデータベース・バッ ァ・キャッシュのサイズに関連します
DB_CACHE_SIZE.,,,,,,,,..デフォルトのデータ・ブロック
DB_KEEP_CACHE_SIZE..,,,.再利用の可能性が高いデータ・ブロック
DB_RECYCLE_CACHE_SIZE...再利用の可能性が低いデータ・ブロック
標準のブロック・サイズは、初期化パラメータDB_BLOCK_SIZEで設定します
標準以外の2K～32Kのブロック・サイズによる表領域を作成できます
LRUアルゴリズムで管理される
DB_BLOCK_SIZEは、動的な変更はできません
DB_CACHE_SIZEは、ALTER SYSTEM文で、動的に変更できます
データベース・バッファ・キャッシュ内の変更された(使用済)バッファをDBWnプロセスがディスクに書き込みます
・実行計画
　SELECT文やDML文を実行する場合には、いくつかの処理をどのような順番で行なうかを決めます。この処理の組合わせを実行計画といいます
実行計画は、再利用できるようにSGAの共有プールに格納します
正確には、SGAの共有プールの共有SQL領域に格納されます
複数ユーザーが使用する同じSQL文を共有プールに格納してメモリーを節約します  

Oracle DBA11g Bronze復習１

◆インストール条件
・１GBのRAM
・１GB（OR RAMの1.5倍）のスワップ領域
・/tmp　４００M
・オラクルソフトウェア用に1.5Gディスっく領域
◆表領域
・必須の表領域は、SYSTEMとSYSAUX
・列単位で設定できるオブジェクト権限は、以下の３つです
  　INSERT、UPDATE、REFERENCE
・UNLIMITED TABLESPACEシステム権限
　*ロールに対してではなく、ユーザに対してのみ付与できる
　メリット：この権限によって、そのユーザーに対する明示的な表領域割当て制限がすべて置き換えられます
　デメリット：UNLIMITED TABLESPACEシステム権限を持つユーザーから表領域へのアクセス権を選択的に取り消すことはできません。 この権限を取り消した後でのみ、選択的または制限付きアクセス権を付与できます
・表領域管理方式
　ローカル管理（デフォルト）：エクステントの使用/解放状態はビットマップ（１ビットは一ブロック/ブロックグループに対応）で管理する。エクステントが割当てられるか解放されると、ビットマップの値が更新される。
　ディクショナリ管理：エクステントが割り当てられたり再利用のために解放されるたびにデータ・ディクショナリ内の適切な表が更新されます。

ローカル管理表領域には、ディクショナリ管理表領域に比べて次のような長所があります。

　・エクステントのローカル管理によって、隣接する空き領域が自動的に追跡され、使用可能エクステントを結合する必要がなくなります。

　・エクステントのローカル管理によって、再帰的な領域管理操作が回避されます。エクステント内の領域を使用または解放することにより、データ・ディクショナリ表やロールバック・セグメントの領域を使用または解放する他の操作が生じる場合に、ディクショナリ管理表領域内でこの種の再帰的な操作が発生することがあります。

・表領域使用率のしきい値
　・新規に作成した表領域は、デフォルトのしきい値が設定されます
　・１つの表領域に対して複数のしきい値を設定することができます
　・しきい値を無効にするには、NULLを設定します
　・アップグレードした表領域のしきい値は、無効になっています
　・表領域使用率のしきい値のチェックは、MMONが10分間隔で行っています

・表領域不足の対策
 表領域のデータ・ファイルを自動拡張にする
 表領域にデータ・ファイルを追加する
 表領域のデータ・ファイルのサイズを大きくする
◆セキュリティ管理
・優先資格証明（優先接続情報）
　ターゲットの暗号化されたログイン資格証明を管理リポジトリに格納することで、管理ターゲットへのアクセスを簡単にします。
・デフォルトロール
　デフォルトロールは、ユーザがセッションを作成した時に、自動的にそのユーザに対して有効になる。
　ユーザに対して複数のデフォルトロールを割り当てることができるが、割当てなくても構わない。
　ユーザーのデフォルト・ロールは、CREATE USER文では設定できません。最初にユーザーを作成したときに、そ  のユーザーのデフォルト・ロールはALLに設定されます。これによって、その後ユーザーに付与されるロールはすべてデフォルト・ロールになります。
   ユーザーのデフォルト・ロールを変更するには、ALTER USER文を使用します。例：
   alter user usrname default role rolename
  ロールをユーザーのデフォルト・ロールにするには、そのロールがユーザーに付与されている必要があります。 

  デフォルトロールは削除できない

　パスワードが必要なロールは、パスワードの要求なしで有効になります
　ロールを介して付与されるロールや外部サービスの認証が必要なロールは、デフォルト・ロールとして有効になりません

◆初期化パラメター

　・動的変更できないパラメータリスト：

SGA_MAX_SIZE	SGA全体の最大サイズ（バイト単位）
LOG_BUFFER	REDOログバッファのサイズ（バイト単位）
AUDIT_TRAIL	enable system auditing
COMPATIBLE	互換性を維持する必要のあるバージョンを指定する
CONTROL_FILES	制御ファイル名をフルパスで指定する
DB_BLOCK_SIZE	ブロックサイズ（最小読み書き単位）のサイズを指定（バイト単位）
DB_FILES	max allowable # db files
DB_NAME	インスタンスが管理するDB名
INSTANCE_TYPE	type of instance to be executed　（rdbm）
REMOTE_OS_AUTHENT	allow non-secure remote clients to use auto-logon accounts
SESSIONS	Oracleサーバ全体での同時セッション最大数
UNDO_MANAGEMENT	UNDO領域管理モードを設定する。 AUTO：自動UNDO管理モード MANUAL：手動UNDO管理モード
WORKAREA_SIZE_POLICY	policy used to size SQL working areas (MANUAL/AUTO)

・Oracle Serve構成
　Oracle Server＝インスタンス＋データベース
インスタンス＝SGA＋バックグラウンドプロセス
データベース（物理）＝データ・ファイル＋制御ファイル＋ REDOログ・ファイル

・Oracleインベントリ・ディレクトリ
　LinuxおよびUNIXの場合のOracleソフトウェアのインストール時に、イン ベントリ・ディレクトリを指定
　Oracleインベントリ・ディレクトリ(oraInventory)には、システムに インストールされた全ソフトウェアのインベントリ(追跡情報)を格納
パッチを適用する際や既存のインストールをアップグレードする際、また Oracleソフトウェアを削除する際に使用され
すべてのOracleソフトウェアのインストールに必須で、共有のものです
定期的なバックアップが必要です
・アラート・ファイルに書き込まれるメッセージ・エラー情報
内部エラー(ORA-600)、ブロック障害（ORA-01578）、 デッドロック・エラー（ORA-00060）等
CREATE/ALTER/DROP DATABASE/TABLESPACE/ROLLBACK SEGMENT文の情報
データベースの起動、停止情報
デフォルト値以外のすべての初期化パラメータの値
バックアップやオンラインREDOログ・ファイルのアーカイブ作業情報 

・デフォルト存在ロール：
　CONNECT　　一般ユーザ用
　RESOURCE　開発者用
　DBA　管理者用のロール。ADMIN OPTION付きのすべてのシステム権限
　EXP_FULL_DATABASE　エクスポートモードで全データベースモードを実行できる権限

　IMP_FULL_DATABASE　全データベースモードでエクスポートされたオブジェクトをインポートする権限
  DELETE_CATALOG_ROLE　システム監査表(SYS.AUD$)からレコードを削除権限

　EXECUTE_CATALOG_ROLE　データ･ディクショナリ･パッケージのEXECUTE権限

　SELECT_CATALOG_ROLE　データ･ディクショナリ表のSELECT権限

・ REDOログファイルへの書き込みタイミング  
トランザクションのコミット時
REDOログ・バッファが容量の1/3に達した時
タイムアウト発生時（3秒）
DBWnがデータベース・バッファ・キャッシュ内の使用済バッファをデータ・ファイルに書き込む前

Oracle 11g SQL基礎復習１

◆単一行関数：問い合わせ対象の表やビューの各行に対して、単一結果行を戻す
★SELECT,WHERE、ORDER　BY句使用可能。
◆グループ関数：問い合わせ対象の行のグループに対して、単一結果を戻す。
SELECT文のFROM句、WHERE句、GROUP BY句で使用不可。
★すべてのグループ関数においてNULL値は無視される。
◆SELECT句で指定した列別名はORDER　BY句で使用できるが、WHERE、GROUP
BY、HAVING句では使用できない。
◆結合について

内部結合	結合条件を満たすデータだけを戻す
外部結合	結合条件を満たさないデータも含めて戻す

クロス結合	2つの表のすべての組み合わせを作成する。直積、デカルト積とも呼ばれる
自然結合	2つの表の同じ名前の列すべてを、自動的に等価結合の結合条件として使用する
USING句	2つの表の同じ名前の列のうち、USING句で指定した列のみを等価結合の結合条件として使用する
ON句	2つまたは1つの表から、ON句で指定した結合条件を満たすもののみを戻す
自己結合	1つの表から結合条件を満たすもののみを戻す。ON句を使用する

◆表名、列名等オブジェクト名のルール：

・長さ１〜３０

・文字で始まる

・使用できる文字は文字、数字、#、$、_

・予約語（SELECT,UPDATE,DATE）等は使用できない

・スキーマ内で重複してはいけない
・大小文字は区別されない

◆データ型

・LONG/LONG RAWの制限：

・一つ表に一つ列のみ

・副問い合わせで表を作成する場合、コピーされない

・GROUP、ORDER　BY句で使用できない

・制約や索引は使用できない

◆制約

・CHECK制約の制限：

・CURRVAL,NEXTVAL,LEVEL,ROWNUM擬似列は使用できない

・SYSDATE,UID,USER,USERENV関数使用できない

・他の行の値を参照する問い合わせは使用できない

・他の列の値を参照する場合は表制約で記述する必要がある