Oracle DBのプロセス構造は大きく「ユーザープロセス」、「サーバープロセス」、「バックグラウンドプロセス」があります。
ユーザープロセス
- クライアントからSQLを発行すると発生するプロセスです。
サーバープロセス
- クライアントが発行したSQLを処理するするのがメインのプロセスです。
バックグラウンドプロセス
- サーバープロセスがSQL管理に専念させるため、他のプロセスを監視するためのプロセスです。Oracleインスタンスを起動することで自動で起動します。
- 主なバッググランドプロセスの種類
| 種類 | 説明 |
DBWn
（データベースライター） | データベースバッファキャッシュ内の変更されたデータをデータファイルへ書き込む |
LGWR
（ログライター） | REDOログバッファにあるREDOログをREDOログファイルへ書き込む |
CKPT
（チェックポイント） | チェックポイント情報を制御ファイルとデータファイルヘッダーへ書き込む |
SMON
（システムモニター） | インスタンスのリカバリを実行する |
PMON
（プロセスモニター） | ユーザープロセス障害からリカバリを実施する |
ARCn
（アーカイバー） | REDOログファイルのコピー（アーカーブファイル）を作成する |
REDO
（リカバラ） | 分散トランザクションに関する障害を自動的に解決する。 |
MMON
（マネージメントモニター） | メモリー内の統計情報をスナップショットとして定期的にデーターベースへ格納する。 |
MMAN
（メモリーマネージャ） | メモリーを必要な場所に動的に割り当てる |
SQLでは特定の項目の昇順、降順ではなく任意の順序で並べ替えて取得することも出来ます。
任意の順序でソートするにはORDER BY句でCASE文を指定する事で取得することが出来ます。
任意の順でソートする例
サンプルテーブル
「BIRTHDAY」テーブル
クエリー（SQL）
|
|
FROM BIRTHDAY b ORDER BY CASE b.NAME WHEN '佐藤 花子' THEN 1 WHEN '石川 一' THEN 2 WHEN '山田 太郎' THEN 3 END; |
出力結果
SQLでsysdateなどの日付型へ加算、減算してxx日後、xxヶ月後、xx年後を求める方法をメモしておきます。
xx秒後、xx秒前を求める方法
クエリー（SQL）例
- システム日付の30秒後と30秒前を取得する例です。
|
|
SELECT sysdate "本日の日付", sysdate + 30/86400 "本日の日付 + 30秒", sysdate - 30/86400 "本日の日付 - 30秒" FROM dual; |
実行結果
xx分後、xx分前を求める方法
クエリー（SQL）例
- システム日付の5分後と5分前を取得する例です。
|
|
SELECT sysdate "本日の日付", sysdate + 5/1440 "本日の日付 + 5分", sysdate - 5/1440 "本日の日付 - 5分" FROM dual; |
実行結果
xx時間後、xx時間前を求める方法
クエリー（SQL）例
- システム日付の2時間後と2時間前を取得する例です。
|
|
SELECT sysdate "本日の日付", sysdate + 2/24 "本日の日付 + 2時間", sysdate - 2/24 "本日の日付 - 2時間" FROM dual; |
実行結果
xx日後、xx日前を求める方法
クエリー（SQL）例
- システム日付の1日後と1日前を取得する例です。
|
|
SELECT sysdate "本日の日付", TO_CHAR(sysdate + 1,'YYYY-MM-DD') "本日の日付 + 1日", TO_CHAR(sysdate - 1,'YYYY-MM-DD') "本日の日付 - 1日" FROM dual; |
実行結果
xxヶ月後、xxヶ月前を求める方法
クエリー（SQL）例
- システム日付の1ヶ月後と1ヶ月前を取得する例です。ADD_MONTHS関数は1/31の1ヶ月後は2/28となるように上手く月末日を調整してくれます。
|
|
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 1),'YYYY-MM-DD') "本日の日付 + 1ヶ月", TO_CHAR(ADD_MONTHS(sysdate, -1),'YYYY-MM-DD') "本日の日付 - 1ヶ月" FROM dual; |
実行結果
xx年後、xx年前を求める方法
クエリー（SQL）例
- システム日付の1年後と1年前を取得する例です。ADD_MONTHS関数に12の倍数を指定することで年単位での加減算が可能となります。
|
|
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 12),'YYYY-MM-DD') "本日の日付 + 1年", TO_CHAR(ADD_MONTHS(sysdate, -12),'YYYY-MM-DD') "本日の日付 - 1年" FROM dual; |
実行結果
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
最大プロセス数、最大セッション数の変更方法
- 最初に「V$SYSTEM_PARAMETER」から現在の最大プロセス数と最大セッション数の設定値を確認します。「V$SYSTEM_PARAMETER」はインスタンスに現在有効になっている初期化パラメータ情報を示します。
|
|
SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER WHERE NAME IN('processes', 'sessions'); |
- 次にALETER文でプロセス数の上限を変更します。
|
|
ALTER SYSTEM SET PROCESSES = 1000 SCOPE=SPFILE; |
- SPFILEの変更はOracleを再起動しないと適用されないため、Oracle DBを再起動します。
詳細は「Oracle Database（Oracleサーバ）の再起動（停止・起動）手順」参照
- 再度プロセス数とセッション数を確認すると上限が変更されているのが確認出来ます。セッション数は明示的に変更しなくてもプロセス数の変更に比例して上限が増えます。
|
|
SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER WHERE NAME IN('processes', 'sessions'); |
SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。
使用例
サンプルテーブル
「GOODS」
NULLのデータを抽出する例
クエリー（SQL）
|
|
SELECT * FROM GOODS g WHERE GOODS_CODE IS NULL; |
実行結果
NULL以外のデータを抽出する例
クエリー（SQL）
|
|
SELECT * FROM GOODS g WHERE GOODS_CODE IS NOT NULL; |
実行結果
SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。
使用例
サンプルテーブル
「GOODS」
クエリー（SQL）
- 以下の例ではCASE文でGOODS_CODEが特定の値と一致した場合、NAME列に文字列を付与してTEST列として出力しています。
|
|
SELECT NAME, GOODS_CODE, (CASE GOODS_CODE WHEN 101 THEN '【PC】' || NAME WHEN 201 THEN '【DESK】' || NAME ELSE '【OTHER】' || NAME END) test FROM GOODS g; |
出力結果
OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。
使用例
サンプルテーブル「BIRTHDAY」
クエリー（SQL）
- BIRTHDAYテーブルのUPDATE_DATEに対してTRUNC関数を使用した例となります。第2引数へformatを指定することで指定した単位で切り捨てた値を取得することが出来ます。
|
|
SELECT TRUNC(b.UPDATE_DATE, 'YYYY'), TRUNC(b.UPDATE_DATE, 'MM'), TRUNC(b.UPDATE_DATE, 'DD'), TRUNC(b.UPDATE_DATE, 'HH'), TRUNC(b.UPDATE_DATE, 'MI') FROM BIRTHDAY b; |
- formatの種類
| format | 説明 |
| YYYY | 年まで取得して月日時分秒を切り捨て |
| MM | 年月まで取得して日時分秒を切り捨て |
| DD | 年月日まで取得して時分秒を切り捨て |
| HH | 年月日時まで取得して分秒を切り捨て |
| MI | 年月日時分まで取得して秒を切り捨て |
出力結果
Oracle Databaseをインストールすると自動的に作成される管理者ユーザー「SYS」と「SYSTEM」のデフォルトパスワードについてメモしておきます。
SYSユーザーのデフォルトパスワード：change_on_install
SYSTEMユーザーのデフォルトパスワード：manager
SQLでシステム日付、誕生日を使用して年齢、システム日付～誕生日までの月数、日数を取得するSQLをメモしておきます。
以下はOracleでの実行例となっています。
サンプルテーブル
「BIRTHDAY」テーブル
【Oracle】システム日付と誕生日から年齢、誕生日までの月数、日数を表示する例
クエリー（SQL）例
|
|
SELECT TO_CHAR(sysdate,'YYYY-MM-DD') "本日の日付", TO_CHAR(b.BIRTHDAY,'YYYY-MM-DD') "誕生日", EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM b.BIRTHDAY) "年齢", TRUNC(MONTHS_BETWEEN(sysdate,b.BIRTHDAY)) "誕生日までの月数", TRUNC(sysdate - b.BIRTHDAY) "誕生日までの日数" FROM dual, BIRTHDAY b; |
実行結果
UPDATEで他のテーブルと結合（JOIN）した結果に対してのみ更新を実施したい場合、SQL ServerやMySQL、PostgreSQLではUPDATE文内に直接JOIN句を記載可能ですが、Oracleでは副問合せで結合することでJOINした結果に対して更新することが可能となります。
サンプルテーブル
「GOODS」テーブル
「GOODS_TYPE」テーブル
【Oracle】UPDATE文でJOINを使用して更新する例
クエリー（SQL）例
- 以下の例ではGOODS_TYPEテーブルに存在するGOODS_CODEと一致するレコードのみGOODSテーブルのPRICE項目を更新しています。
|
|
UPDATE GOODS SET PRICE = 5000 WHERE GOODS_CODE = ANY (SELECT g.GOODS_CODE FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE) |
実行結果
- 以下の様にGOODS_CODEが一致しているレコードのみ更新されていることが確認できます。
【SQL Server、MySQL、PostgreSQL】UPDATE文でJOINを使用して更新する例
クエリー（SQL）例
- SQL Serverなどでは直接UPDATE文内にJOINを指定可能なので以下のような記載でもエラーにはならず更新されます。
|
|
UPDATE g SET r.PRICE = 5000; FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE |
「駑馬十駕」を信念に IT系情報を中心に調べた事をコツコツ綴っています。
