Suche Alle Felder In Allen Tabellen Nach Einem Bestimmten Wert (Oracle)

Zitat:

Ich habe versucht, mit dieser Erklärung unten
finden Sie eine passende Spalte, basierend auf
was ich denke, es sollte benannt werden, aber es
keine Ergebnisse gefunden.*

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

Einer Spalte ist nicht ein Objekt. Wenn du meinst, dass Sie erwarten, dass die Spalte name like '%DTN%', die Abfrage, die Sie wollen, ist:

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

Aber wenn die 'DTN' string ist nur eine Vermutung ihrerseits, das wird wahrscheinlich nicht helfen.

Durch die Art und Weise, wie sicher sind Sie, dass "1/22/2008P09RR8' einen Wert ausgewählt, der direkt aus einer einzelnen Spalte? Wenn Sie nicht wissen, wo es herkommt, es könnte eine Verkettung von mehreren Spalten, oder das Ergebnis einer Funktion oder einen Wert, der sitzt in einer geschachtelten Tabelle-Objekt. So könnten Sie auf einem wild-goose chase versuchen, um zu überprüfen, jede Spalte für den Wert. Kann Sie nicht starten, egal mit welchem client-Anwendung ist die Anzeige diesen Wert und versuchen Sie herauszufinden, welche Abfrage ist es mit, um es zu erhalten?

Sowieso, diciu die Antwort gibt eine Methode zum generieren von SQL-Abfragen, um zu überprüfen jede Spalte jeder Tabelle für den Wert. Sie können auch ähnliche Sachen, die vollständig in eine SQL-Sitzung unter Verwendung einer PL/SQL-block und dynamische SQL. Hier ein paar hastig geschriebenen code:

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

Gibt es einige Möglichkeiten, wie Sie könnte es machen, effizienter zu werden.

In diesem Fall, da der Wert, den Sie suchen, können Sie eindeutig zu beseitigen, eine Spalte mit der ANZAHL oder DATUM geben, die Verringerung der Anzahl der Abfragen. Vielleicht sogar einschränken, es zu Spalten, wobei Typ like 'CHAR%'.

Anstatt eine Abfrage pro Spalte, die Sie bauen könnten, eine Abfrage pro Tabelle wie diese:

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

InformationsquelleAutor Dave Costa

Ja können Sie und Ihre DBA hassen Sie und finden Sie für Nagel Ihre Schuhe auf den Boden, weil das wird zu viel I/O und, um die Datenbank-Leistung wirklich runter, da der cache verfällt.

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

für einen start.

Ich würde beginnen mit dem ausführen von Abfragen, mit der v$session und die v$sqlarea. Diese änderungen basieren auf oracle-version. Dies wird verengen den Raum und trifft nicht alles.

InformationsquelleAutor jim

Hier ist eine weitere modifizierte version, die Sie vergleichen, eine geringere übereinstimmung. Dies funktioniert in 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;
/

InformationsquelleAutor xojins

Ich geändert Flut Skript ausgeführt werden, einmal für jede Tabelle, anstatt für jede Spalte in jeder Tabelle für eine schnellere Ausführung. Es erfordert Oracle 11g oder höher.

    set serveroutput on size 100000

declare
    v_match_count integer;
    v_counter integer;

    -- The owner of the tables to search through (case-sensitive)
    v_owner varchar2(255) := 'OWNER_NAME';
    -- A string that is part of the data type(s) of the columns to search through (case-insensitive)
    v_data_type varchar2(255) := 'CHAR';
    -- The string to be searched for (case-insensitive)
    v_search_string varchar2(4000) := 'FIND_ME';

    -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
    v_sql clob := '';
begin
    for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in 
                       (select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' ||  upper(v_data_type) || '%')
                       order by table_name) loop
        v_counter := 0;
        v_sql := '';

        for cur_columns in (select column_name from all_tab_columns where 
                            owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
            if v_counter > 0 then
                v_sql := v_sql || ' or ';
            end if;
            v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
            v_counter := v_counter + 1;
        end loop;

        v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;

        execute immediate v_sql
        into v_match_count;

        if v_match_count > 0 then
            dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
        end if;
    end loop;

    exception
        when others then
            dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/

InformationsquelleAutor Mike Rodey

Verfahren zu Suchen Gesamte Datenbank:

    CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
      match_count integer;
      qry_str varchar2(1000);
      CURSOR TAB_COL_CURSOR IS 
          SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT';
          BEGIN  
            FOR TAB_COL_REC  IN TAB_COL_CURSOR
            LOOP
              qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME|| 
              ' WHERE '||TAB_COL_REC.COLUMN_NAME;
               IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN
                      qry_str := qry_str||'='||SEARCH_STR; 
               ELSE
                       qry_str := qry_str||' like '||SEARCH_STR; 
               END IF;
                       --dbms_output.put_line( qry_str );
                EXECUTE IMMEDIATE  qry_str  INTO match_count;
                IF match_count > 0 THEN          
                   dbms_output.put_line( qry_str );
                  --dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);     
                    TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
                END IF; 
          END LOOP;
     END SEARCH_DB;    

Execute-Anweisung

  DECLARE
    SEARCH_STR VARCHAR2(200);
    TAB_COL_RECS VARCHAR2(200);
    BEGIN
      SEARCH_STR := 10;
      SEARCH_DB(
        SEARCH_STR => SEARCH_STR,
        TAB_COL_RECS => TAB_COL_RECS
      );
     DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
     END;

Beispiel-Ergebnisse

Connecting to the database test.
SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10
SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10
TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.

InformationsquelleAutor Hemanth

Ich nicht von einer einfachen Lösung, die auf SQL promprt. Howeve es gibt durchaus ein paar Werkzeuge wie toad und PL/SQL-Entwickler, die eine GUI haben, wo ein Benutzer kann die Eingabe der Zeichenfolge durchsucht werden und es wird wieder die Tabelle/Prozedur/Objekt, wo dieser zu finden ist.

InformationsquelleAutor Dheer

Wurde ich mit folgenden Fragen für @Lalit Kumars Antwort,

ORA-19202: Error occurred in XML processing
ORA-00904: "SUCCESS": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 -  "Error occurred in XML processing%s"
*Cause:    An error occurred when processing the XML function
*Action:   Check the given error message and fix the appropriate problem

Lösung ist:

WITH  char_cols AS
  (SELECT /*+materialize */ table_name, column_name
   FROM   cols
   WHERE  data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
       SUBSTR (table_name, 1, 14) "Table",
       SUBSTR (column_name, 1, 14) "Column"
FROM   char_cols,
       TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
       || column_name
       || '" from "'
       || table_name
       || '" where upper("'
       || column_name
       || '") like upper(''%'
       || :val
       || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER  BY "Table"
/ 

InformationsquelleAutor bekur

Kreditaufnahme, etwas zur Verbesserung und Vereinfachung von in diesem Blog-post die folgende einfache SQL-Anweisung scheint den job ganz gut:

SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column"
FROM cols,
     TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE(
       'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("'
       || COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*')))
ORDER BY "Table";

InformationsquelleAutor Steve Chambers