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

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

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

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

スキーマにログイン

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

C:\sqlplus sys/admin@orcl as sysdba

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

C:\sqlplus /nolog

SQL> connect sys/admin@orcl as sysdba
Connected.

スキーマの作成と削除

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

set verify off
spool &2
create user &1 identified by &1
default tablespace users
temporary tablespace temp;
grant dba to &1;
grant connect to &1;
grant create any trigger to &1;
grant create any sequence to &1;
grant create procedure to &1;
grant unlimited tablespace to &1;
grant alter rollback segment to &1;
grant select on v_$session to &1;
grant select on v_$mystat to &1;
grant execute on dbms_lock to &1;
grant select on v_$instance to &1;
spool off
quit
SQL> @D:/createuser.sql TEST2 TEST2
User created.

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

drop user TEST2 cascade;

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

リコンパイル

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

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

EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('TEST2');

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

EXECUTE UTL_RECOMP.RECOMP_SERIAL('TEST2');  
EXECUTE UTL_RECOMP.RECOMP_SERIAL(); 

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

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

imp TEST2/TEST2@orcl file=D:\test2.dmp log=d:\temp\TEST2.log full=y

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

expdp test2/test2@orcl schemas=demo dumpfile=DEMO.dmp logfile=DEMO_exp.log
impdp test2/test2@orcl remap_schema=demo dumpfile=DEMO.dmp logfile=DEMO_imp.log

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
# tnsnames.ora Network Configuration File: C:\app\HP\product\11.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

sqlplusからorclというデータベースサービスにアクセスするには「localhostというサーバーのポート番号1521でリスナーが起動」していればよいわけです。

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

  • C:\app\HP\product\11.2.0\client_1\network\admin\listener.ora
# listener.ora Network Configuration File: C:\app\HP\product\11.2.0\client_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = Oracle8)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HP-PC)(PORT = 1521))
  )

ADR_BASE_LISTENER = C:\app\HP\product\11.2.0\client_1\log

「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
# sqlnet.ora Network Configuration File: C:\app\HP\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSNAMESはローカルネーミングファイルtnsnames.oraを使った接続で、EZCONNECTはネーミングパラメータを使わず、以下の簡易接続するものです。

sqlplus

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