Oracleデータベースのシノニム（Synonym）を基礎から実務までやさしく解説

Oracle Master Silver
2024.11.06

導入

「アプリのSQLからスキーマ名を消したい」「環境ごとに参照先テーブルを切り替えたい」「DBリンク経由の長い参照を書きたくない」——そんな時に役立つのが シノニム（Synonym） です。
本記事では、シノニムの仕組み・種類・作成方法から、DBリンクとの連携、運用上の注意点、よくあるエラーの対処まで、実務でそのまま使える形でまとめます。サンプルSQLは ユーザー作成・権限付与・テーブル作成・INSERT を含めて網羅します。

なお、本記事ではSCOTTサンプルスキーマを使用した例で解説します。
SCOTTサンプルスキーマは以下を実行することでインポートおよび使用が可能です。

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
Oracle入門：サンプルスキーマとは？SCOTTスキーマを使った学習環境の整え方
Oracleデータベースを学習する際、最も効果的な方法の一つが「サンプルスキーマ」を使った実践です。この記事では、サンプルスキーマの基礎知識から代表的なスキーマの種類、インストール方法までを図を交えてわかりやすく解説します。 💰 【PR】O...
oracle-master00000.com
2024.09.23

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

本文

シノニムとは

シノニム（Synonym） は、テーブル／ビュー／シーケンス／PL/SQL オブジェクトなどに付ける 別名 です。
長いオブジェクト名やスキーマ修飾を隠蔽し、SQLの可読性・移植性を高めます。シノニム自体には権限は付与できず、基になるオブジェクトへの権限が必要 である点に注意します。

文字で分かるイメージ図

（実体）SCOTT.EMP  ──────┐
                         │   ← 別名（Synonym）
（別名）EMP_SYN     ──────┘

種類：プライベートとパブリック

  • プライベート・シノニム：作成したユーザーだけが利用できます（同一スキーマ内）。
    例）CREATE SYNONYM emp_syn FOR scott.emp;
  • パブリック・シノニム：データベース内の全ユーザーが利用可能。ただし 各ユーザーは基礎オブジェクトの権限が必要。作成には管理的な権限が要ります。

参照解決の優先順位（重要）

オブジェクト名解決は概ね 自スキーマ → プライベート・シノニム → パブリック・シノニム の順に行われます。名前衝突を避ける設計が大切です。

解決順序
┌──────────────┐
│ 1. 自スキーマ │ 例: EMP（自分の表）
├──────────────┤
│ 2. Private   │ 例: EMP（別名）= SCOTT.EMP
├──────────────┤
│ 3. Public    │ 例: EMP（別名）= SCOTT.EMP
└──────────────┘

作成・管理の基本構文

  • 作成：CREATE [OR REPLACE] [PUBLIC] SYNONYM 別名 FOR スキーマ.オブジェクト[@DBLINK];
  • 削除：DROP [PUBLIC] SYNONYM 別名;

必要権限の要点

  • 自スキーマのプライベート・シノニム：CREATE SYNONYM
  • 他スキーマにプライベート・シノニムを作成：CREATE ANY SYNONYM
  • パブリック・シノニム：CREATE PUBLIC SYNONYM（管理者向け）
    （いずれも基になるオブジェクトに対する SELECT 等の利用権限は別途必要）

DBリンク × シノニムで参照を短縮

DBリンクで SCOTT.EMP@REMOTE_DB_LINK のような長い参照も、シノニムで EMP_REMOTE に短縮できます。アプリSQLは環境を意識せずに同じ書き味で利用できます。

【ローカルDB】                 【リモートDB】
┌──────────┐      DBリンク      ┌──────────┐
│ APPユーザー │ ───────────▶ │ SCOTT.EMP │
└──────┬───┘                   └──────────┘
       │（別名）
       └─ EMP_REMOTE（Synonym）

ビューでの確認・依存関係

  • 一覧確認：USER_SYNONYMS / ALL_SYNONYMS / DBA_SYNONYMS
  • 依存関係：USER_DEPENDENCIES（参照先の有無確認に有用）

活用パターン

  1. スキーマ隠蔽（アプリSQLの簡素化）
    アプリ側は SELECT * FROM CUSTOMER; のまま、環境ごとに CUSTOMER シノニムの参照先を差し替え。
  2. 環境切替（本番・検証）
    参照先のみを入替えて同一SQLを流用。
  3. DBリンク参照の短縮
    宛先やネットワーク情報をアプリから隠蔽。

機能を使用したときのメリット・デメリット

メリット

  • SQLの可読性・保守性が向上（スキーマやDBリンクを隠蔽）
  • 環境差分の吸収（参照先切替が容易）
  • オブジェクト名変更時もアプリ改修を最小化

デメリット／注意点

  • 名前衝突のリスク（解決順序を理解し命名規約を徹底）
  • パブリック・シノニムの乱用はグローバル名前空間を汚染し混乱を招きやすい（推奨はプライベート中心）。
  • 権限は別管理：シノニム自体に権限は付与できないため、基のオブジェクトへ適切な権限付与が必須。

実行例（ユーザー作成・権限・テーブル作成・INSERTを含む）

前提：PDBに接続済み（必要に応じてALTER SESSION SET CONTAINER=...）。
例では 所有者 OWNER1、利用者 APP1 を作成します。

1) ユーザー作成と権限付与（DBAで実行）

-- 所有者ユーザー
CREATE USER owner1 IDENTIFIED BY "Owner1#Pass"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO owner1;

-- 利用者ユーザー
CREATE USER app1 IDENTIFIED BY "App1#Pass"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO app1;

-- シノニム作成権限（プライベート）
GRANT CREATE SYNONYM TO app1;

