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

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)例

実行結果

Oracle:DESCRIBEコマンドでテーブル(表)構造を確認する方法

SQL PlusやSQL Developerに用意されている「DESCRIBE」コマンドを使用することでテーブル構造を確認することが出来ます。

DESCRIBEコマンドはDESCと省略することも出来ます。

SQL PlusでのDESCRIBEコマンド実行例

  • 「DESCRIBE テーブル名」でテーブル構造が確認できます。

SQL PlusでのDESCコマンド実行例

  • DESCでもDESCRIBEと同じ結果を取得できます。

Oracle:接続時に「ORA-01109:データベースがオープンされていません。」エラーが出た場合の原因と対応方法

DBクライアントソフトなどでOracle DBへ接続時に「ORA-01109:データベースがオープンされていません。」が表示された場合の原因と対応方法についてメモしておきます。

「ORA-01109:データベースがオープンされていません。」の原因

  • 文字通り接続しようとしているDBがオープンされていないのが原因となります。今回接続先を「ORCLPDB」に指定していましたがそこがオープンされていないようです。
  • 念のためSQL Plusを起動して「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認するとやはりオープンされていませんでした。

「ORA-01109:データベースがオープンされていません。」の対処方法

  1. SQL Plusで「alter pluggable database ORCLPDB open;」コマンドを実行し、ORCLPDBをオープンします。
  2. 「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認します。
  3. これでORCLPDBのオープンだけなら完了していますが、このままPCを再起動するとまた再びクローズ状態に戻ってしまいます。ですので最後に現在オープンされている全てのPDBを自動起動(オープン)するように「alter pluggable database all save state;」コマンドで保存します。

 

SQL:WHERE句内で「(+)」を指定する意味

SQLを見てるとたまにWHERE句内で「(+)」と記載されているのを見かけることがあります。

この「(+)」はOracle独自で記載が可能となる外部結合演算子といって、「g.GOODS_CODE = gt.GOODS_CODE(+)」のように指定するとLEFT OUTER JOINと同じ結果を取得することが出来ます。

サンプルテーブル

  • 「GOODS」テーブル
  • 「GOODS_TYPE」テーブル

外部結合演算子を利用したクエリー(SQL)例

通常の左外部結合でのクエリー(SQL)例

実行結果

外部結合演算子、左外部結合どちらも同じ結果となります。

補足

  • 「(+)」演算子の歴史的背景
    Oracle の古いバージョンからサポートされていた外部結合の書き方で、ANSI SQL 標準が普及する以前によく使われていました。ANSI 標準(LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN)が登場して以降は、可読性・移植性の観点から推奨されないことが多いです。

  • 制限・注意点

    1. 可読性・保守性g.GOODS_CODE = gt.GOODS_CODE(+) のような記述は、JOIN の方向やどのテーブルに外部結合をかけているかが分かりにくく、後でクエリを読む人が迷いやすいです。

    2. 複雑な結合:複数テーブルを結合する場合、どこに (+) を付けるか、また付け方によって結果が変わるため、注意深く設計する必要があります。

    3. NULL の扱い:外部結合によって NULL が返る行が含まれますが、条件節で他の条件と組み合わせると意図しない行の除外が起きることがあります(例えば WHERE 節で AND 条件として別の非 NULL 条件を加えると外部結合の意味が薄れる等)。

  • ANSI 標準との違い

    項目 (+) 外部結合演算子 ANSI SQL 外部結合 (LEFT OUTER JOIN 等)
    可読性やや分かりにくい比較的分かりやすい
    移植性Oracle に依存多くの RDBMS でサポートされる
    複雑な JOIN の組み立てエラーや誤解の元になりやすいON 句で明示的に指定できるため制御しやすい
    サポート状況最新 Oracle でも非推奨の方向推奨される方式
     

  • 現代のベストプラクティス

    • 新規開発では ANSI SQL の外部結合構文を使うことを推奨。可読性や他 DBMS への移植性が高いため。

    • 既存システムで (+) を使っているなら、リファクタリングやドキュメント整備を行い、「なぜこの構文になっているか」「どのような影響があるか」を明らかにしておく。

    • テストを用意して、クエリ変更後に結果が同じになることを検証する(特に NULL の扱いや結合の漏れが起きていないか)。

  • 実際の書き換え例

    旧構文を ANSI 標準に書き換えるときの例をもう一つ:

     
    -- 旧構文

    -- 書き換え後(ANSI 標準)

    このように、結合条件を ON に書き出すことで構造が明瞭になります。

  • 互換性・移行のヒント

    • Oracle のバージョンによっては (+) 構文が将来削除される可能性があり、非推奨の警告が出ることもあります。

    • 他の RDBMS(PostgreSQL、MySQL、SQL Serverなど)で同じSQLを使うことを想定するなら、ANSI JOINに統一しておくと移行がスムーズです。

    • クエリのパフォーマンスにも注意。古い構文でも Oracle のオプティマイザが合理化する場合がありますが、可視性や保守性を取る方が長期的には有利。

Oracle:ユーザー作成時に「ORA-65096」エラーが出た場合の原因と対応方法

Oracleのインストール後にSQL*Plusなどでユーザー作成しようとした際、「ORA-65096」エラーが発生した場合の原因と対応方法についてメモしておきます。

「ORA-65096:共通ユーザーまたはロール名が無効です」の原因

  • ルートコンテナにローカルユーザーを作成しようとした場合に発生するエラーとなります。
    ルートコンテナには共有ユーザー(common user) と呼ばれる特殊なユーザーしか作成することはできません。
    Oracle 11gまでと違いOracle 12c以降からは一つのインスタンスには一つのコンテナ・データベース(CDB)と、プラガブル・データベース(PDB)と呼ばれる子DBが存在しています。sysなどのユーザーでログイン直後はコンテナ・データベース(CDB)に接続されている状態となっているため、そのままローカルユーザーを作成しようとしてもエラーが発生してしまうということになります。

「ORA-65096:共通ユーザーまたはロール名が無効です」の対処方法

原因が分かってしまえば対応はシンプルです。接続先がコンテナ・データベース(CDB)であるのがまずいのであればプラガブル・データベース(PDB)に変更してしまえばいいだけです。

  1. まずは「show con_name;」で現在接続されているデータベースを確認します。
  2. 次に「select name, open_mode from v$pdbs;」でPDBの名前と現在のOPEN_MODEを確認します。
  3. PDBの名前が「ORCLPDB」というのがわかったのでデータベースの接続先を「ORCLPDB」へ変更します。
  4. もう一度「show con_name;」を実行して接続先が変更されていることを確認します。
  5. 接続先がPDBへ変更されたのでもう一度ユーザー作成を実行すると正常に実行されます。