Получение списка всех таблиц в Oracle: запрос SQL

Быстрый ответ

Для того чтобы получить просмотр всех таблиц, доступных вашему пользователю, используйте SELECT table_name FROM all_tables;. Если вам важно получить список таблицами, которые вы создали, примените команду SELECT table_name FROM user_tables;. Бывает так, что особенно полезным считается запрос SELECT table_name FROM dba_tables; — он дает доступ ко всем таблицам базы данных. Однако стоит учесть, что это касается ваших привилегий доступа.

И хотите превратить результаты в программе sqlplus в читаемый и красивый текст? Для этого вам на помощь могут прийти следующие команды: set linesize 167, set pagesize 1000 или set colsep '|'.

Исследование схем

Класс по использованию таблиц

Предлагаем оставить на время простое просмотрение имен таблиц и перейти к глубокому изучению их содержимого с помощью оператора SELECT. Обращение к all_tab_columns позволит определить связи между таблицами, включая информацию о таких вещах как столбцы и метаданные. Вот, например, как узнать data_type (типы данных) и nullable (допустимость значения NULL). Эти данные окажутся крайне полезными в случаях, когда вы стремитесь найти связи между таблицами или просто определяете, как лучше их соединить.

Подробнее об этом расскажет наш спикер на видео

Глубокое изучение и поиск по шаблону

Использование оператора LIKE позволяет вам глубоке фильтровать поиск и находить конкретные шаблоны. Например, если вы ищете все таблицы с общим префиксом 'EMP', вы можете сформулировать такой запрос: `SELECT table_name FROM all_tables WHERE table_name LIKE 'EMP%';. А если вас интересуют столбцы, содержащие 'DATE', то можно составить такой запрос:SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DATE%';`.

Как отфильтровать вывод от системных таблиц

Если вам требуется отфильтровать системные таблицы Oracle и сосредоточиться только на пользовательских таблицах, то следует исключить из выборки таблицы SYS и SYSTEM. Для этого в оператор WHERE добавляется следующее условие: WHERE owner NOT IN ('SYS', 'SYSTEM'). Таким образом, вы уберете из вывода сложность системных таблиц, оставив акцент только на том, что создал сам пользователь.

Обратная совместимость

Если вы столкнетесь с устаревшими элементами и таблицами, не волнуйтесь. Oracle продолжает поддерживать старые представления словаря, такие как DBA_TABS, TAB и CAT, которые дают доступ к данным в более старых форматах. Однако нужно учитывать, что эти представления обусловлены обратной совместимостью и могут не содержать некоторые из новых метаданных, доступных в dba_tables или all_tab_columns.

Визуализация

Можно вообразить базу данных Oracle в виде огромной библиотеки 📚, где каждая книга — это отдельная таблица:

Библиотека 📚: [Таблица_Книга_1, Таблица_Книга_2, Таблица_Книга_3, ..., Таблица_Книга_N]

Чтобы пройтись по всем "книгам" в вашей "библиотеке", следует провести инвентаризацию каталога 🔍:

SELECT table_name FROM all_tables; -- Зачем вам система Дьюи, когда есть SQL?

В итоге получим вот что:

🔍📚 ➜ Названия книг: [Таблица_Книга_1, Таблица_Книга_2, Таблица_Книга_3, ..., Таблица_Книга_N]

В данном контексте наше "каталогизирование" (🔍) = выполнение запроса, а названия книг = имена таблиц в базе данных Oracle, таким образом вы полностью "каталогизируете" все "книги" (таблицы), которые размещены на вашей "полке" (в базе данных).

Если же вам интересны не только таблицы, но и представления, то у вас есть возможность "полки" для представлений, которые вы можете просмотреть при помощи SELECT view_name FROM all_views;.

Исправление случившихся ошибок при работе с Oracle

Обзор словарных представлений и привилегии системы

Словарные представления Oracle содержат скрытую информацию и не все пользователи могут получить доступ к ним. Если вы не являетесь DBA, но вам необходимо осуществить глубокую проработку представлений словаря, то придется просить о предоставлении некоторых привилегий, таких как SELECT ANY DICTIONARY или роль SELECT_CATALOG_ROLE. Как обычно, сначала все-таки следует обратиться к правилам безопасности, прежде чем приступать к работе с этими представлениями.

Сложные задачи и поиск столбцов

Бывает, что простой просмотр имен таблиц и основной информации об их столбцах может оказаться недостаточным. В этом случае для решения более сложных задач могут потребоваться подробные метаданные, которые находятся в dba_tab_columns. Здесь есть доступ к информации о столбцах, типах данных, а также информации о том, в каком tablespace находится таблица. При выполнении сложных запросов вам может понадобиться связать с dba_constraints, чтобы найти определенные столбцы или те, что подчиняются определенным ограничениям.

Работа с пространствами имен

Нужно помнить, что схемы Oracle работают как пространства имен. Поле owner разделяет таблицы по схемам, как королевства, разделенные границами между королями. Для просмотра таблиц, принадлежащих конкретному пользователю, используется следующий запрос: SELECT table_name FROM all_tables WHERE owner = 'YOUR_SCHEMA_NAME';. Все случает, когда в одной и той же базе данных и при наличии нескольких схем были объекты с совпадающими именами, но принадлежащие разным пользователям. Но и с такими ситуациями вы справитесь, ведь у вас есть все необходимые инструменты!

Полезные материалы

1. ALL_TAB_COLUMNS — Официальное описание представления данных словаря ALL_TAB_COLUMNS от Oracle.
2. Oracle / PLSQL: Tables — Руководство по поиску всех таблиц в базе данных Oracle.
3. Tables – Oracle FAQ — Полный обзор о таблицах Oracle, в том числе как получить список всех таблиц.
4. Questions — На AskTOM вы найдете ответы на ваши вопросы о таблицах Oracle и работе с ними в рамках схемы.