0371.blog

ORACLE MASTER Bronze Oracle Database 12c 第三章

lecture


※この記事はまだ完成していません。もうちょっと待ってね。

こんにちは、0371です。

今回はORACLE MASTER Bronze Oracle Database 12cの第三章についてまとめました。

ORACLE MASTER Bronze Oracle Database 12c 第三章

第三章は「スキーマオブジェクト」についての内容です。

スキーマについて

  • ユーザーが作成したオブジェクトを管理する仕組み「単位」を「スキーマ」という。ユーザーを作成すると、そのユーザのスキーマが自動的に作成される。
  • スキーマ名はユーザー名と同じ。
  • ユーザーがデータベース上に作成したオブジェクトは「スキーマオブジェクト」という。
  • スキーマオブジェクトにアクセス(読み書き)できるのは、スキーマと同じ名前のユーザーである(他ユーザーは権限を付与されなければアクセス不可)
  • SYSユーザーは、すべての管理操作が許可された特権的なユーザーで、データベース作成時に自動的に作成される。
  • データディクショナリの実票とビューを所有する。

「スキーマ」は表などユーザーがデータベース上に作成したものを取りまとめて管理するための仕組みです。ユーザーがデータベース上に作成したものはスキーマオブジェクトと呼ばれます。

ユーザーに関する情報や権限に関する情報、スキーマオブジェクトに関する情報などデータベースを動かすために必要な情報を Oracle 自身が管理する特別な表に格納しており、これらの特別な表の集合を「データディクショナリ」といいます。

「SYSユーザー」については以下の通り。

  • データディクショナリの実表とビューを所有するユーザーである。
  • データベース作成時に自動的に作成される。
  • すべての管理操作が許可された特権的なユーザーである。

データ型について

  • 主要なデータ型の名前と種類を理解する。

    • 文字型 : CHAR(n), VARCHAR2(n), CLOB
    • 数値型 : NUMBER(p, s)
    • 日付型 : DATE, TIMESTAMP
    • 画像や音声などを格納する型 : BLOB

リレーショナルデータベースでは、データを表形式で管理します。
一つの列には、全ての行で1種類のみを格納することができ、そのデータの種類の指定に用いるのがデータ型です。

データ型には、以下のような種類があります。

データ型の分類

データ型 格納できるデータ
NUMBER(p,s) 整数、浮動小数。pは数値の有効桁数、sは桁数pのうちで小数点以下となる桁数の指定
CHAR(n) 最大2000(バイト数か文字数)までの固定長文字列。nは格納できる最大文字数の指定
VARCHAR2(n) 最大4000(バイト数か文字数)までの可変長文字列。nは格納できる最大文字数の指定
BLOB 最大128テラバイトのバイナリデータ
CLOB 最大128テラバイトの文字列
DATE 日付(年月日時分秒)
TIMESTAMP 小数秒を含む日付(タイムゾーンを含めることが可能)

NUMBER型は、整数及び浮動小数点を含む数値を格納できるデータ型です。
有効桁数は省くことができ、その場合は最大38桁までの指定になります。
また、小数の位取りも可能です。(下何桁を小数にするか)
例としては、有効桁数は4桁で、そのうち小数は1桁にしたいときは、NUMBER(4,1)と指定します。

CHAR型とVARCHAR2型は、文字列を格納するデータ型です。

CHAR型は固定長文字列を扱い、有効桁数で文字列の長さを指定します。
格納する文字列が、有効桁数の文字長に満たない場合は、空白文字で埋められます。
たとえば、CHAR(5)と指定したデータ型に「はい」という文字列を格納すると、「はい   」という文字列として格納されます。
SQLで検索するときも「はい   」と検索しなければヒットしません。

一方、VARCHAR2型は、可変長文字列なので、格納した文字列に空白文字を挿入しません。 たとえば、VARCHAR2(30)として、「はい」という文字列を格納すると、「はい」という文字列として格納されます。 SQLで検索するときは、「はい」と検索するとヒットします。 基本的には、VARCHAR2型を使用します。

BLOB型は、Binary Large Object の略で、画像や音声などのバイナリデータを格納します。
CLOB型は、Character Large Object の略で、CHAR型やVARCHAR2型では対応しきれないような巨大な文字列データを格納したいときに使用します。

DATE型は日付や時刻を格納でき、「年月日時分秒」のデータを格納できます。
TIMESTAMP型は、「年月日時分秒」に加え、小数秒のデータやタイムゾーンも指定して格納することができます。

制約について

  • UNIQUE制約を定義すると、列に一意索引が作成される。
  • PRIMARY KEY制約を定義すると、列(または列の組み合わせ)に一意索引が作成される。
  • 参照されている列の値を更新 / 削除しようとすると、参照先との関連付けが成立するように(整合性を維持するために)エラーになる。
  • UNIQUE制約はNULLを挿入することができる。NULLの挿入をエラーにするのは、NOT NULL制約。
  • PRIMARY KEY制約は、NULLを挿入することができない。また、同一表に複数定義できない。
  • FOREIGN KEY制約は、同じ表または他の表の一位の値(PRIMARY KEY制約またはUNIQUE制約が定義されている列または列の組み合わせ)を参照する。「参照整合性制約」ともいう。
  • CHECK制約は、データベースの値が指定された条件を満たすかどうかを評価する。他の列の値を参照することも可能。
  • 表作成時のルールは次のとおり。

    • オブジェクト名の先端は英字
    • オブジェクト名の長さは30文字以内
    • スキーマ内で名前は一意に保つ。
  • PRIMARY KEY制約、UNIQUE制約を追加する場合、一意索引も作成される。また、既存の値はNULLではなくかつ一意でなければいけない。

制約とは、表に無効なデータが挿入されないようにする仕組みです。 制約は列に対して定義します。
制約の一覧は以下の通りです。

制約の分類

制約 意味 機能
NOT NULL NOT NULL制約 NULL を許可しない
UNIQUE 一意キー制約 NULL 以外の重複を許可しない
PRIMARY KEY 主キー制約 行を一位に識別できる列あるいは列の組み合わせであることを保証する。重複及びNULLは許可しない。表に1つだけ定義できる。
FOREIGN KEY 外部キー制約
参照整合性制約
同一表または他表の列または列の組み合わせを参照する。格納する値が参照先の主キー列または一意キー列に存在しない場合はエラーとする。
CHECK チェック制約 指定した条件を満たす行のみ受け入れる。同一表のたの列を参照することができる。

NOT NULL制約は、値のない状態を許さないという制約です。
NULLとは、「値がない」または「不明である」という状態を指し、これを許可しません。
NOT NULL制約を使用することで、表にデータを挿入、更新するときにNULLを格納しないようにすることができます。

UNIQUE制約とは、列内で値の重複があっては困る場合に使用します。
ただし、NULLを挿入することは可能です。
重複もNULLも許可しない場合は、その列にNOT NULL制約とUNIQUE制約の両方を定義する必要があります。

PRYMARY KEY制約は、行を識別するための列または列の組み合わせで、表の中から特定の1件だけを取り出すための条件となりうる値です。
値の重複もNULLも許可しません。
主キーにふさわしい列は、番号やIDのような列です。
一つの表に定義できるPRYMARY KEY制約は、1つだけです。

FOREIGN KEY制約は、他の表の列などを参照したいときに2つの表を関連付けするときに使用する制約です。
全く関係のない表と表の関連付けを防ぐ役割があります。
FOREIGN KEY制約は、外部キーを格納する列に対して定義します。
この列に格納できるのは、PRIMARY KEY制約が定義された列の値(主キー)か、UNIQUE制約を定義した列の値、あるいはNULLです。
参照されている列の値を更新 / 削除しようとすると、参照先との関連付けが成立するように(整合性を維持するために)エラーになります。

CHECK制約は、定義した条件を満たす値だけを格納するための制約です。 条件は、SQL文で記述する述語(比較演算子)で指定します。

つまり、簡単に言うと以下のようになります。

制約の分類

制約 意味 機能
NOT NULL NOT NULL制約 必ず値を格納する
UNIQUE 一意キー制約 重複値はNG。ただしNULLは許可する
PRIMARY KEY 主キー制約 表の主キー
FOREIGN KEY 外部キー制約
参照整合性制約
(参照先の列に)格納済みの値でなければNG
CHECK チェック制約 条件に合わない値はNG

表の作成について

  • 新しい表の作成時に列にDEFAULTを設定できる。
  • 「索引」の使用目的は、表の問い合わせの高速化。
  • 「索引」は1つの列または列の組み合わせに対して作成できる。
  • PRIMARY KEY制約または、UNIQUE制約を定義すると、制約列に索引が自動的に作成される。
  • 表に行の挿入(INSERT)や削除(DELETE)、索引が定義された列の更新(UPDATE)が行われると索引のメンテナンスが発生し、その分処理が重くなる。
  • 索引を定義した表が削除されると、その索引も削除される。

