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

Question

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 ?

Answer 1

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'
     ...
     ;

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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'.