Поиск по БД, возможно?

@СyberSpec · Регистрация: 10.04.2009

Author24 — интернет-сервис помощи студентам

Приветствую.

Исследую структуру БД. Делаю селекты, сопоставляю, сверяю. Вот есть не стыковки. Встретил значение реквизита, которое кроме как в этом справочнике нигде не увидел. Это значение очень важно для меня. Я хочу сделать поиск по БД запросом, потому что таблиц более 2600 и смотреть все я не могу.

Работаю по сети, у меня есть аккаунт с правом только чтения.

Суть. Выбрать имена таблиц в которых в любом из полей в любой строчке есть заданный реквизит. Реально ли написать такой селект?

@Denis Popov · 07.07.2011, 13:30

Можно просто перебрать все строковые поля всех таблиц на предмет их значения, например, найти все поля где встречается символ 'A':

Oracle 11 SQL

SELECT *
FROM (SELECT table_name
           , column_name
           , dbms_xmlgen.getXmltype(
               'select count(*) cnt from '||table_name||' where '||column_name||' like ''%A%'''
             ).EXTRACT('ROWSET/ROW/CNT/text()').getNumberVal() cnt
      FROM COLS
      WHERE 1=1
        AND COLS.data_type LIKE '%CHAR%'
     )
WHERE cnt > 0
ORDER BY table_name, column_name;

@СyberSpec · 07.07.2011, 14:04 **[ТС]**

Сообщение от Denis Popov

Можно просто перебрать все строковые поля всех таблиц на предмет их значения, например, найти все поля где встречается символ 'A':

Oracle 11 SQL

SELECT *
FROM (SELECT table_name
           , column_name
           , dbms_xmlgen.getXmltype(
               'select count(*) cnt from '||table_name||' where '||column_name||' like ''%A%'''
             ).EXTRACT('ROWSET/ROW/CNT/text()').getNumberVal() cnt
      FROM COLS
      WHERE 1=1
        AND COLS.data_type LIKE '%CHAR%'
     )
WHERE cnt > 0
ORDER BY table_name, column_name;

Жаль у меня не вышло.

System.Data.OracleClient.OracleException: ORA-00911: invalid character

@Denis Popov · 07.07.2011, 14:17

Какая версия Oracle? Внутренний подзапрос выполняется?

Oracle 11 SQL

SELECT table_name
     , column_name
     , dbms_xmlgen.getxmltype(
         'select count(*) cnt from '||table_name||' where '||column_name||' like ''%a%'''
       ).EXTRACT('rowset/row/cnt/text()').getNumberVal() cnt
FROM COLS
WHERE cols.data_type LIKE '%CHAR%'
ORDER BY cnt DESC;

@turbanoff · 07.07.2011, 14:54

ORA-00911: invalid character

В подавляющем большинстве случаев причина этого в точке с запятой в конце statement-а.

Добавлено через 14 минут
странно вообще:
на запрос ругается так:

Код

ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1

внутренний запрос начинает выполняться и останавливается с ошибкой:

Код

ORA-19202: Error occurred in XML processing
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1

Предлагаю вариант с функцией:

Oracle 11 SQL

CREATE OR REPLACE FUNCTION VALUE_PRESENT
    (table_name IN VARCHAR2
    ,column_name IN VARCHAR2
    ,TEXT IN VARCHAR2
    )
RETURN NUMBER
AS
    nVAL NUMBER(17);
BEGIN
    BEGIN
    EXECUTE IMMEDIATE
        'select count(*)'||
        ' from '||table_name||
        ' where '||column_name||' like ''%'||TEXT||'%'''
        INTO nval;
    EXCEPTION WHEN OTHERS THEN
        RETURN 0;
    END;
    RETURN nVAL;
END;
/
 
SELECT table_name
     , column_name
FROM COLS
WHERE COLS.data_type LIKE '%CHAR%'
  AND VALUE_PRESENT(table_name, column_name, 'SearchText') > 0;

@СyberSpec · 08.07.2011, 08:11 **[ТС]**

Сообщение от Denis Popov

Какая версия Oracle? Внутренний подзапрос выполняется?

Oracle 11 SQL

SELECT table_name
     , column_name
     , dbms_xmlgen.getxmltype(
         'select count(*) cnt from '||table_name||' where '||column_name||' like ''%a%'''
       ).EXTRACT('rowset/row/cnt/text()').getNumberVal() cnt
FROM COLS
WHERE cols.data_type LIKE '%CHAR%'
ORDER BY cnt DESC;

выполняется если убрать ";" после desc. возвратилось 0 строк. Если из первого вашего запроса убрать последнюю ";" то будет другая ошибка:

System.Data.OracleClient.OracleException: ORA-22806: not an object or REF

версия БД:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Стоит сказать, что выполняю запросы из клиента написанного на VB.NET.

turbanoff, функцию нужно будет создавать в БД, правильно?
Я писал что у меня доступ только на чтение. Нужен вариант когда ничего в структуре БД не поменяется.

@Denis Popov · 08.07.2011, 12:26

Сообщение от СyberSpec

Стоит сказать, что выполняю запросы из клиента написанного на VB.NET.

Возьми например SQL Developer

@СyberSpec · 08.07.2011, 15:20 **[ТС]**

Сообщение от Denis Popov

Возьми например SQL Developer

Зачем??

Добавлено через 2 часа 40 минут
Простой запрос

SELECT table_name
, column_name
FROM COLS
WHERE COLS.data_type LIKE '%CHAR%'

выполняется без ошибок и возвращает 0 записей.
что я делаю не так?

@Denis Popov · 08.07.2011, 16:02

Сообщение от СyberSpec

выполняется без ошибок и возвращает 0 записей.
что я делаю не так?

Убедись что схема правильная:

SQL

1	SELECT USER FROM DUAL

и что есть таблицы вообще:

SQL

1	SELECT * FROM USER_TABLES

@СyberSpec · 11.07.2011, 08:15 **[ТС]**

Запрос

T-SQL

1	SELECT * FROM USER_TABLES

Вернул 0 строк. Но таблиц же более 2 тысяч, почему 0? Это 0 таблиц созданных из под моего user'а? Тогда конечно, он недавно создан и права у него только на чтение. Непонятно.

@turbanoff · 11.07.2011, 08:35

в таком случае вам нужно знать в какой схеме хранятся данные. Или вы предполагаете поиск по всем схемам?

@СyberSpec · 11.07.2011, 09:47 **[ТС]**

Сообщение от turbanoff

в таком случае вам нужно знать в какой схеме хранятся данные. Или вы предполагаете поиск по всем схемам?

Да именно, приходиться работать вслепую. Никакой документации или годной информации по этой БД у меня нет.

Добавлено через 1 час 1 минуту
Вернее доки есть, только за отдельную плату, а начальство не любит когда платить нужно.
Мне необходимо найти связь между заказами и приходом на склад. Я перебираю select'ами с условием на ссылку на ключ заказа. перебираю все таблицы. их более 2к. я на букве "C" еще только)) помогите плз кто знает как автоматизировать, то что я сейчас делаю - делаю против сердца прям. рутина и глупость по моему.

@Grossmeister · 11.07.2011, 10:18

В Oracle 3 типа системных view (есть и еще, но сейчас нас это не волнует):
1. user_.... - это "свои" объекты, т.е. те, хозяином которых являешься ты
2. all_... - это твои объекты + те, на которые тебе даны гранты
3. dba_... - это все объекты (обычно сюда имеют доступ только привилегированные пользователи)

Описание этих view есть в томе доки под названием Database Reference

@СyberSpec · 11.07.2011, 10:39 **[ТС]**

Сообщение от Grossmeister

В Oracle 3 типа системных view (есть и еще, но сейчас нас это не волнует):
1. user_.... - это "свои" объекты, т.е. те, хозяином которых являешься ты
2. all_... - это твои объекты + те, на которые тебе даны гранты
3. dba_... - это все объекты (обычно сюда имеют доступ только привилегированные пользователи)

Описание этих view есть в томе доки под названием Database Reference

в 1 пункте пусто, в 3й меня не пустит никто)
вот я и делаю

T-SQL

1	SELECT * FROM all_Tables

и потом глядя на колонку TABLE_NAME делаю запросы вида

SQL

1	SELECT * FROM тут_имя_таблицы WHERE prn = тут_ключ_таблицы_для_которой_ищу_связь

ужасно муторно.

Добавлено через 7 минут
и не правильно, ведь связь может быть и не по аттрибуту "PRN", тогда я опять пролетаю..

@Denis Popov · 11.07.2011, 10:48

Если работает запрос к таблице без указания схемы, то может быт ты имеешь доступ к ней через синоним, публичный или приватный. обрати внимание на значение поля ALL_TABLES.OWNER, если он не равен твоему текущему пользователю, то первоначальный запрос можно модифицировать, например, для пользователя SCOTT - владельца объектов.

