「データベース」カテゴリーアーカイブ

Oracle:事前構成済の表領域

Oracleではデータベースを作成すると自動的に以下の表領域が作成されます。

事前構成済の表領域

 

表領域説明
SYSTEMOracleサーバーがデータベースを管理するために使用する表領域
SYSAUXSYSTEM表領域の補助表領域
TEMPデータベースのデフォルトの一時表領域
UNDOTBS1UNDO表領域
USERS永続表領域
SYSやSYSTEMユーザー以外のユーザー用のデフォルト表領域
EXAMPLEデータベース作成時に「サンプル・スキーマの作成」を指定すると作成される表領域
 

Oracle:データベースの起動段階と状態について

Oracleデータベースを起動すると内部的に以下の段階でデータベースが使用可能な状態となります。

データベースの起動段階と状態

 

段階ステータス状態
1SHUTDOWNデータベースが停止している状態
2NOMOUNTインスタンスが起動している状態
※制御ファイル、データファイル、REDOログファイルはクローズ
3MOUNTインスタンスが起動し、制御ファイルがオープンしている状態
※データファイル、REDOログファイルはクローズ
4OPENインスタンスが起動し、制御ファイル、データファイル、REDOログ・ファイルもオープンしている状態
※一般ユーザーが使用可能な状態
 

Oracle:制約の種類

Oracleデータベースでは以下の制約を設定することが出来ます。

Oracle DB:制約の種類

制約説明
NOT NULL制約NULL値の設定を許可しない
UNIQE制約
(一意キー制約)
一意の値のみ許可します。複数レコード登録時、重複する値を許可しません。NULL値は許可します。
CHECK制約指定した条件の値のみ許可します。
PRIMARY KEY制約
(主キー制約)
一意の値のみ許可し、重複する値は許可しません。NULL値も許可しません。
FOREIGN KEY制約
(外部キー制約)
指定した親テーブルに存在する値のみ許可します。
 

制約のポイント

  • 制約はCREATE TABLE または ALTER TABLEで設定出来ます。
  • 制約設定の構文には列制約構文と表制約構文の2種類あります。
  • NOT NULL制約は列制約構文でのみ設定出来ます。

制約の設定例

  • 下記例のNOT NULL制約の設定箇所が列制約構文、UNIQUE制約の設定箇所が表制約構文となります。

 

Oracle:プロセス構造の仕組み

Oracle DBのプロセス構造は大きく「ユーザープロセス」、「サーバープロセス」、「バックグラウンドプロセス」があります。

ユーザープロセス

  • クライアントからSQLを発行すると発生するプロセスです。

サーバープロセス

  • クライアントが発行したSQLを処理するするのがメインのプロセスです。

バックグラウンドプロセス

  • サーバープロセスがSQL管理に専念させるため、他のプロセスを監視するためのプロセスです。Oracleインスタンスを起動することで自動で起動します。
  • 主なバッググランドプロセスの種類
    種類説明
    DBWn
    (データベースライター)
    データベースバッファキャッシュ内の変更されたデータをデータファイルへ書き込む
    LGWR
    (ログライター)
    REDOログバッファにあるREDOログをREDOログファイルへ書き込む
    CKPT
    (チェックポイント)
    チェックポイント情報を制御ファイルとデータファイルヘッダーへ書き込む
    SMON
    (システムモニター)
    インスタンスのリカバリを実行する
    PMON
    (プロセスモニター)
    ユーザープロセス障害からリカバリを実施する
    ARCn
    (アーカイバー)
    REDOログファイルのコピー(アーカーブファイル)を作成する
    REDO
    (リカバラ)
    分散トランザクションに関する障害を自動的に解決する。
    MMON
    (マネージメントモニター)
    メモリー内の統計情報をスナップショットとして定期的にデーターベースへ格納する。
    MMAN
    (メモリーマネージャ)
    メモリーを必要な場所に動的に割り当てる

 

SQL:任意の順序でソート(並べ替え)する方法

SQLでは特定の項目の昇順、降順ではなく任意の順序で並べ替えて取得することも出来ます。

任意の順序でソートするにはORDER BY句でCASE文を指定する事で取得することが出来ます。

任意の順でソートする例

サンプルテーブル

「BIRTHDAY」テーブル

クエリー(SQL)

