Oracle где используется таблица
Добрый день! Мы команда системных аналитиков одного из подразделений управления данными «Ростелекома». В нашей компании насчитывается более 300 неоднородных источников данных — такое многообразие необходимо для поддержки работы Ростелекома по всем многочисленным направлениям. Мы изучаем источники данных и по необходимости частично выгружаем в контур хранилища.
В этом процессе выделяется две подзадачи: определение стратегии сбора данных из таблиц источника в зависимости от их свойств и подготовка таблиц-«приемников» хранилища данных. Для этого мы используем различные GUI и средства реверс-инжиниринга. Кроме того, при сборе информации системный аналитик начинает обрастать пулом вспомогательных запросов к информационным таблицам СУБД (преимущественно Oracle). В этой статье я поделюсь «джентльменским набором» таких скриптов, используемых нашей командой.
Для начала небольшое пояснение ко всем приведенным скриптам:
- Во многих скриптах для агрегации строк используется xmlagg, так как listagg не может обработать слишком длинные строки, получающиеся в результате конкатенации.
- Во всех скриптах кроме «Процедуры, функции и пакеты» целевые таблицы задаются через таблицу filter в блоке «with». Заполняется наименование схемы и наименование таблицы.
- К каждому скрипту прилагается один или несколько сценариев использования, описание спецификации (результирующего набора), а также список используемых системных таблиц (для оценки возможности использования на конкретной БД).
DBA_PART_TABLES
Представление DBA_PART_TABLES содержит информацию о типе схемы раздела и прочих параметрах хранения разделов и подразделов. Узнать тип каждого раздела каждой секционированной таблицы можно с помощью следующего запроса:
Заключение
Я попытался описать большинство вещей, который на мой взгляд могут пригодится программисту. Так как их довольно много, то я их только обозначил, часто не вдаваясь в детали. Как конкретно сделать необходимую настройку можно всегда прочитать в упомянутой книжке Тома Кайта, найти в колонке asktom или просто нагуглить. Главное знать что гуглить, и, надеюсь, данный топик вам это подсказал.
Заключение
Описанные выше скрипты помогают нашим системным аналитикам избавиться от многих рутинных действий по сбору информации о базе данных и сосредоточиться на более творческих вещах, таких как стратегия загрузки и структура таблиц-«приемников». Надеюсь, скрипты пригодятся и вам. Было бы интересно узнать, как вы автоматизируете эти и подобные задачи.
Существует несколько важных представлений словаря базы данных, которые можно использовать для нахождения детальной информации о любом из объектов базы данных, о которых говорилось в этой главе. Администраторы баз данных также интенсивно используют представления словаря данных, чтобы управлять различными объектами схемы. Здесь приводится краткий список важнейших представлений, часть из которых упоминалась выше. Полные данные о типах информации, которую можно получить от каждого из этих представлений, доступны по команде DESCRIBE (например, DESCRIBE DBA_CATALOG).
В этой статье блога будут описаны некоторые важные представления словаря данных, которые помогут управлять объектами, не хранящими данные (т.е. объектами, которые не относятся к таблицам и индексам). Ниже приведен список важнейших представлений словаря данных для просмотра объектов базы данных.
- DBA_SYNONYMS. Информация о синонимах базы данных.
- DBA_TRIGGERS. Информация о триггерах.
- DBA_SEQUENCES. Информация о созданных пользователем последовательностях.
- DBA_DB_LINKS. Информация о связях базы данных.
Как упоминалось ранее, представление DBA_OBJECTS предоставляет важную информацию обо всех перечисленных объектах, наряду с некоторыми другими типами объектов базы данных. Однако перечисленные представления содержат детальную информацию о каждом объекте, такую как исходный текст триггера, которую вы не получите из представления DBA_OBJECTS.
Управление такими объектами, как таблицы и представления, осуществляется ссылкой на представления словаря данных, наподобие DBA_TABLES и DBA_VIEWS. Существуют также отдельные представления для секционированных таблиц. Давайте рассмотрим ключевые представления словаря данных, относящиеся к таблицам и индексам.
DBA_TABLES
Представление DBA_TABLES содержит информацию обо всех реляционных таблицах базы данных. Представление DBA_TABLES — основной справочник для нахождения информации о хранении, количестве строк в таблице, состоянии протоколирования, информации буферного пула и многих других деталях. Ниже приведен простой пример запроса представления DBA_TABLES:
На заметку! Представление DBA_ALL_TABLES содержит информацию обо всех объектных и реляционных таблицах в базе данных, в то время как представление DBA_TABLES ограничено только реляционными таблицами.
Представление DBA_TABLES служит для нахождения таких вещей, как включено ли сжатие и отслеживание зависимостей на уровне строки, и была ли таблица уничтожена и помещена в корзину (Recycle Bin).
Индексы
Кроме всем известных индексов в виде B-деревьев в Oracle еще есть так называемые битовые индексы, которые показывают очень высокую производительность на запросах к таблицам в которых есть колонки с очень разреженными значениями. Особенно эффективно в этом случае будут работать запросы (по сравнению с обычными индексами) в которых присутствуют сложные комбинации OR и AND к разряженным столбцам. Данный индекс храниться не в B-дереве, а в битовых картах, что и дает возможность быстрого выполнения описанных запросов. Вопрос в количестве уникальных значений в таблице при которых данный индекс еще будет более предпочтителен весьма сложен: это может быть как 10 уникальных значений, так и 10 000. Здесь надо создавать индекс на конкретной таблице и смотреть что получается. Главное не пытайтесь использовать данный индекс на таблицах с большим количеством вставок и обновлений индексируемой колонки, так как такие операции будут блокировать довольно большие участки в индексируемой таблице и ваша система может встать колом или даже поймаете deadlock.
Одна из вещей, которая меня всегда очень радовала в Oracle — это возможность создания индекса по функции. Т.е. если вам в запросах приходиться использовать какую-нибудь функцию, то вы можете построить по ней индекс и значительно ускорить операции чтения.
Еще одно интересное свойство индексов, о котором необходимо знать, это то, что в индексе не хранятся значения NULL. Таким образом если вы будете делать запросы с условием или <> по индексируемой колонке, то в ответ строчек со значением NULL в индексируемой колонке вы обратно не получите. С другой стороны данное свойство можно очень эффективно использовать дня некоторых специфичных случаев. Например, у вас есть очень большая табличка в которой хранятся ордера, которая никогда не чистится. И существует фоновый процесс, который обязан все ордера отсылать в какую-нибудь backoffice систему. Первое решение, которое напрашивается — это завести еще одну колонку с флагом is_sent, где изначально стоит 0 и при отсылке мы будем проставлять 1. Т.е. фоновый процесс при каждом запуске будет делать запрос к таблице с условием is_sent=0. Битовый индекс вы здесь использовать не можете, так как табличка очень активно пополняется. Обычный индекс на основе В-дерева будет занимать очень много места, так как нужно хранить ссылки на огромное количество строчек. Но если мы слегка поменяем нашу логику и в качестве пометки отсылки, и в колонку is_sent будем класть NULL вместо 1, то индекс у нас будет крошечный, так как в любой момент в нем будут храниться только не NULL значения, а их будет очень мало.
Подвисание некоторых запросов на запись
При зависании некоторых ваших запросов в произвольный момент времени стоит заглянуть в alert.log на предмет наличия incomplete checkpoint. Это говорит о том, что ваши оперативные журнальные файлы слишком большие или их слишком мало, таким образом, защищаемые ими данные не успевают сбрасываться из кэша на диск, а СУБД заполнила уже все доступные оперативные журнальные файлы и хочет использовать их по кругу повторно, чего делать ни в коем случае нельзя, вот и появляется пауза. Хотя если ваше приложение работает на java, то в первую очередь я бы загляну на наличия Full GC в логах.
Что позволяет БД Oracle работать так быстро?
Когда вы меняете данные в БД, то ваши изменения сначала идут в кэш, а потом асинхронно в нескольких потоках (число можно сконфигурировать) пишутся на диск. Синхронно же пишется специальных лог (оперативный журнальный файл), чтобы была возможность восстановить данные после сбоя, если они еще не успели с кэша сброситься на диск. Данный подход позволяет выиграть в скорости, так как в этом случае на диск все пишется последовательно в один файл, причем можно настроить так, чтобы писалось параллельно на два или больше дисков, тем самым увеличивая надежность защиты от потери изменений. Описанных файлов должно быть несколько, и они используются по кругу: как только все данные защищенные одним из лог файлов были записаны фоновым процессом в блоки данных на диск, то данный лог файл может быть переиспользован. Таким образом в какой-то мере это позволяет еще и сэкономить, имея ультрабыстрые диски небольшого размера только для небольших журнальных файлов используемых по кругу.
Обычно я рассказываю об этом, когда мне предлагают что-то сохранять просто в файл на диске, так как это будет «быстрее» за счет того, что мы будет писать все данные последовательно и головке жесткого диска не надо будет бегать и искать рэндомные блоки. Я все же настаиваю, что мы тут ничего не выиграем, так как будем писать на медленный диск, который скоро всего активно используется множеством других процессов для записи огромного количества различных логов, а Oracle синхронно тоже пишет у себя на диск только последовательно, как я описал выше.
DBA_EXTERNAL_TABLES
Представление DBA_EXTERNAL_TABLES показывает подробности о любой внешней таблице в базе данных, включая их тип доступа, параметры доступа и информацию о каталоге.
Позвольте Oracle кэшировать ваши данные эффективно
В Oracle все данные читаются-пишутся не прямо на диск, а через кэш. По умолчанию кэш основан на LRU алгоритме, так что если вы читаете какую-нибудь очень большую табличку по идентификатору в больших количествах, запрашивая в каждый раз новую строчку, то такие запросы могут вытеснять из кэша небольшую статическую табличку, которой бы самое милое дело постоянно находиться в кэше. Для таких целей при создании таблицы вы можете указать специальный вид кэша, куда будут ходить запросы к вашим таблицам. Так для первой таблицы в вышеописанном примере подойдет кэш RECYCLE, который по сути не хранит никакие данные, а сразу их выбрасывает из кэша. А для второй таблицы подойдет кэш KEEP, который позволить хранить в кэше небольшие статические таблице и запросы ко всем остальным таблицам не будут вытеснять данные статических таблиц из кэша.
Stand by копия
Вышеупомянутые архивные файлы можно отправлять по сети и на лету применять к копии БД. Таким образом у вас всегда под рукой будет горячая копия с минимальным запаздыванием данных. В некоторых приложениях, где нет необходимости показывать данные с точностью до последнего момента, можно настроить такую БД только на чтение и разгрузить основной экземпляр БД, причем таких экземпляров на чтение может быть несколько.
DBA_INDEXES
Представление словаря DBA_INDEXES служит для того, чтобы узнать все необходимое об индексах в базе данных, включая имя индекса, его тип, таблицу и табличное пространство, к которому он относится. Определенные столбцы, наподобие BLEVEL (сообщает уровень B-дерева индекса) и DISTINCT_KEYS (количество разных значений ключа индекс), наполняются, только если собрана статистика по индексу с использованием пакета DBMS_STATS.
Скрипт «Информация о таблицах»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
TABLE_NAME | Наименование таблицы |
COMMENTS | Комментарий к таблице |
HEIGHT | Количество строк в таблице (приблизительно) |
WIDTH | Количество столбцов |
DATETIME_COLUMNS | Столбцы с временнЫми типами данных и столбцы, исходя из наименования, предположительно являющиеся временнЫми метками (паттерны – %period%, %date%, %time%) |
AVG_ROW_LEN | Средняя длина строки в байтах |
PART_KEY | Столбцы по которым осуществлено партиционирование |
SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
Используемые системные таблицы: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Запрос полезен для определения стратегии выгрузки данных из системы источника. Если на рассматриваемой таблице построен первичный ключ, то можно организовать выгрузку с последующим выделением «инкремента» по нему. При наличии метки времени — например, в технических полях с информацией о вставке данных или об обновлении — можно организовать выгрузку только измененных/добавленных записей за период времени. Информация о структуре партиций может пригодиться при создании аналогичной таблицы-«приемника».
Тело запроса:
DBA_VIEWS
Как известно, представления — это результаты запросов к некоторым таблицам базы данных. Представление словаря данных DBA_VIEWS позволяет увидеть SQL-запросы, лежащие в основе представлений. В листинге ниже показано, как получить текст представления OS_CUSTOMERS, принадлежащего пользователю oe.
Совет. Чтобы обеспечить полное отображение текста при использовании представления DBA_VIEWS, установите большое значение переменной long (например, SET LONG 2000). В противном случае вы увидите лишь несколько первых строк определения представления.
Пустые строки
В оракл есть одна очень интересная особенность, от которой они теперь уже никогда не смогут избавиться. Дело в том, что если вы кладете в БД пустую строку, то она сохраниться как NULL. Таким образом при последующем чтении вы никогда не получите пустой строки, а только NULL. Имейте так же в виду, что по этой же причине пустые строки не попадают в индекс, так что если вы будете делать запросы, план выполнения которых, будет использовать индекс, то ваше пустые (вернее NULL) строки вы никогда не получите, но об этом чуть позже.
Скрипт «Атрибутный состав таблиц»
Используемые системные таблицы: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v$nls_parameters.
Этот скрипт будет полезен для подготовки таблиц-«приемников» в хранилище данных, когда нужна подробная информация о таблице, ее взаимосвязях с другими таблицами, а также полном атрибутном составе. Через вспомогательную таблицу filter2 задается фильтрация таблиц, для которых осуществляется поиск ссылок (от и к). По умолчанию берутся таблицы из всех схем, кроме системных.
Тело запроса:
DBA_TAB_PARTITIONS
Представление DBA_TAB_PARTITIONS подобно представлению DBA_TABLES, но содержит детальную информацию о разделах таблиц. Благодаря DBA_TAB_PARTITIONS, можно узнать имя раздела, его максимальные значения, информацию о хранении раздела,статистику по разделу, а также прочую информацию, которая доступна в представлении DBA_TABLES. В листинге ниже показан простой запрос, использующий представление DBA_TAB_PARTITIONS.
Таблицы бывают разные
Кроме обычных таблиц в oracle как и во многих других БД есть так называемые индекс-таблицы, когда данные таблицы непосредственно лежат в индекс-дереве первичного ключа. Таким образом достигается сразу две вещи: во первых для чтения данных по первичному ключу вы имеете на одно чтение меньше, во вторых данные в таблице получаются упорядоченными по первичному ключу, так что операция ORDER BY PK будет выполняться без дополнительной сортировки. К недостаткам можно отнести тот факт, что отличить логирование в оперативные журнальные файлы данного индекса вы уже не сможете.
Еще один замечательный тип таблиц — это кластерные таблицы, которые позволяют хранить данные из двух или более таблиц кластеризованные по одному значению ключа в одном блоке данных. Это может быть весьма эффективно если вы всегда используете какие-нибудь таблицы совместно.
На основе кластерных таблиц есть еще кластерные хэш-таблицы, в которых для доступа вместо B-дерева используется таблица на основе хеша кластерного ключа. Звучит, конечно, очень интересно, но, честно говоря, на практике никогда не сталкивался.
Скрипт «Партиции и субпартиции»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
TABLE_NAME | Наименование таблицы |
PART_KEY | Столбцы по которым осуществлено партиционирование |
PARTITION_NAME | Наименование партиции |
PARTITION_POSITION | Номер партиции |
PARTITION_HEIGHT | Количество строк в партиции |
SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
SUBPARTITION_NAME | Наименование субпартиции |
SUBPARTITION_POSITION | Номер субпартиции |
SUBPARTITION_HEIGHT | Количество строк в субпартиции |
Используемые системные таблицы: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Скрипт будет полезен для получения характеристик (наименование, размеры) партиций при их непосредственном использовании в качестве источников данных.
Тело запроса:
DBA_TAB_MODIFICATIONS
Представление DBA_TAB_MODIFICATIONS показывает все изменения DML в таблице,произошедшие с момента последнего сбора статистики по этой таблице. Вот запрос к этому представлению:
База данных не обновляет представление DBA_TAB_MODIFICATIONS в реальном времени. Следовательно, вы можете и не увидеть изменений в различных таблицах, немедленно отраженных в этом представлении.
DBA_OBJECTS
Представление DBA_OBJECTS содержит информацию обо всех объектах базы данных, включая таблицы, индексы, пакеты, процедуры, функции, измерения, материализованные представления, планы ресурсов, типы, последовательности, синонимы, триггеры, представления и разделы таблиц (оно же секционирование). Как несложно догадаться, это представления удобно, когда нужно знать общую информацию относительно любого объекта базы данных. В листинге ниже показан запрос, предназначенный для нахождения времени создания и времени последней модификации объекта (LAST_DDL_TIME). Этот тип запроса поможет идентифицировать время модификации определенного объекта, что часто используется в процессе аудита.
Механизм восстановления данных
В СУБД Oracle можно включить архивацию вышеописанных оперативных журнальных файлов, и все изменения будут архивироваться. Таким образом при потере любого диска с блоками данных мы можем восстановить их на любой момент времени, включая момент прямо перед падением, накатив на последние архивные журнальные файлы текущий оперативный журнал.
Скрипт «Процедуры, функции и пакеты»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
NAME | Наименование процедуры/функции/пакета/заголовка пакета |
BODY | Тело |
TYPE | Тип (PACKAGE BODY, PACKAGE, FUNCTION, PROCEDURE) |
WRAPPED | Флаг «Закодировано тело или нет (wrapped)» |
Используемые системные таблицы: all_source
Тело запроса:
DBA_TAB_COLUMNS
Предположим, вы нужно узнать среднюю длину каждой строки таблицы или значение по умолчанию каждого столбца (если таковое есть). Представление DBA_TAB_COLUMNS — отличный способ быстро получить всю детальную информацию о столбцах таблиц схемы, как показано в листинге ниже.
DBA_IND_COLUMNS
Представления DBA_IND_COLUMNS по структуре подобно представлению DBA_CONS_COLUMNS и содержит информацию обо всех проиндексированных столбцах каждой таблицы. Эта информация важна при настройке производительности, когда вы замечаете,что запрос использует индекс, но вы не знаете точно, на каких столбцах этот индекс определен. Запрос, приведенный в листинге ниже, показывает, что таблица имеет индексы, определенные на неверных столбцах.
Совет. Взглянув на столбец INDEX_NAME, можно легко идентифицировать составные ключи. Если одно и то же вхождение INDEX_NAME появляется больше одного раза, значит, это составной ключ; и столбцы, являющиеся его частью, показаны в столбце COLUMN_NAME. Например,INVENTORY_PK — первичный ключ таблицы INVENTORIES, определенный на двух столбцах:PRODUCT_ID и WAREHOUSE_ID. Порядок столбцов в определении составного ключа можно узнать с помощью столбца COLUMN_POSITION.
Еще пара заметок для программиста
Если у вас колонка имеет тип VARCHAR2(100), то попытка туда запихнуть строку longString.substring(0, 100) не факт, что увенчается успехом, так как ограничение 100 в определении колонки по умолчанию относится к количеству байтов, а не символов, поэтому при наличии двухбайтовых символов вы можете попасть впросак. На самом деле данное поведение можно немного сконфигурировать, подробнее можно почитать тут. Хорошо если вы еще не пытаетесь выполнить вставку в бесконечном цикле, по принципу делать пока не получиться, ведь это «получиться» в данном случае никогда не наступит.
Ну и общая рекомендация для всех типов БД: никогда не делайте update всех колонок в таблице при изменении одного поля объекта. Кажется весьма очевидным, но как показывает практика, данный антипаттерн часто имеет место быть, поэтому я настоятельно рекомендую проверить, что ваши фреймворки делают UPDATE только действительно измененных полей.
DBA_MVIEWS
Представление словаря DBA_MVIEWS сообщает все о материализованных представлениях в базе данных, в том числе информацию, включено ли для них средство переписывания запросов. В листинге ниже демонстрируется использования этого представления.
Скрипт «Информация о таблицах»
Наименование колонки | Комментарий |
SCHEMA_NAME | Наименование схемы данных (OWNER) |
TABLE_NAME | Наименование таблицы |
COMMENTS | Комментарий к таблице |
HEIGHT | Количество строк в таблице (приблизительно) |
WIDTH | Количество столбцов |
DATETIME_COLUMNS | Столбцы с временнЫми типами данных и столбцы, исходя из наименования, предположительно являющиеся временнЫми метками (паттерны – %period%, %date%, %time%) |
AVG_ROW_LEN | Средняя длина строки в байтах |
PART_KEY | Столбцы по которым осуществлено партиционирование |
SUBPART_KEY | Столбцы по которым осуществлено субпартиционирование |
Используемые системные таблицы: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Запрос полезен для определения стратегии выгрузки данных из системы источника. Если на рассматриваемой таблице построен первичный ключ, то можно организовать выгрузку с последующим выделением «инкремента» по нему. При наличии метки времени — например, в технических полях с информацией о вставке данных или об обновлении — можно организовать выгрузку только измененных/добавленных записей за период времени. Информация о структуре партиций может пригодиться при создании аналогичной таблицы-«приемника».
Тело запроса:
Связывание переменных
Наверное об этом уже наслышан каждый программист, но я все же упомяну о такой обязательной техники, как связывание переменных. Дело в том, что для каждого уникального запроса строится план разбора и кладется в кэш. Если различных запросов очень много, как, например, весьма распространенный запрос по ID, то на каждый запрос буден генериться свой план, к тому же они будут вытеснять из кэша все другие планы, что может в разы увеличить время отклика вашей базы данных.
Стоит так же заметить, что не стоит этим злоупотреблять и использовать связывание для столбцов с небольшим количеством различных значений, как-то флаг is_deleted, ведь различных запросов в этом случае будет не так много, а, возможно, для более конкретного запроса СУБД удастся построить более эффективный план.
INDEX_STATS
Представление INDEX_STATS полезно для того, чтобы узнать, насколько эффективно индекс использует свое пространство. Крупные индексы имеют тенденцию со временем становиться несбалансированными, если происходит много удалений данных таблицы (а, следовательно, и индекса). Ваша цель — не упускать из виду эти крупные индексы,чтобы сохранять их сбалансированными.
Обратите внимание, что представление INDEX_STATS наполняется, только когда таблица подвергается анализу с помощью команды ANALYZE, как показано ниже:
Запрос из листинга ниже, использующий представление INDEX_STATS, помогает определить необходимость в перестройке индекса. Чтобы определить, следует ли перестраивать индекс, в запросе необходимо сосредоточиться на перечисленных ниже столбцах представления INDEX_STATS.
ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the DBMS_STATS package.
DBA_TABLES describes all relational tables in the database.
USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
Columns marked with an asterisk ( * ) are populated only if you collect statistics on the table with the DBMS_STATS package.
Owner of the table
Name of the table
Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized tables
Name of the cluster, if any, to which the table belongs
Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the IOT_TYPE column is not NULL, then this column contains the base table name.
If a previous DROP TABLE operation failed, indicates whether the table is unusable ( UNUSABLE ) or valid ( VALID )
Minimum percentage of free space in a block; NULL for partitioned tables
Minimum percentage of used space in a block; NULL for partitioned tables
Initial number of transactions; NULL for partitioned tables
Maximum number of transactions; NULL for partitioned tables
Size of the initial extent (in bytes); NULL for partitioned tables
Size of secondary extents (in bytes); NULL for partitioned tables
Minimum number of extents allowed in the segment; NULL for partitioned tables
Maximum number of extents allowed in the segment; NULL for partitioned tables
Percentage increase in extent size; NULL for partitioned tables
Number of process freelists allocated to the segment; NULL for partitioned tables
Number of freelist groups allocated to the segment; NULL for partitioned tables
Indicates whether or not changes to the table are logged; NULL for partitioned tables:
Indicates whether the table has been backed up since the last modification ( Y ) or not ( N )
Number of rows in the table
Number of used data blocks in the table
Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the DBMS_STATS package.
Average amount of free space, in bytes, in a data block allocated to the table
Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID
Average length of a row in the table (in bytes)
Average freespace of all blocks on a freelist
Number of blocks on the freelist
Number of threads per instance for scanning the table, or DEFAULT
Number of instances across which the table is to be scanned, or DEFAULT
Indicates whether the table is to be cached in the buffer cache ( Y ) or not ( N )
Indicates whether table locking is enabled ( ENABLED ) or disabled ( DISABLED )
Sample size used in analyzing this table
Date on which this table was most recently analyzed
Indicates whether the table is partitioned ( YES ) or not ( NO )
If the table is an index-organized table, then IOT_TYPE is IOT , IOT_OVERFLOW , or IOT_MAPPING . If the table is not an index-organized table, then IOT_TYPE is NULL.
Indicates whether the table is temporary ( Y ) or not ( N )
Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge ( Y ) or not ( N )
Indicates whether the table is a nested table ( YES ) or not ( NO )
Buffer pool for the table; NULL for partitioned tables:
Database Smart Flash Cache hint to be used for table blocks:
Solaris and Oracle Linux functionality only.
Cell flash cache hint to be used for table blocks:
See Also: Oracle Exadata Storage Server Software documentation for more information
Indicates whether partitioned row movement is enabled ( ENABLED ) or disabled ( DISABLED )
GLOBAL_STATS will be YES if statistics are gathered or incrementally maintained, otherwise it will be NO
Indicates whether statistics were entered directly by the user ( YES ) or not ( NO )
Indicates the duration of a temporary table:
SYS$SESSION - Rows are preserved for the duration of the session
SYS$TRANSACTION - Rows are deleted after COMMIT
Null - Permanent table
Indicates whether Oracle Database ignores blocks marked corrupt during table and index scans ( ENABLED ) or raises an error ( DISABLED ). To enable this feature, run the DBMS_REPAIR . SKIP_CORRUPT_BLOCKS procedure.
This column is obsolete
Owner of the cluster, if any, to which the table belongs
Indicates whether row-level dependency tracking is enabled ( ENABLED ) or disabled ( DISABLED )
Indicates whether table compression is enabled ( ENABLED ) or not ( DISABLED ); NULL for partitioned tables
Default compression for what kind of operations:
QUERY LOW ROW LEVEL LOCKING
QUERY HIGH ROW LEVEL LOCKING
ARCHIVE LOW ROW LEVEL LOCKING
ARCHIVE HIGH ROW LEVEL LOCKING
NO ROW LEVEL LOCKING
The QUERY LOW , QUERY HIGH , ARCHIVE LOW , ARCHIVE HIGH , QUERY LOW ROW LEVEL LOCKING , QUERY HIGH ROW LEVEL LOCKING , ARCHIVE LOW ROW LEVEL LOCKING , ARCHIVE HIGH ROW LEVEL LOCKING , and NO ROW LEVEL LOCKING values are associated with Hybrid Columnar Compression, a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.
Indicates whether the table has been dropped and is in the recycle bin ( YES ) or not ( NO ); NULL for partitioned tables
This view does not return the names of tables that have been dropped.
Indicates whether the table segment is READ-ONLY or not. Possible values:
YES - The table segment is READ-ONLY
NO - The table segment is not READ-ONLY
N/A - Not applicable. This value appears in a partitioned table, where there is no segment that relates to the logical table object.
Indicates whether the table segment is created. Possible values:
YES - The table segment is created.
NO - The table segment is not created.
N/A - Not applicable. This value appears in a partitioned table, where there is no segment that relates to the logical table object.
Result cache mode annotation for the table:
DEFAULT - Table has not been annotated
Indicates whether the table has the attribute clustering clause ( YES ) or not ( NO )
Indicates whether Heat Map tracking is enabled on the table
Modification time, creation time, or both for Automatic Data Optimization
Indicates whether the table has an identity column ( YES ) or not ( NO )
Indicates whether the table contains container-specific data. Possible values:
YES if the table was created with the CONTAINER_DATA clause
Indicates whether the In-Memory Column Store (IM column store) is enabled ( ENABLED ) or disabled ( DISABLED ) for this table
Indicates the priority for In-Memory Column Store (IM column store) population. Possible values:
Indicates how the IM column store is distributed in an Oracle Real Application Clusters (Oracle RAC) environment:
FOR CAPACITY [ HIGH | LOW ]
FOR CAPACITY QUERY
This column has a value based on where the segments lie for a table. For example, if the table is partitioned and is enabled for the IM column store, the value is NULL for ALL_TABLES but non- NULL for ALL_TAB_PARTITIONS .
Indicates the duplicate setting for the IM column store in an Oracle RAC environment:
Default collation for the table
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether this object is duplicated on this shard ( Y ) or not ( N )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether this object is sharded ( Y ) or not ( N )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether the table is an external table ( YES ) or not ( NO )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
The value for columnar compression in the storage cell flash cache. Possible values:
ENABLED : Oracle Exadata Storage will decide automatically whether to cache in columnar form
DISABLED : Oracle Exadata Storage is prevented from caching in columnar form
NO CACHECOMPRESS : Oracle Exadata Storage will cache in HCC format (no recompression)
FOR QUERY : Oracle Exadata Storage will recompress and cache in INMEMORY query high format
FOR CAPACITY : Oracle Exadata Storage will recompress and cache in INMEMORY capacity low format
This column is intended for use with Oracle Exadata.
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether the table is enabled for CONTAINERS() by default ( YES ) or not ( NO )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether the table is enabled for use with the container_map database property ( YES ) or not ( NO )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether the table is enabled for fetching an extended data link from the root ( YES ) or not ( NO )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
For internal use only
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
DEFAULT : Data is populated on all instances specified with the PARALLEL_INSTANCE_GROUP initialization parameter. If that parameter is not set, then the data is populated on all instances. This is the default.
NONE : Data is not populated on any instance.
ALL : Data is populated on all instances, regardless of the value of the PARALLEL_INSTANCE_GROUP initialization parameter.
USER_DEFINED : Data is populated only on the instances on which the user-specified service is active. The service name corresponding to this is stored in the INMEMORY_SERVICE_NAME column.
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates the service name for the service on which the IM column store should be populated. This column has a value only when the corresponding INMEMORY_SERVICE is USER_DEFINED . In all other cases, this column is null.
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
Indicates whether the table is used as the value of the container_map database property ( YES ) or not ( NO )
This column is available starting with Oracle Database 12 c Release 2 (12.2.0.1).
This SQL query returns the names of the tables in the EXAMPLES tablespace:
This SQL query returns the name of the tablespace that contains the HR schema:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_STATS package
Как запросить базу данных Oracle, чтобы отобразить имена всех таблиц в ней?
Это предполагает, что у вас есть доступ к представлению DBA_TABLES словаря данных. Если у вас нет этих привилегий, но они нужны, вы можете запросить, чтобы администратор БД явно предоставил вам привилегии для этой таблицы, или чтобы администратор БД предоставил вам SELECT ANY DICTIONARY привилегию или SELECT_CATALOG_ROLE роль (любая из которых позволит вам запросить любую таблицу словаря данных). ). Конечно, вы можете захотеть исключить определенные схемы, такие как SYS и SYSTEM имеющие большое количество таблиц Oracle, которые вам, вероятно, не нужны .
Кроме того, если у вас нет доступа DBA_TABLES , вы можете просмотреть все таблицы, к которым у вашей учетной записи есть доступ, через ALL_TABLES представление:
Хотя это может быть подмножеством таблиц, доступных в базе данных ( ALL_TABLES показывает информацию обо всех таблицах, к которым у вашего пользователя есть доступ).
Если вас интересуют только те таблицы, которыми вы владеете, а не те, к которым у вас есть доступ, вы можете использовать USER_TABLES :
Поскольку он USER_TABLES содержит информацию только о тех таблицах, которыми вы владеете, у него нет OWNER столбца - владельцем по определению является вы.
Oracle также имеет ряд устаревших данных словаря views-- TAB , DICT , TABS и CAT для example-- , которые можно было бы использовать. В целом, я бы не советовал использовать эти устаревшие представления, если вам абсолютно не нужно перенести свои сценарии в Oracle 6. Oracle не изменяла эти представления в течение длительного времени, поэтому у них часто возникают проблемы с объектами более новых типов. Например, TAB и CAT представления, и представления показывают информацию о таблицах, которые находятся в корзине пользователя, в то время как [DBA|ALL|USER]_TABLES все представления отфильтровывают их. CAT также показывает информацию о материализованных журналах представлений с TABLE_TYPE «TABLE», что вряд ли будет тем, что вы действительно хотите. DICT объединяет таблицы и синонимы и не говорит вам, кто владеет объектом.
Нужно ли программисту прикладных приложений понимать как работает БД? Том Кайт, признанный специалист Oracle, автор знаменитой колонки asktom, в своей книге «Oracle для профессионалов. Архитектура и основные особенности.» настаивает, что это просто необходимо. Даже если в вашей команде есть грамотный администратор, знание того, как работает СУБД Oracle поможет вам лучше понимать друг друга и эффективней взаимодействовать, не говоря уже о случае, когда такого специалиста у вас нет. В данном топике я упомяну об основных вещах, понимание которых позволит грамотно работать с БД Oracle и использовать некоторые её особенности с большой отдачей для вашего приложения. Если же вы уже прочитали вышеупомянутую книгу Тома Кайта, то можете просто исползовать эту статью в качестве памятки. Одно замечание — книжку я читал давно, и тогда еще последней версией БД Oracle была 9i, курсы по администрированию я тоже проходил по девятке, так что, если в десятке и выше что-то поменялось и добавилось, то не обессудьте. Хотя я пишу о довольно фундаментальных вещах, которые вряд ли сильно поменяись.
Неблокирующее чтение и сегмент отката
Одной из наиболее замечательных особенностей СУБД Oracle является неблокирующее чтение, которое достигается за счет сегмента отката. Запросы к Oracle на чтение никогда не блокируются, так как данные почти всегда могут быть прочитаны из сегмента отката.
Сегмент отката дает еще одну плюшку: из него можно попытаться считать немного устаревшие данные для какой-нибудь таблицы, которые были в ней на определенный момент. Называется данная фича — flashback.
Однако иногда сегмент отката может подложить свинью: если у вас есть большой job для bulk удаления данных (удаление генерирует всех больше данных в сегменте отката), то вы можете получить ORA-01555: snapshot too old. Главное что в этом случае надо помнить — это то, что не надо переписывать ваш job, чтобы он коммитил через каждые N операций, а нужно использовать отдельный специально созданный сегмент отката для таких операций.
Уровни изоляции транзакций
В Oracle вообще нет уровня изоляции READ_UNCOMMITED. Дело в том, что в других базах данных он используется для достижения максимального параллелизма путем удаления блокировок чтения. Но в Oracle чтение и так всегда выполняется без блокировок, таким образом мы уже имеем все преимущества, которые может дать этот уровень, не вводя никаких дополнительных ограничений.
Вообще, в Oracle явно доступно всего два уровня изоляции: по умолчанию используется READ_COMMITTED, но при желании вы можете установить SERIALIZABLE.
Однако на уровне операторов (SELECT, UPDATE и т.д.) у вас по умолчанию уже есть REPEATABLE_READ, т.е. в рамках одного оператора вы всегда получаете согласованное чтение, что достигается конечно же за счет сегмента отката. Мне всегда очень нравился пример приводимый Томом Кайтом для описания того, что это дает. Допустим у вас есть очень большая таблица со счетами и вы выполняете SELECT на получение суммы. В Oracle, в отличие от многих других БД, даже если в середине вашего запроса другая транзакция переведет некоторую суммы с первого счета на последний, вы в итоге все равно получите данные актуальные на начало вашего запроса, так как дойдя до последний строчки ваш SELECT увидит, что строчка была изменена, пойдет в сегмент отката и прочитает данные, которые были в этой ячейке на момент начала выполнения запроса. Во многих других базах данных, вы получите ответ в виде суммы, никогда не существующей в вашей таблице. Однако в Oracle в данном случае есть опасность получить ORA-01555: snapshot too old.
В дополнение к стандартным уровням изоляции в Oracle еще есть так называемые READ_ONLY транзакции, которые дают REPEATABLE_READ в рамках всей транзакции, а не только в рамках одного оператора. Но как следует из названия, в такой транзакции вы можете выполнять только чтение.
Читайте также: