Oracle Script Lister les SYNONYMS invalides

Mercredi 1 juin 2011 3 01 /06 /Juin /2011 21:53

Oracle Script Lister les SYNONYMS invalides

Oracle Script Lister les synonyms invalides.

ORA-04045: errors during recompilation/revalidation
ORA-00980: synonym translation is no longer valid
ORA-01775: looping chain of synonyms.

En utilisant les vues DBA_SYNONYMS et DBA_OBJECTS nous allons pouvoir détecter des incohérences et voir les synonymes qui n'ont plus leur objets en références, et ainsi faire des DROP SYNONYM.

Trouver et lister les synonyms invalides.

SQL> COLUMN OWNER FORMAT A8
SQL> COLUMN SYNONYM_NAME FORMAT A30
SQL> COLUMN TABLE_OWNER FORMAT A13
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SET LINESIZE 110
SQL>
SQL>   SELECT   S.OWNER,
  2             S.SYNONYM_NAME,
  3             S.TABLE_OWNER,
  4             S.TABLE_NAME
  5      FROM   DBA_SYNONYMS S
  6     WHERE   S.TABLE_OWNER NOT IN ('SYS', 'SYSTEM') AND S.DB_LINK IS NULL
  7             AND S.TABLE_NAME NOT IN
  8                      (SELECT   OBJECT_NAME
  9                         FROM   DBA_OBJECTS O
 10                        WHERE       O.OBJECT_NAME = S.TABLE_NAME
 11                                AND O.OWNER = S.TABLE_OWNER
 12                                AND O.OBJECT_TYPE NOT LIKE '%SYNONYM%')
 13  ORDER BY   OWNER ASC;

OWNER    SYNONYM_NAME                   TABLE_OWNER   TABLE_NAME
-------- ------------------------------ ------------- ------------------------------
PUBLIC   SDO_AVAILABLE_ELEM_OPS         MDSYS         SDO_AVAILABLE_ELEMENTARY_OPS
PUBLIC   TOTO                           SYSADM        TOTO
PUBLIC   AGENCE                         SYSADM        AGENCE
PUBLIC   DBMS_AW_UTILITIES              OLAPSYS       DBMS_AW_UTILITIES
PUBLIC   TMP_PHYSIOLOGIE                SYSADM        TMP_PHYSIOLOGIE
PUBLIC   X_SAV_PAI_MELANGE_B            SYSADM        X_SAV_PAI_MELANGE_B
PUBLIC   X_SAV_PAI_MELANGE_A            SYSADM        X_SAV_PAI_MELANGE_A
PUBLIC   X_SAV_PAI_MELANGE_BA_D         SYSADM        X_SAV_PAI_MELANGE_BA_D
PUBLIC   X_SAV_PAI_MELANGE_ST           SYSADM        X_SAV_PAI_MELANGE_ST
PUBLIC   X_SAV_PAI_STOCK_ATEL           SYSADM        X_SAV_PAI_STOCK_ATEL
PUBLIC   X_SAV_PAI_STOCK_TR             SYSADM        X_SAV_PAI_STOCK_TR

OWNER    SYNONYM_NAME                   TABLE_OWNER   TABLE_NAME
-------- ------------------------------ ------------- ------------------------------
PUBLIC   X_SAV_T_FICHE_EXPLOITATION     SYSADM        X_SAV_T_FICHE_EXPLOITATION
PUBLIC   X_SAV_PAI_BA                   SYSADM        X_SAV_PAI_BA
PUBLIC   X_SAV_PAI_LOT_T                SYSADM        X_SAV_PAI_LOT_T
PUBLIC   X_SAV_PAI_LOT_T2               SYSADM        X_SAV_PAI_LOT_T2
PUBLIC   T_EXTRACTION_ENCOURS_C         SYSADM        T_EXTRACTION_ENCOURS_C
PUBLIC   T_EXTRACTION                   SYSADM        T_EXTRACTION
PUBLIC   SAV_LIG_PREF_WF_MOINS_1        SYSADM        SAV_LIG_PREF_WF_MOINS_1
PUBLIC   SAV_LIG_PR_DET_WF_MOINS_1      SYSADM        SAV_LIG_PR_DET_WF_MOINS_1
PUBLIC   SAV_PREF_WF_MOINS_1            SYSADM        SAV_PREF_WF_MOINS_1
PUBLIC   SAV_PROV_ENT_WF_MOINS_1        SYSADM        SAV_PROVT_ENT_WF_MOINS_1
PUBLIC   SAV_SERVCERT_20050630          SYSADM        SAV_SERVCERT_20050630

