Oracleスキーマを操作するSQL PlusコマンドとOracleパラメータファイル

本記事のポイント

Oracle ClientはOracel Serverに「サーバー→リスナー→データベース」の順番に接続し、PCにOracleサーバーとクライアントを両方インストールすると、サーバーのNet Configuration AssistantやNet Managerは無効になり、構成パラメータファイルはクライアントのものを使用し、サーバーのもの(dbhome_1フォルダ内のもの)は使用しません。

tnsnames.oraファイルの構成パラメターをローカルネーミングパラメター、listener.oraファイルの構成パラメターはリスナーパラメーター、sqlnet.oraファイルの構成パラメータをネーミングメソッドパラメータと呼びます。

Oracleスキーマオブジェクトの構成

Oracleのスキーマオブジェクトは主に以下のオブジェクトで構成されています。パッケージやプロシージャーやファンクションは他のOracleオブジェクト(SQL Plusなど)やユーザーアプリケーション(VBなど)からCallできます。

  1. テーブル:
  2. インデックス:
  3. ビュー:
  4. パッケージ:プロシージャーまたはファンクションのライブラリ
  5. プロシージャー:ユーザープログラム(戻り値なし)
  6. ファンクション:ユーザープログラム(戻り値あり)
  7. トリガー:テーブル操作をトリガーに自動実行されるプロシージャー

スキーマにログイン

まずSQLPlusを起動しsys権限のユーザでOracle DB(スキーマ)にログインして行います。

Windows Serverなら問題ないですがUNIX上ではコマンドラインで入力されたIDとPWDは「ps -ef | grep sqlplus」であっさり表示されてしまうので、SQLPlusは/nolog付きで起動してインスタンスに繋ぎに行ったほうが良さそうです。

スキーマの作成と削除

スキーマTEST2を作成し権限を付与するためのスクリプトにUIDとPWDを引数として実行します。

スキーマを削除するには、そのスキーマーがオブジェクトを持っている場合でも削除できるようにcascadeを付けます。

cascadeとは「滝のように上から下へ流れる」という意味であり、スキーマに含むオブジェクトまでごっそり削除というニュアンスです。CSSもCascading Style Sheetの略で、上位のセレクタに適用したプロパティの値は下位のセレクタにも適用されます。

リコンパイル

スキーマに含まれている無効なPL/SQLモジュール、無効なビュー、Javaクラス、索引タイプおよび演算子など、スキーマーの全オブジェクトを対象としてリコンパイルするにはDBMS_UTILITYパッケージのCOMPILE_SCHEMAプロシージャー、またはUTL_RECOMPパッケージを使います。

ストアドプロシージャーの実行はEXEC(UTE)またはCALLで行います。

UTL_RECOMPパッケージの場合、スキーマ名を省略すると全スキーマをまとめてリコンパイルできます。

インポートとエクスポート

従来(論理)バックアップには「C:\app\HP\product\11.2.0\client_1\BIN」内のimp.exeとexp.exeを使用していました。

Oracle11gからimp/expコマンドはサポートされなくなり、その代わりにデータポンプDataPump(expdp/impdb)が使えるようになりました。imp/expとの違いはユーティリティ側で処理されるかデータベース側でジョブとして処理されるかであり、imp/expコマンドに比べてパフォーマンスが向上しています。

Oracleクライアントからサーバーへの接続時のエラー

Oracle ClientはOracel Serverに「サーバー→リスナー→データベース」の順番に接続していきますが、僕の場合Oracleの接続エラーで一番多いのはリスナーに関係するものであり、中でもエラーの常習犯が以下のORA-12514です。

error

これは「クライアントのtnsnames.oraのHOSTパラメータで指定したホスト名(またはIPアドレス)上で、tnsnames.oraのPORTパラメータで指定したポート番号でサーバーのリスナーが起動していない(Listenしていない)」ということです。

PCにOracleサーバーとクライアントを両方インストールすると、サーバーのNet Configuration AssistantやNet Managerは無効になります。よってこの場合に設定するtnsnames.oraとlistener.oraはクライアントのものであり、サーバーのもの(dbhome_1フォルダ内のもの)は使用しません。

ローカルネーミングパラメータtnsnames.ora

  • C:\app\HP\product\11.2.0\client_1\network\admin\tnsnames.ora
sqlplusからorclというデータベースサービスにアクセスするには「localhostというサーバーのポート番号1521でリスナーが起動」していればよいわけです。

リスナーパラメータlistener.ora

  • C:\app\HP\product\11.2.0\client_1\network\admin\listener.ora
「HP-PC(localhostでもOK)というホスト名のポート番号1521でリスナーが起動」していればよいわけです。

今動いているデータベースサービス名やポート番号はサーバー上から以下のコマンドで確認の上、パラメタファイルと比較します。

lsnrctl

ネーミングメソッドパラメータsqlnet.ora

Oracle ClientからOracle Server DBの接続は、サーバーとクライアント両方のOracle Net(アプリケーション層のプロトコル)を通じて、リスナーと接続できるようにします。Oracle Netの構成では、ネーミングメソッドパラメータsqlnet.oraでOracle Server DBの識別方法を設定します。

  • C:\app\HP\product\11.2.0\client_1\network\admin\sqlnet.ora
TNSNAMESはローカルネーミングファイルtnsnames.oraを使った接続で、EZCONNECTはネーミングパラメータを使わず、以下の簡易接続するものです。

sqlplus

tnsnames.oraファイルの構成パラメターをローカルネーミングパラメター、listener.oraファイルの構成パラメターはリスナーパラメーター、sqlnet.oraファイルの構成パラメータをネーミングメソッドパラメータと呼びます。