1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Oracleのシノニム切り替えについて

Last updated at Posted at 2020-10-06

あるアプリでOracleのテーブルを参照しており、その参照元テーブルを洗い替えで更新したい場合、
シノニムを使用することによりアプリに影響がないように更新が可能です。

例えば、下記の図のような構成の場合を想定。

スクリーンショット 2020-10-06 12.02.45.png

現在のシノニム確認

SELECT * FROM ALL_SYNONYMS
WHERE SYNONYM_NAME = 'HOGE_TABLE';

+----------------+---------------+----------------+---------------+-----------+
| OWNER          | SYNONYM_NAME  | TABLE_OWNER    | TABLE_NAME    | DB_LINK   |
+----------------+---------------+----------------+---------------+-----------+
| SAMPLE_SCHEMA  | HOGE_TABLE    | SAMPLE_SCHEMA  | HOGE_TABLE_1  | (null)    |
+----------------+---------------+----------------+---------------+-----------+

シノニムで参照されていないスタンバイ側のテーブルを洗い替え

TRUNCATE TABLE SAMPLE_SCHEMA.HOGE_TABLE_2;

INSERT INTO SAMPLE_SCHEMA.HOGE_TABLE_2
SELECT * FROM SAMPLE_SCHEMA.SOURCE_TABLE;

スクリーンショット 2020-10-06 12.05.27.png

シノニム切り替え

CREATE OR REPLACE SYNONYM SAMPLE_SCHEMA.HOGE_TABLE
FOR SAMPLE_SCHEMA.HOGE_TABLE_2;

スクリーンショット 2020-10-06 12.06.29.png

切り替え後のシノニム確認

SELECT * FROM ALL_SYNONYMS
WHERE SYNONYM_NAME = 'HOGE_TABLE';

+----------------+---------------+----------------+---------------+-----------+
| OWNER          | SYNONYM_NAME  | TABLE_OWNER    | TABLE_NAME    | DB_LINK   |
+----------------+---------------+----------------+---------------+-----------+
| SAMPLE_SCHEMA  | HOGE_TABLE    | SAMPLE_SCHEMA  | HOGE_TABLE_2  | (null)    |
+----------------+---------------+----------------+---------------+-----------+
1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?