Поиск известного значения в неизвестной базе...
Иногда, в процессе работы ,приходится сталкиваться с задачами,
когда знаешь некоторое значение в базе , например уникальный идентификационный номер,
и необходимо определить в каких таблицах, и каких столбцах базы данное значение расположено
Особенно это справедливо , когда работаешь с чужой базой , и имеешь ограниченный набор прав,
например нет возможности трассировки , а клиентская часть представляет собой веб интерфейс , который является частью сложной многозвенной структуры общего приложения.
То есть, ты видишь на экране условное значение , необходимо разобраться , из каких таблиц и столбцов значение считывается.
Приведу несколько примеров, которые лично мне помогли решить данную задачу.
Подготовим тестовые данные
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, выполняем запрос поиска данных в каждой из колонок.
И ,через некоторое ,иногда весьма значительное время, видим на экране результат - решение, имя таблицы, название колонки, где находятся данные.
Разумеется, в дальнейшем, вы сможете преобразовать данный скрипт в функции , изменить критерии поиска , дополнить поиск по нескольким значениям, это только базовая заготовка.