Rechercher tous les champs dans toutes les tables pour une valeur spécifique (Oracle)

Question

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

Il y a des centaines de tables avec des milliers de lignes dans certaines tables, je sais donc que cela pourrait prendre beaucoup de temps à interroger. Mais la seule chose que je sais est qu'une valeur pour le champ que je souhaiterais interroger est 1/22/2008P09RR8.

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

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

Il n'y a absolument aucune documentation sur cette base de données et je n'ai aucune idée d'où provient ce champ.

Des idées ?

Answer 1

Citation :

J'ai essayé d'utiliser cette déclaration ci-dessous pour trouver une colonne appropriée basée sur 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%'

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

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

Mais si la chaîne 'DTN' est juste une supposition de votre part, cela ne vous aidera probablement pas.

Soit dit en passant, à quel point êtes-vous certain que '1/22/2008P09RR8' est une valeur sélectionnée directement à partir d'une seule colonne ? Si vous ne savez pas du tout d'où cela provient, cela pourrait être une concaténation de plusieurs colonnes, ou le résultat d'une fonction, ou une valeur se trouvant dans un objet de table imbriquée. Vous pourriez donc courir après des chimères en essayant de vérifier chaque colonne pour cette valeur. Ne pourriez-vous pas commencer par l'application cliente qui affiche cette valeur et essayer de comprendre quelle requête elle utilise pour l'obtenir ?

Quoi qu'il en soit, la réponse de diciu donne une méthode pour générer des requêtes SQL afin de vérifier chaque colonne de chaque table pour cette valeur. 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 des moyens de rendre cela plus efficace également.

Dans ce cas, étant donné la valeur que vous recherchez, vous pouvez clairement éliminer toute colonne de type NUMBER ou DATE, ce qui réduirait le nombre de requêtes. Peut-être même de restreindre aux colonnes où le type est du type '%CHAR%'.

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

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

Answer 2

J'ai apporté quelques modifications au code ci-dessus pour le rendre plus rapide si vous recherchez dans un seul propriétaire. Il vous suffit de modifier les 3 variables v_owner, v_data_type et v_search_string pour correspondre à ce que vous recherchez.

SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
-- Tapez le propriétaire des tables que vous regardez
  v_owner VARCHAR2(255) :='ENTRER_NOM_UTILISATEUR_ICI';

-- Tapez le type de données que vous regardez (en MAJUSCULE)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Tapez la chaîne que vous recherchez
  v_search_string VARCHAR2(4000) :='chaîne à rechercher ici...';

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

Answer 3

Oui, vous le pouvez et votre DBA vous détestera et vous trouvera pour clouer vos chaussures au sol car cela entraînera beaucoup d'E/S et fera vraiment chuter les performances de la base de données lorsque le cache sera purgé.

sélectionnez column_name de all_tab_columns c, user_all_tables u où c.nom_table = u.nom_table;

pour commencer.

Je commencerais par les requêtes en cours d'exécution, en utilisant v$session et v$sqlarea. Cela varie en fonction de la version d'Oracle. Cela permettra de cibler l'espace et d'éviter d'affecter l'ensemble des données.

Answer 4

Voici une autre version modifiée qui va comparer une correspondance de sous-chaîne en minuscules. Cela fonctionne dans Oracle 11g.

DECLARE
  match_count INTEGER;
-- Tapez le propriétaire des tables que vous recherchez
  v_owner VARCHAR2(255) :='NOM_DU_PROPRIETAIRE';

-- Tapez le type de données que vous recherchez (en MAJUSCULE)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Tapez la chaîne que vous recherchez
  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;
/

Answer 5

Je ferais quelque chose comme ceci (génère tous les selects dont vous avez besoin). Vous pourrez ensuite les transmettre à 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;

Cela 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 que cela fait - pour chaque table_name de user_tables obtenir chaque champ (à partir de desc) et créer un select * from table where champ est égal à 'val'.