127 votes

Recherche d'une valeur spécifique dans tous les champs de toutes les tables (Oracle)

Est-il possible de rechercher une valeur particulière dans chaque champ de chaque table dans Oracle ?

Il y a des centaines de tables avec des milliers de lignes dans certaines tables, donc je sais que cela pourrait prendre beaucoup de temps pour effectuer une requête. Mais la seule chose que je sais, c'est que la valeur du champ sur lequel j'aimerais effectuer la requête est la suivante 1/22/2008P09RR8 . <

J'ai essayé d'utiliser l'instruction ci-dessous pour trouver une colonne appropriée en fonction de ce que je pense qu'elle devrait être nommée, mais cela n'a donné aucun résultat.

SELECT * from dba_objects 
WHERE object_name like '%DTN%'

Il n'existe absolument aucune documentation sur cette base de données et je n'ai aucune idée de l'origine de ce champ.

Des idées ?

0 votes

Peut-on faire cela en utilisant une seule requête au lieu d'utiliser une procédure stockée ?

0 votes

0 votes

@LalitKumarB La page que vous avez indiquée n'est plus accessible. Serait-il possible de poster quelques informations en guise de réponse ?

103voto

Dave Costa Points 25282

Citation :

J'ai essayé d'utiliser l'instruction ci-dessous pour trouver une colonne appropriée en fonction de ce que je pense qu'elle devrait être nommée mais cela aucun résultat.*

SELECT * from dba_objects WHERE
object_name like '%DTN%'

Une colonne n'est pas un objet. Si vous voulez dire que vous vous attendez à ce que le nom de la colonne soit du type '%DTN%', la requête que vous voulez est la suivante :

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';

Mais si la chaîne "DTN" n'est qu'une supposition de votre part, cela ne servira probablement à rien.

À propos, êtes-vous certain que "1/22/2008P09RR8" est une valeur sélectionnée directement dans une seule colonne ? Si vous ne savez pas du tout d'où elle provient, il peut s'agir d'une concaténation de plusieurs colonnes, du résultat d'une fonction ou d'une valeur située dans un objet de table imbriqué. Vous risquez donc de vous lancer dans une course folle en essayant de vérifier cette valeur dans chaque colonne. Ne pouvez-vous pas commencer par l'application client qui affiche cette valeur et essayer de déterminer la requête qu'elle utilise pour l'obtenir ?

Quoi qu'il en soit, la réponse de diciu donne une méthode de génération de requêtes SQL pour vérifier la valeur de chaque colonne de chaque table. Vous pouvez également faire des choses similaires entièrement dans une session SQL en utilisant un bloc PL/SQL et du SQL dynamique. Voici un code écrit à la hâte pour cela :

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT owner, table_name, column_name
                  FROM all_tab_columns
                  WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
          ' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING '1/22/2008P09RR8';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /

Il existe également des moyens de la rendre plus efficace.

Dans ce cas, étant donné la valeur que vous recherchez, vous pouvez clairement éliminer toute colonne de type NUMERO ou DATE, ce qui réduirait le nombre de requêtes. Vous pouvez même vous limiter aux colonnes dont le type est '%CHAR%'.

Au lieu d'une requête par colonne, vous pourriez construire une requête par table comme ceci :

SELECT * FROM table1
  WHERE column1 = 'value'
     OR column2 = 'value'
     OR column3 = 'value'
     ...
     ;

0 votes

Vous devez la limiter aux colonnes char, varchar et varchar2, car les colonnes de nombres et de dates ne peuvent pas contenir cette chaîne.

8 votes

@ammoQ -- comme je l'ai dit dans l'avant-dernier paragraphe ?

0 votes

Je l'ai exécuté sur 9i et j'ai obtenu l'erreur column_name unknown. Quelqu'un peut-il me dire quelles modifications sont nécessaires pour exécuter ce programme sur 9i ?

37voto

Flood Points 131

J'ai fait quelques modifications au code ci-dessus pour qu'il fonctionne plus rapidement si vous cherchez dans un seul propriétaire. Il suffit de changer les 3 variables v_owner, v_data_type et v_search_string pour les adapter à ce que vous recherchez.

SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='string to search here...';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/

0 votes

J'ai dû commenter la première ligne afin de pouvoir exécuter cette requête. De même, je n'ai pas pu supprimer le filtre propriétaire et exécuter la requête.

1 votes

J'avais besoin de mettre des guillemets doubles autour du nom de la table / du nom de la colonne pour éviter les problèmes lorsque ceux-ci doivent être cités : 'SELECT COUNT(*) FROM "'||t.table_name||'" WHERE "'||t.column_name||'" = :1'

0 votes

Attention, all_tab_cols contient également des vues, malgré son nom.

8voto

jim Points 1035

Oui, vous pouvez le faire et votre DBA vous détestera et vous demandera de clouer vos chaussures au sol, car cela entraînera de nombreuses entrées/sorties et réduira considérablement les performances de la base de données au fur et à mesure que le cache se purgera.

select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;

pour commencer.

Je commencerais par les requêtes en cours, en utilisant la fonction v$session et le v$sqlarea . Cela change en fonction de la version d'oracle. Cela réduira l'espace et ne touchera pas à tout.

7voto

xojins Points 31

Voici une autre version modifiée qui permettra de comparer une correspondance de sous-chaîne inférieure. Elle fonctionne avec Oracle 11g.

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='OWNER_NAME';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='%lower-search-sub-string%';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/

0 votes

J'obtiens cette erreur i.imgur.com/w41enOB.png

5voto

diciu Points 18634

Je ferais quelque chose comme ceci (générer toutes les sélections dont vous avez besoin). Vous pourrez ensuite les intégrer à sqlplus :

echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw;
do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print $1}' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;

Il donne :

select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'

Et ce qu'il fait, c'est que pour chaque table_name de user_tables récupérer chaque champ (de desc) et créer un select * from table where field equals 'val'.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X