Identifier les requêtes multi-schémas.

Bonjour,

J'ai actuellement une base de données (bdd1) avec plusieurs schémas (s1,s2,etc). Je prévois de déplacer un schéma (s1) dans une nouvelle base de données (bdd2).

Or vu l'historique je ne suis pas à l'abri que certains schémas (s2, etc) interrogent des données du schéma migré (s1), et donc poser des problèmes lors de la migration.

J'essaie donc d'identifier toutes les requêtes qui contiennent dans la clause FROM au moins une table de s1 et au moins une table de s2 ou tout autre schéma.

Autant dire je suis parti dans une analyse syntaxique à base de instr, substr, de la vue sys.wrh$_SQLTEXT , analyse dont je ne me sors que très difficilement vue la complexité de certaines requêtes contenant des requêtes imbriquées :aie:

A la lecture de la problématique, avez-vous des conseils / pistes à me donner ? 8O

Merci

Pourquoi ne pas déporter le problème à l'envers en laissant le schéma dans la base et en substituant des vues aux tables actuelles, vues pointant sur l'autre base dans laquelle vous avez déporté les tables ?

Vous n'auriez alors rien à analyser et aucune requête à récrire !

Donc fait en quelques minutes !

A +

C'est en effet le plan B (obligeant l'usage de dblink ce que nous voulons évité).

Nous aurions aimé en profiter pour "contrôler" les accès.

Ha c'est vrai que les requêtes multibase sous Oracle sont assez merdiques via dblink...

Si vous étiez sous MS SQL Server... Mais ce serait une autre histoire !

A +

Vos requêtes sont générées par les applications. C'est plutôt de ce coté que vous devez rechercher.
Par contre si votre application respecte le concept de "thick database" alors c'est plus simple vos requêtes sont dans la base.

PS. Il est vrai que chez Oracle toute chose qui n'est pas maitrise par celui qui veut l'utiliser devient vite merdique!

Pas simple comme problème en effet !

Une chose est sûre, c'est que l'analyse syntaxique des requêtes, outre sa complexité, n'est pas une bonne voie.
Il suffit qu'un synonyme ou une vue fasse référence à un objet d'un autre schéma pour que la requête échappe à ce genre de recherche.

La solution proposée par SQL Pro a l'avantage d'être systématique et d'éviter tout trou dans la raquette.

Pour se faire une idée des requêtes impliquant plusieurs schémas, mon idée première est d'observer les choses par V$SQL_PLAN.
On a la colonne OBJECT_OWNER qui indique le schéma impliqué pour chaque opération du plan. Cette vue travaillant au niveau des objets finaux réels, on est à l'abri du biais des vues ou synonymes.

Code:

---

1 2 3 4 5 6 7

select sql_id, sql_text from v$sqlarea where sql_id in (select sql_id from v$sql_plan where object_owner is not null group by sql_id having count(distinct object_owner)>1);

---

On peut imaginer d'exécuter cette requête tous les quarts d'heure pendant une semaine et d'en stocker le résultat, ce qui devrait donner une excellente vision, mais sans qu'on puisse garantir son exhaustivité.

Citation:

---

Envoyé par mnitu

PS. Il est vrai que chez Oracle toute chose qui n'est pas maitrise par celui qui veut l'utiliser devient vite merdique!

---

Ca c'est ben vrai, ça !:ptdr:

Bonjour,
sys.wrh$_SQLTEXT n'est pas une bonne idée car seules les requêtes coûteuses sont répertoriées.
La fonctionnalité Oracle à utiliser est AUDIT pour tracer tout ce qui fait des select sur les tables de ce schema.
Cordialement,
Franck.

Depuis la fin de la semaine dernière j'ai laissé l'analyse syntaxique, je suis plus parti sur une requête de ce type :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

select distinct req1.*, a1.synonym_name, a1.table_owner, a1.table_name, a2.synonym_name, a2.table_owner, a2.table_name from ( SELECT SQL_ID as sql_id , DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 1) as c1 FROM sys.wrh$_SQLTEXT w where ( (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 1)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 4000)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 8000)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 12000)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 16000)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 20000)) like '%FROM%') or (upper(DBMS_LOB.SUBSTR ( SQL_TEXT, 4000, 24000)) like '%FROM%') ) ) req1 , all_synonyms a1, all_synonyms a2 where req1.c1 like ('%' || a1.synonym_name || '%') and req1.c1 like ('%' || a2.synonym_name || '%') and a1.table_owner in ('S1') and a1.owner = 'PUBLIC' and a2.table_owner not in ('S1','SYS','SYSTEM','SYSMAN','PERFSTAT') and a2.owner = 'PUBLIC' ;

---

Citation:

---

sys.wrh$_SQLTEXT n'est pas une bonne idée car seules les requêtes coûteuses sont répertoriées.
La fonctionnalité Oracle à utiliser est AUDIT pour tracer tout ce qui fait des select sur les tables de ce schema.

---

Ha ! Je note. En effet c'est logique, dans le sens où c'est l'historique AWR et que les AWR ne relèvent que les requêtes couteuses.

Le problème ça n'est pas que les tables, il peut aussi y avoir des vues ou des procédures stockées.

Je commencerais par analyser les accès aux différents objets de S1 avec les vues du dictionnaire (par exemple dba_tab_privs).