Overblog Suivre ce blog
Administration Créer mon blog
5 mai 2012 6 05 /05 /mai /2012 15:27

 

Créer des Synonyms sur des Objets d'un Schéma Oracle.

Comment créer des Synonyms sur des tables d'un schéma / User dans Oracle.
Comment créer des Synonyms sur les Procédures ou Fonctions d'un schéma dans Oracle.

Syntaxe pour la création de synonymes privés dans Oracle.

 
 CREATE [OR REPLACE] SYNONYM [schéma.]NomSynonym FOR [schéma.]nomObjet [@lienBaseDonnées]; 

 

Création de Synonym sur les Objets Table d'un Schéma ou Utilisateur dans Oracle.

Nous allons créer des synonymes privés sur les Objets Tables appartenant à un utilisateur (ici user Scott) donc un Schéma en s'aidant de la vue DBA_OBJECTS.
Création d'un fichier script que l'on appellera creation-synonym.sql sur C:\ avec les instructions sql ci-dessous.

 
 REM FICHIER SCRIPT creation-synonym.sql******* REM SET HEADING OFF SET ECHO OFF SPOOL 'C:\create-synonym-tables-objects.sql' SELECT 'CREATE OR REPLACE SYNONYM ' ||LOWER(object_name) ||' FOR ' ||LOWER(owner)||'.'||LOWER(object_name) ||';' AS "CREATE TABLE SYNONYM" FROM dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'; SPOOL OFF 

Puis exécution de ce script creation-synonym.sql.
Celui ci va générer un fichier C:\create-synonym-tables-objects.sql que l'on exécutera ensuite via la console Sql*Plus ou simplement en mode copier/coller dans la console.

 
 SQL> @c:\creation-synonym.sql CREATE OR REPLACE SYNONYM b1 FOR scott.b1; CREATE OR REPLACE SYNONYM a1 FOR scott.a1; CREATE OR REPLACE SYNONYM eleve FOR scott.eleve; CREATE OR REPLACE SYNONYM participe FOR scott.participe; CREATE OR REPLACE SYNONYM evaluation FOR scott.evaluation; CREATE OR REPLACE SYNONYM new_emp FOR scott.new_emp; CREATE OR REPLACE SYNONYM vehicule FOR scott.vehicule; CREATE OR REPLACE SYNONYM dept FOR scott.dept; CREATE OR REPLACE SYNONYM bonus FOR scott.bonus; CREATE OR REPLACE SYNONYM salgrade FOR scott.salgrade; CREATE OR REPLACE SYNONYM emp FOR scott.emp; CREATE OR REPLACE SYNONYM table_a FOR scott.table_a; 12 ligne(s) sélectionnée(s). SQL> 

La clause Select de la requête a construit les commandes sql de création de synonym sur toutes les tables du schéma Scott, le tout est spoolé dans le fichier C:\create-synonym-tables-objects.sql.
On exécute celui-ci et les synonymes privés sur les Objets Tables appartenant l'utilisateur Scott seront créés.

 
 SQL> @c:\create-synonym-tables-objects.sql Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. Synonyme créé. SQL> 

Nous aurions pu ajouter la commande Sql*Plus @c:\create-synonym-tables-objects.sql à la fin du fichier script creation-synonym.sql pour une exécution direct en une seule passe comme ceci.

 
 REM FICHIER SCRIPT creation-synonym.sql******* REM SET HEADING OFF SET ECHO OFF SPOOL 'C:\create-synonym-tables-objects.sql' SELECT 'CREATE OR REPLACE SYNONYM ' ||LOWER(object_name) ||' FOR ' ||LOWER(owner)||'.'||LOWER(object_name) ||';' AS "CREATE TABLE SYNONYM" FROM dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'; SPOOL OFF @C:\create-synonym-tables-objects.sql 

 

Création de Synonym sur les Objets Fonctions, Sequences, Packages d'un Schéma ou Utilisateur dans Oracle.

Nous utiliserons le même script pour la création des synonymes privés sur les Objets de type Function, Sequence, Package.
Nous changerons simplement le type d'objet dans AND object_type='TABLE' par AND object_type IN ('FUNCTION','SEQUENCE','PACKAGE').

 REM FICHIER SCRIPT creation-synonym.sql******* REM SET HEADING OFF SET ECHO OFF SPOOL 'C:\create-synonym-objects.sql' SELECT 'CREATE OR REPLACE SYNONYM ' ||LOWER(object_name) ||' FOR ' ||LOWER(owner)||'.'||LOWER(object_name) ||';' AS "CREATE SYNONYM" FROM dba_objects WHERE owner = 'SCOTT' AND object_type IN ('FUNCTION','SEQUENCE','PACKAGE'); SPOOL OFF @C:\create-synonym-objects.sql 

 

