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 ------------------------------