SQL

SELECT TABLE_NAME
     , column_name
     , dbms_xmlgen.getXmltype(
         'select count(*) cnt from '||owner||'.'||TABLE_NAME||' where '||column_name||' like ''%A%'''
       ).extract('ROWSET/ROW/CNT/text()').getnumberval() cnt
FROM ALL_TAB_COLUMNS
WHERE owner = 'SCOTT'
  AND data_type LIKE '%CHAR%'
ORDER BY cnt DESC

@Grossmeister · 11.07.2011, 10:52

1. кроме all_tables есть еще all_tab_columns.
Кроме того из all_constraints и all_cons_columns можно посмотреть связи между таблицами (если в базе созданы связки PK-FK). В этом случае в all_constraints будет constraint_type = 'R' (от Reference)

2. генерировать скритпы можно автоматом.
- в SQL*Plus включаешь spool
- формируешь скрипты типа

SQL

DECLARE
   CURSOR C1
   IS
   SELECT TABLE_NAME
   FROM all_tables
   WHERE ...;
-- здесь показал только таблицы, имена и типы колонок - аналогично в all_tab_columns
BEGIN
   FOR R1 IN C1 loop
      dbms_output.put_line('SELECT * FROM '|| R1.table_name ||' WHERE ...');
   END loop;
END;

-- spool off
Потом запускаешь запросы из сформированного файла

3. Вообще конечно искать по всем полям какое-то значение - задача еще та. Если есть приложение, работающее с базой и использующее нужное значение - проще включить трассировку сессии (если потом есть возможность достать с сервера трассировочный файл). Если значение может использоваться в хранимых процедурах, то можно поискаиь с пом. LIKE в текстах (исходные тексты храняться в all_source)

@СyberSpec · 11.07.2011, 11:12 **[ТС]**

Спасибо. И за all_constraints и all_cons_columns отдельное большущее спасибо, надеюсь поможет. Буду теперь пробовать ваши советы.

@СyberSpec · 12.07.2011, 11:35 **[ТС]**

Уф. Вопрос снова открыт. all_constraints и all_cons_columns рассказали о том что некоторые таблицы не связаны, а то что в них значения это ссылки на другие документы и на них самих никто не ссылается, это меня немного коробит. Почему так сделали разработчики ума не приложу. теперь вот хз что делать. Короче, ситуация:
Есть таблицы
DOCS_PROP свойства документов
DOCS_PROP_VALS значения свойств док.
DOCS_PROP_LINK ссылка на справочник для значений, хотя хз точно что это за справочник из 20 строк.

Логично думать что где-то есть таблица связывающая документы и их свойства. Вот ее и надо найти. Осложняется тем что судя по связям из таблицы all_constraints эти три таблички сами по себе. По названию тоже пробовал искать и увидел DOCS_OWNERS подумал что это документы и их свойства, но табличка пустая.

Из вариантов предложенных Grossmeister'ом возможно использовать только 2-ой, но у меня мало опыта. Поэтому прошу помоч составить sql запрос для поиска значения в БД. Просто селект, можно ведь? Тут еще сложность с названием поля в условии отбора WHERE. оно как угодно может называться((
у меня есть только значение 11852081 это ключ DOCS_PROP и я знаю что оно в гдето храниться как число.
Возвращаясь к первому посту, эта задача реально выполнима? Или такой селект будет отваливаться потому что он будет в цикле по всем таблицам и полям этих таблиц(я себе это так представляю), а к некоторым у меня и доступ то закрыт?.. Я негодую.

@СyberSpec Кибернетик 465 / 89 / 12 Регистрация: 10.04.2009 Сообщений: 424
		1
	Поиск по БД, возможно? 07.07.2011, 12:42. Показов 15689. Ответов 17 Метки нет (Все метки) Приветствую. Исследую структуру БД. Делаю селекты, сопоставляю, сверяю. Вот есть не стыковки. Встретил значение реквизита, которое кроме как в этом справочнике нигде не увидел. Это значение очень важно для меня. Я хочу сделать поиск по БД запросом, потому что таблиц более 2600 и смотреть все я не могу. Работаю по сети, у меня есть аккаунт с правом только чтения. Суть. Выбрать имена таблиц в которых в любом из полей в любой строчке есть заданный реквизит. Реально ли написать такой селект? 0

