「Oracle」カテゴリーアーカイブ

Oracle:COALESCE関数の使い方

oracleの独自関数のCOALESCE(コアレス)関数の用途についてメモしておきます。

COALESCE関数とは

COALESCE関数は引数のリストから最初のNULL以外の値を返却するOracle独自関数となります。

NVL2関数との違いは必ずしも引数へ指定するデータ型を全て合わせる必要はないことです。

ただデータの一貫性を保ちたい場合は極力データ型は合わせた方が望ましいです。

サンプルテーブル

  • サンプルテーブル「CLIENT_ADDRESS」

COALESCE関数の使用例

  • SQL(クエリー)例

    上記例ではSTART_DATEがNULLであればEND_DATEの値を、END_DATEもNULLであればsysdateを返却します。

  • 実行結果

Oracle:NVL関数とNVL2関数の違い

oracleの独自関数としてNVL関数やNVL2関数があります。

知ってると結構便利な関数なので、この2つの関数の違いについて整理しておきます。

NVL関数とは

NVL関数は第1引数がNULLなら第2引数の値(代替値)を返します。

もし第1引数の結果がNULLでなければ、そのまま第1引数の値を返します。

注意点として第1引数と第2引数へは同じデータ型を指定する必要があります。

NVL関数の使用例

  • サンプルテーブル「CLIENT_ADDRESS」
  • SQL(クエリー)例
    以下の例ではEND_DATEがNULLでない場合はEND_DATEの値を、NULLの場合はsysdateを返却します。
  • 実行結果

NVL2関数とは

NVL2関数は第1引数がNULLの場合に、第2引数の値を返却し、第1引数がNULLの場合は第3引数の値を返却します。

注意点として第1引数、第2引数、第3引数に指定する値は全て同じデータ型を指定する必要があります。

NVL2関数の使用例

  • サンプルテーブル「CLIENT_ADDRESS」
  • SQL(クエリー)例
    以下の例ではEND_DATEがNULLでない場合は第2引数のSTART_DATEの値を返却し、END_DATEがNULLの場合は第3引数のsysdateを返却します。
  • 実行結果

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
    (メモリーマネージャ)
    メモリーを必要な場所に動的に割り当てる

 

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. 再度プロセス数とセッション数を確認すると上限が変更されているのが確認出来ます。セッション数は明示的に変更しなくてもプロセス数の変更に比例して上限が増えます。

Oracle:年、月、日、時、分、秒以下を切り捨てて取得する方法

OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。

使用例

サンプルテーブル「BIRTHDAY」

クエリー(SQL)

  • BIRTHDAYテーブルのUPDATE_DATEに対してTRUNC関数を使用した例となります。第2引数へformatを指定することで指定した単位で切り捨てた値を取得することが出来ます。
  • formatの種類 
    format説明
    YYYY年まで取得して月日時分秒を切り捨て
    MM年月まで取得して日時分秒を切り捨て
    DD年月日まで取得して時分秒を切り捨て
    HH年月日時まで取得して分秒を切り捨て
    MI年月日時分まで取得して秒を切り捨て
     

出力結果

 

Oracle:管理者ユーザー「SYS」と「SYSTEM」のデフォルトパスワード

Oracle Databaseをインストールすると自動的に作成される管理者ユーザー「SYS」と「SYSTEM」のデフォルトパスワードについてメモしておきます。

SYSユーザーのデフォルトパスワード:change_on_install

  • sysユーザーでのログイン例です。

SYSTEMユーザーのデフォルトパスワード:manager

  • systemユーザーのログイン例です。

SQL:システム日付と誕生日を元に年齢、誕生日までの月数、日数を表示する方法

SQLでシステム日付、誕生日を使用して年齢、システム日付~誕生日までの月数、日数を取得するSQLをメモしておきます。

以下はOracleでの実行例となっています。

サンプルテーブル

「BIRTHDAY」テーブル

【Oracle】システム日付と誕生日から年齢、誕生日までの月数、日数を表示する例

クエリー(SQL)例

実行結果