Поиск известного значения в неизвестной базе...

Иногда, в процессе работы ,приходится сталкиваться с задачами, 
когда знаешь некоторое значение в базе , например уникальный идентификационный номер,
и необходимо определить в каких таблицах, и каких столбцах базы данное значение расположено

Особенно это справедливо , когда работаешь с чужой базой , и имеешь ограниченный набор прав,
например нет возможности трассировки , а клиентская часть представляет собой веб интерфейс , который является частью сложной многозвенной структуры общего приложения.

То есть, ты видишь на экране условное значение , необходимо разобраться , из каких таблиц и столбцов значение считывается. 

Приведу несколько примеров, которые лично мне помогли решить данную задачу.

Подготовим тестовые данные

create table t1(n number);
create table t2(n number);
create table t3(n number);
create table t4(n number);
insert into t1 select level from dual connect by level < 10;
insert into t2 select level from dual connect by level < 20;
insert into t3 select level from dual connect by level < 10;
insert into t4 select level from dual connect by level < 20;
commit;

соберем статистику по таблицам необходимой схемы

begin
 	dbms_stats.gather_schema_stats(ownname => 'TRADE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>FALSE);
end;

Допустим , на экране браузера мы увидели уникальное значение "15" , попробуем найти все таблицы и колонки содержащие данное значение в схеме TRADE 

Выполним поиск данных с помощью следующей несложной программы

declare 
  -- Local variables here
  search_value number;
  search_schema varchar2(50);
  vsql varchar2(250);
  row_count number;
begin
  -- Test statements here
  search_value := 15; -- искомое значение  !!!!
  search_schema := 'TRADE'; -- схема базы данных , где производим поиск
  DBMS_OUTPUT.put_line('Поиск значения "'||search_value||'" ; старт :'||sysdate);
  for cr in 
    ( 
    select c.table_name, c.column_name, c.HIGH_value, c.low_value ,t.num_rows 
    from 
     all_tab_columns c , dba_tables t
    where c.owner = search_schema
     and c.owner = t.owner 
     and c.table_name = t.table_name 
     and t.num_rows > 5 
     and t.num_rows < 1000
     and c.data_type = 'NUMBER'
     and high_value is not null
  ) 
  loop
    -- формируем динамический sql 
    vsql := 'select /* + FIRST_ROWS(1) */ count(1) from '||search_schema||'.'|| cr.table_name ||' where '||cr.column_name||'='''||search_value||''' and rownum<2';
    execute immediate vsql into row_count;
    if row_count = 1 then 
      DBMS_OUTPUT.put_line('значение '||search_value ||' найдено в таблице : '||cr.table_name||' колонке : '|| cr.column_name); 
    end if;
  end loop;
  exception 
     when others then 
       DBMS_OUTPUT.put_line('Ошибка '||substr(sqlcode||' '||substr(sqlerrm,1,200),1,210));            
end;

Результат 
Поиск значения "15" ; старт :21.10.15 22:01
значение 15 найдено в таблице : T2 колонке : N
значение 15 найдено в таблице : T4 колонке : N

То есть, мы нашли то , что искали!

Итак, общий алгоритм таков , проходим последовательно все представляющие для нас интерес колонки, пользуясь словарными таблицами, при этом четко должны быть сформированы критерии отбора нужных колонок

Далее , с помощью динамического SQL, выполняем запрос поиска данных в каждой из колонок.
И ,через некоторое ,иногда весьма значительное время, видим на экране результат - решение, имя таблицы, название колонки, где находятся данные.

Разумеется, в дальнейшем, вы сможете преобразовать данный скрипт в функции , изменить критерии поиска , дополнить поиск по нескольким значениям, это только базовая заготовка.

• < Назад
• Вперёд >