@Denis Popov 11 / 11 / 0 Регистрация: 05.08.2010 Сообщений: 30
	08.07.2011, 12:26	7
	Сообщение от СyberSpec Стоит сказать, что выполняю запросы из клиента написанного на VB.NET. Возьми например SQL Developer 0

@СyberSpec Кибернетик 465 / 89 / 12 Регистрация: 10.04.2009 Сообщений: 424
	08.07.2011, 15:20 [ТС]	8
	Сообщение от Denis Popov Возьми например SQL Developer Зачем?? Добавлено через 2 часа 40 минут Простой запрос SELECT table_name , column_name FROM COLS WHERE COLS.data_type LIKE '%CHAR%' выполняется без ошибок и возвращает 0 записей. что я делаю не так? 0

@turbanoff 4091 / 3825 / 745 Регистрация: 18.05.2010 Сообщений: 9,331 Записей в блоге: 11
	11.07.2011, 08:35	11
	в таком случае вам нужно знать в какой схеме хранятся данные. Или вы предполагаете поиск по всем схемам? 1

@СyberSpec Кибернетик 465 / 89 / 12 Регистрация: 10.04.2009 Сообщений: 424
	11.07.2011, 09:47 [ТС]	12
	Сообщение от turbanoff в таком случае вам нужно знать в какой схеме хранятся данные. Или вы предполагаете поиск по всем схемам? Да именно, приходиться работать вслепую. Никакой документации или годной информации по этой БД у меня нет. Добавлено через 1 час 1 минуту Вернее доки есть, только за отдельную плату, а начальство не любит когда платить нужно. Мне необходимо найти связь между заказами и приходом на склад. Я перебираю select'ами с условием на ссылку на ключ заказа. перебираю все таблицы. их более 2к. я на букве "C" еще только)) помогите плз кто знает как автоматизировать, то что я сейчас делаю - делаю против сердца прям. рутина и глупость по моему. 0

@Grossmeister Модератор 4217 / 3058 / 583 Регистрация: 21.01.2011 Сообщений: 13,205
	11.07.2011, 10:18	13
	В Oracle 3 типа системных view (есть и еще, но сейчас нас это не волнует): 1. user_.... - это "свои" объекты, т.е. те, хозяином которых являешься ты 2. all_... - это твои объекты + те, на которые тебе даны гранты 3. dba_... - это все объекты (обычно сюда имеют доступ только привилегированные пользователи) Описание этих view есть в томе доки под названием Database Reference 1

@СyberSpec Кибернетик 465 / 89 / 12 Регистрация: 10.04.2009 Сообщений: 424
	11.07.2011, 11:12 [ТС]	17
	Спасибо. И за all_constraints и all_cons_columns отдельное большущее спасибо, надеюсь поможет. Буду теперь пробовать ваши советы. 0

@СyberSpec Кибернетик 465 / 89 / 12 Регистрация: 10.04.2009 Сообщений: 424
	12.07.2011, 11:35 [ТС]	18
	Уф. Вопрос снова открыт. all_constraints и all_cons_columns рассказали о том что некоторые таблицы не связаны, а то что в них значения это ссылки на другие документы и на них самих никто не ссылается, это меня немного коробит. Почему так сделали разработчики ума не приложу. теперь вот хз что делать. Короче, ситуация: Есть таблицы DOCS_PROP свойства документов DOCS_PROP_VALS значения свойств док. DOCS_PROP_LINK ссылка на справочник для значений, хотя хз точно что это за справочник из 20 строк. Логично думать что где-то есть таблица связывающая документы и их свойства. Вот ее и надо найти. Осложняется тем что судя по связям из таблицы all_constraints эти три таблички сами по себе. По названию тоже пробовал искать и увидел DOCS_OWNERS подумал что это документы и их свойства, но табличка пустая. Из вариантов предложенных Grossmeister'ом возможно использовать только 2-ой, но у меня мало опыта. Поэтому прошу помоч составить sql запрос для поиска значения в БД. Просто селект, можно ведь? Тут еще сложность с названием поля в условии отбора WHERE. оно как угодно может называться(( у меня есть только значение 11852081 это ключ DOCS_PROP и я знаю что оно в гдето храниться как число. Возвращаясь к первому посту, эта задача реально выполнима? Или такой селект будет отваливаться потому что он будет в цикле по всем таблицам и полям этих таблиц(я себе это так представляю), а к некоторым у меня и доступ то закрыт?.. Я негодую. 0