Oracle CREATE SYNONYM 文取得

```SQL:get_ddl_synonyms.sql
-- #!/bin/sh
-- #@(#) \$Id get_ddl_synonyms.sh
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/usr/app/oracle/product/12.1.0
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
--
--  CREATE SYNONYM 文取得
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 250
SET PAGESIZE 50000
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL get_ddl_synonyms_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL get_ddl_synonyms_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN CREATE_SYNONYM  FORMAT A250
SELECT 'CREATE OR REPLACE '
    || CASE
           WHEN A.OWNER = 'PUBLIC' THEN
               A.OWNER || ' '
       END
    || 'SYNONYM '
    || CASE
           WHEN A.OWNER = 'PUBLIC' THEN
               RPAD(A.SYNONYM_NAME, 30)
           ELSE                                   -- ↓OWNER.SYNONYM_NAMEの長さは現場対応
               RPAD(A.OWNER || '.' || A.SYNONYM_NAME, 45)
       END
    || ' FOR ' || A.TABLE_OWNER || '.' || A.TABLE_NAME || NVL2(A.DB_LINK, '@' || A.DB_LINK, NULL) || ' ;' AS CREATE_SYNONYM
FROM(
    SELECT 
        SY.OWNER
      , SY.SYNONYM_NAME
      , SY.TABLE_OWNER
      , SY.TABLE_NAME
      , SUBSTR(SY.DB_LINK, 1, INSTR(SY.DB_LINK, '.') -1) AS DB_LINK
--    , DECODE(OB.STATUS, 'INVALID', OB.STATUS, ' ') AS STATUS
--    , NVL(RO.OBJECT_TYPE, 'NOTFOUND') AS OBJECT_TYPE  -- NOTFOUND:対象のオブジェクトが存在しない。
                                                           -- SYNONYMだけが残っているのでDROP SYNONYMすべし。
      , DECODE(RO.STATUS, 'INVALID', RO.STATUS) AS T_STATUS
    FROM(
        SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA)  -- ユーザーが作成したスキーマのみ
        ) MS
    INNER JOIN DBA_SYNONYMS SY
    ON    SY.TABLE_OWNER = MS.OWNER
    LEFT OUTER JOIN DBA_OBJECTS OB
    ON    OB.OWNER = MS.OWNER
    AND   OB.OWNER = SY.OWNER
    AND   OB.OBJECT_TYPE = 'SYNONYM'
    AND   OB.OBJECT_NAME = SY.SYNONYM_NAME
    LEFT OUTER JOIN DBA_OBJECTS RO
    ON    RO.OWNER       = SY.TABLE_OWNER
    AND   RO.OBJECT_NAME = SY.TABLE_NAME
    AND   RO.OBJECT_TYPE NOT IN
        (
        'INDEX'         -- TABLE とダブるので除外
      , 'PACKAGE BODY'
      , 'TYPE BODY'
        )
    WHERE NOT EXISTS  -- except trash box, since Oracle10.1.0
        (
        SELECT NULL
        FROM DBA_RECYCLEBIN RB
        WHERE OB.OWNER       = MS.OWNER
        AND   RB.OWNER       = OB.OWNER
        AND   RB.TYPE        = OB.OBJECT_TYPE
        AND   RB.OBJECT_NAME = OB.OBJECT_NAME
        )
    ) A
ORDER BY 
    A.OWNER
  , A.SYNONYM_NAME
  , A.TABLE_OWNER
  , A.TABLE_NAME ;
SPOOL OFF
COLUMN CREATE_SYNONYM  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80
SET TERMOUT ON
SET TIMING OFF
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of get_ddl_synonyms.sh ------------------------------
```
参照
[Oracle 純粋にユーザー側のスキーマのみを取得](https://qiita.com/Papageno/items/2d4ccae4b99d79d0b48b)