Oracle SQL 10G www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• C'est quoi un Synonyme Public et Prive Oracle
• CREATE / DROP SYNONYM
• DROP SYNONYMS invalides
Repost 0
Published by Daniel Roesch - dans SYNONYMS
commenter cet article
1 juin 2011 3 01 /06 /juin /2011 21:53

 

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. 

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• C'est quoi un Synonyme Public et Prive Oracle
• CREATE / DROP SYNONYM
• Dictionnaire de données et Vues DBA-All-User-V$
Repost 0
Published by Daniel Roesch - dans SYNONYMS
commenter cet article
10 mars 2010 3 10 /03 /mars /2010 18:47

 

Création et suppression d'un SYNONYM privé ou public dans Oracle.

Comment créer un synonyme. ?
Comment supprimer un synonyme. ?

Voir article C'est quoi un synonyme Public et prive Oracle?


Création d'un synonym Public

SQL> CREATE [OR REPLACE] [PUBLIC] SYNONYM NomSynonym FOR [schéma.]nomObjet [@lienBaseDonnées];

 

SQL> CREATE PUBLIC SYNONYM site FOR sales.industrie;
SQL> CREATE PUBLIC SYNONYM societe FOR sales.industrie;
SQL> CREATE PUBLIC SYNONYM agence FOR sales.industrie;


Ici on vient de créer 3 synonyms  Public différents (site, société, agence) pour la même table industrie du schéma sales


Création d'un synonym Privé

SQL> CREATE [OR REPLACE] SYNONYM [schéma.]NomSynonyme FOR [schéma.]nomObjet [@lienBaseDonnées];

 

SQL> CREATE SYNONYM sales.taux FOR sales.salaire;
SQL> CREATE SYNONYM sales.primes FOR sales.salaire;


Ici on vient de créer 2 synonyms  Privés différents (taux, primes) dans le schéma sales pour la même table salaire du schéma sales



Suppression d'un synonym avec DROP.

SQL> DROP [PUBLIC] SYNONYM [schéma.]nomSynonyme [FORCE];

 

SQL> DROP PUBLIC SYNONYM site;
SQL> DROP PUBLIC SYNONYM societe;
SQL> DROP PUBLIC SYNONYM agence;
SQL> DROP SYNONYM sales.taux;
SQL> DROP SYNONYM sales.primes;


Nous venons de supprimer les 3 synonyms Publics et les 2 synonyms Privés.
Attention, il vous faut le privilège DROP PUBLIC SYNONYM et/ou DROP ANY SYNONYM.


Interroger la Vue DBA_SYNONYMS  avec un owner <> 'PUBLIC' ou = 'VotreUser' afin de voir les synonyms existants. Voir aussi les vues ALL_SYNONYMS et USER_SYNONYMS.

SQL> SELECT * FROM dba_synonyms WHERE owner <>'PUBLIC';

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• C'est quoi un Synonyme Public et Prive Oracle
• Dictionnaire de données et Vues DBA-All-User-V$
• Oracle Script Lister les SYNONYMES invalides
Repost 0
Published by Daniel Roesch - dans SYNONYMS
commenter cet article
10 mars 2010 3 10 /03 /mars /2010 08:24

 

Gerer les synonyms Publics et Privés - Private.

C'est quoi un SYNONYM. ?

C'est un Alias sur un Objet de la base ou Schéma, une sorte de raccourcis.
L'Objet peut être une Table, une Vue, une Séquence, une Procédure, une Fonction, un Package.
Le synonym peut être Public ou Privé.
Public, il sera accessible à partir de tous schéma et user.
Privé il sera accessible uniquement à partir du schéma dans lequel il a été créé.

Pourquoi créer des synonyms?

- Masquer le vrai nom des objets et leur localisations.
- Simplifier les noms des objets.
- Éviter le pré-fixage dans les requêtes avec le nom de son propriétaire.

Création d'un synonym avec CREATE SYNONYM.

Tout d'abord vous devez avoir le privilège
- CREATE SYNONYM pour créer un synonym Privé dans votre schéma.
- CREATE ANY SYNONYM pour créer un synonym Privé dans n'importe quel schéma.
- CREATE PUBLIC SYNONYM pour créer un synonym Public.

 

SQL> GRANT CREATE SYNONYM TO User ;

SQL> GRANT CREATE ANY SYNONYM TO User ;

SQL> GRANT CREATE PUBLIC SYNONYM TO User ;



Attention:
La création de synonym ne donne pas de droit sur l'objet.
Oracle regardera si l'utilisateur accédant au synonym possède les privilèges nécessaires ( SELECT , INSERT, UPDATE, DELETE, EXECUTE).

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON MaTable TO User ;

SQL> GRANT ALL ON MaTable TO User ;

SQL> GRANT EXECUTE ON MaFonction TO User ;


Si vous possédez le privilège SELECT ANY TABLE alors vous avez implicitement les privilèges sur les Objets.

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• CREATE / DROP SYNONYM
• Dictionnaire de données et Vues DBA-All-User-V$
• Oracle Script Lister les SYNONYMS invalides
Repost 0
Published by Daniel Roesch - dans SYNONYMS
commenter cet article