OWNER    SYNONYM_NAME                   TABLE_OWNER   TABLE_NAME
-------- ------------------------------ ------------- ------------------------------
PUBLIC   SAV_T_DEC_SEGAN                SYSADM        SAV_T_DEC_SEGAN
PUBLIC   TEMP_CNRS                      SYSADM        TEMP_CNRS
PUBLIC   TMA_LIG_PREF                   SYSADM        TMA_LIG_PREF
PUBLIC   TMA_T_PROVTAIL                 SYSADM        TMA_T_PROVTAIL
SYSADM   ANALYSE_2                      WINFACT       ANALYSE_2
SYSADM   PHYSIOLOGIE_2                  WINFACT       PHYSIOLOGIE_2

28 rows selected.

Construction des DROP synonyms invalides.

SQL> SELECT      'DROP '
  2             || DECODE (S.OWNER, 'PUBLIC', 'PUBLIC SYNONYM ', 'SYNONYM ')
  3             || DECODE (S.OWNER, 'PUBLIC', '', S.OWNER || '.')
  4             || S.SYNONYM_NAME
  5             || ';' "SQL STATEMENT INVALID SYNONYMS"
  6      FROM   DBA_SYNONYMS S
  7     WHERE   S.TABLE_OWNER NOT IN ('SYS', 'SYSTEM') AND S.DB_LINK IS NULL
  8             AND S.TABLE_NAME NOT IN
  9                      (SELECT   OBJECT_NAME
 10                         FROM   DBA_OBJECTS O
 11                        WHERE       O.OBJECT_NAME = S.TABLE_NAME
 12                                AND O.OWNER = S.TABLE_OWNER
 13                                AND O.OBJECT_TYPE NOT LIKE '%SYNONYM%')
 14  ORDER BY   OWNER ASC;

SQL STATEMENT INVALID SYNONYMS
-----------------------------------------------------------------------------
DROP PUBLIC SYNONYM SDO_AVAILABLE_ELEM_OPS;
DROP PUBLIC SYNONYM TOTO;
DROP PUBLIC SYNONYM AGENCE;
DROP PUBLIC SYNONYM DBMS_AW_UTILITIES;
DROP PUBLIC SYNONYM X_SAV_PAI_MELANGE_B;
DROP PUBLIC SYNONYM X_SAV_PAI_MELANGE_A;
DROP PUBLIC SYNONYM X_SAV_PAI_MELANGE_BA_D;
DROP PUBLIC SYNONYM X_SAV_PAI_MELANGE_ST;
DROP PUBLIC SYNONYM X_SAV_PAI_STOCK_ATEL;
DROP PUBLIC SYNONYM X_SAV_PAI_STOCK_TR;
DROP PUBLIC SYNONYM X_SAV_T_FICHE_EXPLOITATION;

SQL STATEMENT INVALID SYNONYMS
-----------------------------------------------------------------------------
DROP PUBLIC SYNONYM X_SAV_PAI_BA;
DROP PUBLIC SYNONYM X_SAV_PAI_LOT_T;
DROP PUBLIC SYNONYM X_SAV_PAI_LOT_T2;
DROP PUBLIC SYNONYM T_EXTRACTION_ENCOURS_C;
DROP PUBLIC SYNONYM T_EXTRACTION;
DROP PUBLIC SYNONYM SAV_LIG_PREF_WF_MOINS_1;
DROP PUBLIC SYNONYM SAV_LIG_PR_DET_WF_MOINS_1;
DROP PUBLIC SYNONYM SAV_PREF_WF_MOINS_1;
DROP PUBLIC SYNONYM SAV_PROV_ENT_WF_MOINS_1;
DROP PUBLIC SYNONYM SAV_SERVCERT_20050630;
DROP PUBLIC SYNONYM SAV_T_DEC_SEGAN;

SQL STATEMENT INVALID SYNONYMS
-----------------------------------------------------------------------------
DROP PUBLIC SYNONYM TEMP_CNRS;
DROP PUBLIC SYNONYM TMA_LIG_PREF;
DROP PUBLIC SYNONYM TMA_T_PROVTAIL;
DROP PUBLIC SYNONYM TMP_PHYSIOLOGIE;
DROP SYNONYM SYSADM.ANALYSE_2;
DROP SYNONYM SYSADM.PHYSIOLOGIE_2;

28 rows selected.