-- パブリック・シノニムを試す場合は管理者でのみ（必要時のみ）
-- GRANT CREATE PUBLIC SYNONYM TO SYSTEM;  -- 例：SYSTEMなど管理ユーザーで

2) テーブル作成とサンプルデータ（OWNER1で実行）

-- テーブル作成（所有者）
CONN owner1/"Owner1#Pass"

CREATE TABLE dept(
  deptno NUMBER(2)   PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp(
  empno    NUMBER(4)   PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)   REFERENCES dept(deptno)
);

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO emp(empno,ename,job,deptno,hiredate,sal)
VALUES (7369,'SMITH','CLERK',20,DATE '1980-12-17',800);
INSERT INTO emp(empno,ename,job,deptno,hiredate,sal)
VALUES (7566,'JONES','MANAGER',20,DATE '1981-04-02',2975);
COMMIT;

3) 利用権限を付与（OWNER1→APP1）

GRANT SELECT ON owner1.emp  TO app1;
GRANT SELECT ON owner1.dept TO app1;

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

4) プライベート・シノニムの作成と利用（APP1で実行）

CONN app1/"App1#Pass"

-- 別名（自スキーマ）を作成
CREATE SYNONYM emp_syn  FOR owner1.emp;
CREATE SYNONYM dept_syn FOR owner1.dept;

-- 以後はスキーマ修飾なしで参照可能（権限があるため）
SELECT empno, ename, job FROM emp_syn ORDER BY empno;
SELECT deptno, dname     FROM dept_syn ORDER BY deptno;

【実行例】

SQL> show user
USER is "APP1"
SQL> SELECT empno, ename, job FROM emp ORDER BY empno;
SELECT empno, ename, job FROM emp ORDER BY empno
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

  --★他スキーマのテーブルなのでスキーマ名を付けないとselectできない


SQL> SELECT empno, ename, job FROM owner1.emp ORDER BY empno;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7566 JONES      MANAGER

  --★スキーマ名を付けるとselectできる

SQL> SELECT deptno, dname     FROM dept ORDER BY deptno;
SELECT deptno, dname     FROM dept ORDER BY deptno
                              *
ERROR at line 1:
ORA-00942: table or view does not exist

  --★他スキーマのテーブルなのでスキーマ名を付けないとselectできない


SQL> SELECT deptno, dname     FROM owner1.dept ORDER BY deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

  --★スキーマ名を付けるとselectできる

SQL> SELECT empno, ename, job FROM emp_syn ORDER BY empno;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7566 JONES      MANAGER

SQL> SELECT deptno, dname     FROM dept_syn ORDER BY deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

  --★シノニムを使用してselect

5) パブリック・シノニムの作成（必要時のみ、DBAで慎重に）

-- 管理者で作成（乱用は避ける）
CREATE PUBLIC SYNONYM dept FOR owner1.dept;

-- どのユーザーでも SELECT 可能に見えるが、
-- 実際は owner1.dept への個別の権限付与（またはロール付与）が必須

6) DBリンク × シノニム（上級）

例：APP1 からリモートDBの SCOTT.EMP を参照する

-- （tnsnames.ora で REMOTE_SVC を解決できる前提）
CONN app1/"App1#Pass"

CREATE DATABASE LINK remote_db_link
  CONNECT TO scott IDENTIFIED BY tiger
  USING 'REMOTE_SVC';

-- リモートのSCOTT.EMPへ別名を定義
CREATE SYNONYM emp_remote FOR scott.emp@remote_db_link;

-- ローカル表と同じ書き味で参照できる
SELECT empno, ename FROM emp_remote WHERE job = 'MANAGER';

DBLINKについてはコチラ。

Oracle DBLINKについて
1. DBLINKとはDBLINK（データベースリンク）は、あるOracleデータベースから別のOracleデータベースに接続し、データを参照したり操作を行うためのリンクです。特に、異なるシステムやデータベースを連携させる必要がある際に便利...
oracle-master00000.com
2024.11.07

7) 管理（置換・削除）

-- 参照先変更（再定義）
CREATE OR REPLACE SYNONYM emp_syn FOR owner1.emp_archive;

-- 削除
DROP SYNONYM emp_syn;
-- パブリックの場合
-- DROP PUBLIC SYNONYM dept;

よくある質問（トラブルシューティング）

Q1. ORA-00980: シノニム変換が無効です。
A. 参照先オブジェクトが無い／権限が無い場合に発生。
　→ 参照先の存在確認・権限付与・DBリンクの疎通確認・参照先変更（CREATE OR REPLACE）で解消。

Q2. ORA-01775: シノニム定義がループしています。
A. シノニムが互い（あるいは自己）に指し合ってループしている状態。
　→ 片方（または両方）を正しい実体に向けて再作成、もしくは不要なシノニムを削除。

Q3. シノニムに権限は付与できる？
A. できません。権限は 基のオブジェクト に対して付与します。

Q4. パブリックとプライベート、どちらを選ぶ？
A. 原則は プライベート推奨。パブリックは名前空間汚染や影響範囲拡大のリスクがあるため、必要最小限に。

Q5. 参照解決順序はどこを確認？
A. ドキュメントに明記。自スキーマ→プライベート→パブリックの順で解決されます。

まとめ（要点）

  • シノニムは 別名 であり、権限は 実体側で管理。
  • 種類は プライベート／パブリック。原則はプライベート推奨。
  • 解決順序 を理解し、命名規約で衝突回避。
  • DBリンク×シノニム で参照の短縮と環境切替が容易。
  • 運用では USER/ALL/DBA_SYNONYMS と依存関係の確認を習慣化。
  • 代表的なエラー（ORA-00980, ORA-01775）は参照先・設計の見直しで解決。


[参考]
SQL言語リファレンス – CREATE SYNONYM

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

コメント

タイトルとURLをコピーしました