表の作成時のルールは以下の通り。

  • オブジェクトの先頭は英字。
  • オブジェクトの長さは30文字以内。
  • スキーマ内では名前は一意に保つ。

作成した表の列に対して実行可能な変更は以下の通り。

列に対する変更

制約 意味
変更内容 説明
列の追加 表に行が存在していても可能。追加する列に NOT NULL制約を定義する場合は、表に行が存在しない(0件)の場合、またはDEFAULTオプションを同時に定義する場合のみ可能
列の削除 表に行が存在していても、該当列に値が存在していても可能。
データ型の変更 該当列に値が存在しない(全行NULL)の場合、全てのデータ型に変更可能。該当列に値が存在する場合、次のケースでのみ変更可能 → VARCHAR2 ←→ CHAR
列サイズを大きくする 列に値が存在していても可能
列サイズを小さくする 該当列に値が存在しない(全行NULL)の場合、全てのデータ型で可能。該当列に値が存在する場合、VARCHAR2型のみ変更可能。このとき変更可能なサイズは、すでに格納されている値の最大値まで

列の追加をする場合は、DEFAULTオプションで省略時解釈値を定義していない限りは、追加された列の値はすべてNULLになります。
データを格納した行がある表に、NOT NULL制約付きで列を追加する場合には、

  • DEFAULTオプションで省略時解釈値もいっしょに指定する。
  • データが1行もない表に追加する列には、DEFAULTオプションなしでNOT NULL制約を定義できる。 追加する列は、既存列の最後に追加される。既存の列と列の間に追加することはできない。

列の削除は、行の有無に関わらず、表から列の削除を行うことができます。
ただし、1つしか残っていない最後の列を削除することはできません。
削除した列を復旧することもできません。

データ型の変更は、互換性のあるデータ型の間でのみ行うことができます。
全ての行にデータがない場合は、互換性のないデータ型に変更することも可能です。

互換性のあるデータ型の間での変換

  • CHAR型からVARCHAR2型へ
  • VARCHAR2型からCHAR型へ
  • NUMBER型からVARCHAR2型へ

列サイズを大きくすることは可能です。
列サイズを小さくすることは、表に一行もない場合か、変更対象の列の値が全行でNULLの場合に小さくすることができます。

制約を後から追加することも可能です。
ただし、追加する制約に矛盾するような既存のデータがあってはいけません。
PRYMARY KEY制約、UNIQUE制約を追加する場合は、一意索引も作成されます。
既存の値はNULLでなくかつ一意でなければいけません。

表の変更、削除について

  • 表を削除すると、表構造、データ、全ての制約及び表に定義されている索引が削除される。
  • ゴミ箱はUNDO領域、高速リカバリ領域、SGAの一部ではなく、ゴミ箱のための領域は存在しない。
  • ゴミ箱の実態は、データディクショナリに「その表は削除された」という情報が追加されたこと。
  • ゴミ箱に入れた表を復活させることを「フラッシュバックドロップ」という。
  • フラッシュバックドロップ機能を有効にすることを「RECYCLEBIN初期化パラメータ」をONに設定する。
  • フラッシュバックドロップ機能が有効になっている場合、削除した表(表構造とデータ)と索引は元に戻すことができる。
  • CASCADE CONSTRAINTS を指定すると、削除する表を参照している他表に定義してあるFOREGIN KEY制約も削除される。
  • PURGEを指定するとゴミ箱に入らないので、フラッシュバックドロップによる復活はできない。
  • 表をコピーする場合、コピー元と新しい表の列名は一致していなくて良い。コピーされるのは、表の構造データおよびNOT NULL制約。

表の削除は、データと一緒に削除することができます。
ただし、検索や更新などが行われている表は削除できません。
また、他表からFOREIGN KEY制約で参照されている行が一行でもある場合も削除できません。
表を削除すると、表構造、データ、すべての制約および表に定義されている索引が削除されます。
削除した表に関係するスキーマオブジェクトは以下のようになります。

Oracleにはごみ箱という概念があります。
削除された表はごみ箱に入ります。
ただし、ごみ箱という領域があるわけではありません。
データディクショナリ表に「その表は削除された」という情報が追加されることを「ごみ箱に入る」といいます。
削除された表は、そのまま残っており、「削除された」というフラグで管理されています。
ごみ箱に入っている表は復活させることができます。
「削除されたよ」という情報を消し、削除した表を復活させることをフラッシュバックドロップといいます。
フラッシュバックドロップ機能を有効にするかどうかは、RECYCLEBIN初期化パラメータで設定します。