出力結果

SQL:xx日後、xxヶ月後、xx年後などの日付を取得する方法

SQLでsysdateなどの日付型へ加算、減算してxx日後、xxヶ月後、xx年後を求める方法をメモしておきます。

xx秒後、xx秒前を求める方法

クエリー(SQL)例

  • システム日付の30秒後と30秒前を取得する例です。

実行結果

 

xx分後、xx分前を求める方法

クエリー(SQL)例

  • システム日付の5分後と5分前を取得する例です。

実行結果

 

xx時間後、xx時間前を求める方法

クエリー(SQL)例

  • システム日付の2時間後と2時間前を取得する例です。

実行結果

 

xx日後、xx日前を求める方法

クエリー(SQL)例

  • システム日付の1日後と1日前を取得する例です。

実行結果

 

xxヶ月後、xxヶ月前を求める方法

クエリー(SQL)例

  • システム日付の1ヶ月後と1ヶ月前を取得する例です。ADD_MONTHS関数は1/31の1ヶ月後は2/28となるように上手く月末日を調整してくれます。

実行結果

 

xx年後、xx年前を求める方法

クエリー(SQL)例

  • システム日付の1年後と1年前を取得する例です。ADD_MONTHS関数に12の倍数を指定することで年単位での加減算が可能となります。

実行結果

Oracle:最大プロセス数や最大セッション数を変更する方法

Oracleでのセッションやプロセス数には上限があり、それを超えてしまうとデータベースにアクセス出来ずに予期せぬエラー(ORA-12519)が発生するなどの不具合が発生してしまいます。

Oracleで最大プロセス数や最大セッション数は初期化パラメータを管理しているSPFILEに定義されています。変更前にSPFILEのバックアップを取得しておくのをオススメします。

SPFILEの配置場所

Oracle DB 18cの場合の例です。

  • 配置位置:[ORACLE_HOME]/database/SPFILE[ORACLE_SID].ORA
    (例)C:\ORACLE\WINDOWS.X64_180000_db_home\database\SPFILEORCL.ORA

最大プロセス数、最大セッション数の変更方法

  1. 最初に「V$SYSTEM_PARAMETER」から現在の最大プロセス数と最大セッション数の設定値を確認します。「V$SYSTEM_PARAMETER」はインスタンスに現在有効になっている初期化パラメータ情報を示します。

  2. 次にALETER文でプロセス数の上限を変更します。
  3. SPFILEの変更はOracleを再起動しないと適用されないため、Oracle DBを再起動します。
    詳細は「Oracle Database(Oracleサーバ)の再起動(停止・起動)手順」参照
  4. 再度プロセス数とセッション数を確認すると上限が変更されているのが確認出来ます。セッション数は明示的に変更しなくてもプロセス数の変更に比例して上限が増えます。

DBeaver:テーブルに登録済のレコードをinsert文形式に変換して取得する方法

データベースのクライアントソフトのDBeaverには便利な機能がいくつもありますが、その内の機能の一つにテーブルに登録済のレコードをinsert文形式に変換して取得出来る機能があります。

開発などでIT環境などに登録済のデータをlocal環境へデータコピーしたい場合などに使える便利な機能の一つです。SQL形式(insert文)以外にもCSVやHTML、XML形式などにも変換出来るので様々な用途に応用可能となります。

DBeaverで登録済のレコードをinsert文形式に変換してい取得する方法

  1. コピーしたいテーブルのデータを表示してinsert文へ変換したいレコードを選択します。
  2. 右クリックして「高度なコピー」⇒「Copy as SQL」を選択するとinsert文形式でのコピーは完了です。
  3. 以下の様にコピーした内容を貼り付けてみるとinsert文形式へ変換されているのが確認出来ます。

 

SQL:NULLの判定方法

SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。

使用例

サンプルテーブル

「GOODS」

NULLのデータを抽出する例

クエリー(SQL)

実行結果

NULL以外のデータを抽出する例

クエリー(SQL)

実行結果

SQL:SELECT時にCASE文で条件指定する方法

SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。

使用例

サンプルテーブル

「GOODS」

クエリー(SQL)

  • 以下の例ではCASE文でGOODS_CODEが特定の値と一致した場合、NAME列に文字列を付与してTEST列として出力しています。

出力結果