他表からFOREIGN KEY制約で参照されている行が一行でもある場合、CASCADE CONTSTRAINS を指定することで、表と表を参照しているFPREIGN KEY制約を削除します。
PURGEを指定するとごみ箱には入らないので、フラッシュバックドロップによる復活はできません。

表の複写はCREATE TABLE文にSELECTを含めることでできます。
この時、SELECT句には特定の列だけを指定したり、式や関数を使用することが可能です。
WHERE句を使用することで、条件に一致する行だけをコピーすることもできます。
コピー元の新しい表の列名は一致している必要はありません。
新しい表の作成時に列にDEFAULTを設定できます。
コピーされるのは、表の構造、データおよびNOT NULL制約です。

索引は、スキーマオブジェクトの一つで、条件を満たす行のデータがおかれている位置を拘束に調べることができます。
Bツリータイプの構造で、表の列あるいは列の組み合わせに対して定義する索引です。
ルート(根)→ブランチ(枝)→リーフ(葉)の順番に探索していきます。
最終的に行き着くリーフは、列や列の組み合わせの値と行のデータがおかれている位置(アドレス)が対になった表になっています。
索引は列の組み合わせに対しても作成できます。
また、PRYMARY KEY制約またはUNIQUE制約を定義すると、制約列に索引が自動的に作成されます。

ビューについて

  • 「ビュー」はデータを持たない、ビューに対する更新は、ビューのもととなる表に対して行われる。
  • ビューは、表の特定の列や行だけにアクセスを制限できるため、セキュリティ強化に繋がる。
  • ビューは、複数の表をもとにすることが可能なため、複雑な問い合わせを隠すことができる。
  • ビューに対してDML文(INSERT, UPDATE, DELETE)を実行したときには、ビューが参照している 表に対して値の更新などが行われる。
  • ビューに対してINSERTを実行したときに、NOT NULL制約を定義した列がビューに含まれている場合、エラーになる。
  • ビューが参照する表が削除された場合、そのビューは無効(INVALID)になる。
  • 同一スキーマ内で表と同じ名前のシノニムは作成できない。

ビューは、一つあるいは複数の表に対する問い合わせ(SELECT文)を保存した仮想表ともいわれるスキーマオブジェクトです。
ビューはデータを持ちません。
データアクセスの利便性やセキュリティを向上させることができます。
ビューの定義はデータディクショナリに保存されます。
ビューに対してDML文を実行したときは、ビューのデータにではなく、ビューが参照している表に対して値の更新などが行われます。
NOT NULL制約が定義されている列がビューに含まれていない場合、ビューに対して行った挿入(INSERT)はエラーになります。
ビューが参照する表が削除された場合、そのビューは無効(INVALID)になります。

シノニムとは、表などのスキーマオブジェクトにつける別名のことです。
同一スキーマ内で表と同じ名前のシノニムは作成されません。

ストアドプログラムについて

  • 「ストアドプログラム」はPL/SQL言語で記述されたオブジェクト。
  • ストアドプログラムのタイプは以下の通り。

    • ファンクション : 特定の処理を実行する。呼び出し元に1つだけ値を返すプログラム。
    • プロシージャ : 特定の処理を実行する。呼び出し元に値を返さないプログラム。
    • パッケージ : ファンクションやプロシージャをまとめた構造体、仕様部と本体部がある。
    • トリガー : データベースイベントや表の更新時に自動的に起動して処理を行う。
  • ビューは表を問い合わせるSELECT文を保存したオブジェクトであり、プログラムではない。
  • 「SQL*LOADER」は OS のコマンドラインインターフェイス上でコマンドとして起動することもCloud Controlから使用することができる。
  • ダイレクトパスロードはデータブロックをデータファイルに直接書き込むため Oracle への負荷が大幅に削減される。
  • Data Pump を使用すると異なるプラットフォーム間でのデータの移動ができる。データベースリンクを使用してリモートデータベースから直接ダンプファイルセットにエクスポートできる。

ストアドプログラムとは、ソースコードとコンパイル後のコードがデータベースに格納(ストア)されたプログラムのことを言います。 ストアドプログラムの種類は以下の通りです。 p95

参考書籍

オラクルマスター教科書 Bronze Oracle Database DBA12c "https://amzn.to/3o4ScxL"

今日の一言

第三章が分かったのでOracle DBAはマスターしたも同然!!!!

次回の記事はこちら

ORACLE MASTER Bronze Oracle Database 12c 第四章
"https://0371.blog/oracle-dba-4"