Last ddl time oracle что это
Обновлено: 07.07.2024
Пакет PL / SQL представляет собой логическое объединение связанной подпрограммы (процедуры / функции) в один элемент. Пакет компилируется и сохраняется как объект базы данных, который можно использовать позже.
В этом уроке вы узнаете
Компоненты пакетов
Пакет PL / SQL состоит из двух компонентов.
Спецификация упаковки
Спецификация пакета состоит из объявления всех открытых переменных, курсоров, объектов, процедур, функций и исключений.
Ниже приведены некоторые характеристики спецификации пакета.
Синтаксис
Приведенный выше синтаксис показывает создание спецификации пакета.
Корпус
Он состоит из определения всех элементов, которые присутствуют в спецификации пакета. Он также может иметь определение элементов, которые не объявлены в спецификации, эти элементы называются частными элементами и могут вызываться только из пакета.
Ниже приведены характеристики корпуса упаковки.
Синтаксис:
- Приведенный выше синтаксис показывает создание тела пакета.
Теперь мы увидим, как ссылаться на элементы пакета в программе.
Ссылающиеся элементы пакета
Как только элементы объявлены и определены в пакете, нам нужно обратиться к элементам, чтобы использовать их.
Все общедоступные элементы пакета могут быть переданы путем вызова имени пакета, за которым следует имя элемента, разделенное точкой, т.е. «<имя_пакета>. <Имя_элемента>».
Открытая переменная пакета также может быть использована таким же образом для назначения и извлечения значений из них, то есть «<имя_пакета>. <Имя_переменной>».
Создать пакет в PL / SQL
В PL / SQL всякий раз, когда пакет вызывается / вызывается в сеансе, для этого пакета будет создан новый экземпляр.
Oracle предоставляет возможность инициализировать элементы пакета или выполнять какие-либо действия во время создания этого экземпляра с помощью «Инициализация пакета».
Это не что иное, как блок выполнения, который записывается в теле пакета после определения всех элементов пакета. Этот блок будет выполняться всякий раз, когда пакет передается в первый раз в сеансе.
Синтаксис
- Приведенный выше синтаксис показывает определение инициализации пакета в теле пакета.
Форвардные декларации
На частные элементы можно ссылаться, только если они уже объявлены в теле пакета. По этой причине используется предварительное объявление. Но это довольно необычно в использовании, потому что в большинстве случаев закрытые элементы объявляются и определяются в первой части тела пакета.
Синтаксис:
Приведенный выше синтаксис показывает предварительное объявление. Закрытые элементы объявляются отдельно в передней части пакета, и они были определены в более поздней части.
Использование курсоров в пакете
В отличие от других элементов, нужно соблюдать осторожность при использовании курсоров внутри пакета.
Если курсор определен в спецификации пакета или в глобальной части тела пакета, то один раз открытый курсор будет сохраняться до конца сеанса.
перегрузка
Это полезно, когда многие подпрограммы должны выполнять одну и ту же задачу, но способ вызова каждой из них должен быть разным. В этом случае имя подпрограммы будет оставаться одинаковым для всех, а параметры будут изменены в соответствии с оператором вызова.
Пример 1 : В этом примере мы собираемся создать пакет для получения и установки значений информации о сотруднике в таблице «emp». Функция get_record возвращает вывод типа записи для данного номера сотрудника, а процедура set_record вставляет запись типа записи в таблицу emp.
Шаг 1) Создание спецификации пакета
Вывод:
Код Объяснение
- Строка кода 1-5 : создание спецификации пакета для guru99_get_set с одной процедурой и одной функцией. Эти два в настоящее время являются открытыми элементами этого пакета.
Шаг 2) Пакет содержит тело пакета, в котором будут определены все действительные процедуры и функции. На этом шаге создается тело пакета.
Вывод:
Код Объяснение
- Строка кода 7 : создание тела пакета.
- Строка кода 9-16 : определение элемента set_record, который объявлен в спецификации. Это то же самое, что и определение автономной процедуры в PL / SQL.
- Строка кода 17-24: определение элемента get_record. Это то же самое, что и определение автономной функции.
- Строка кода 25-26: определение части инициализации пакета.
Шаг 3) Создание анонимного блока для вставки и отображения записей, ссылаясь на созданный выше пакет.
Вывод:
Объяснение кода:
- Строка кода 34-37: заполнение данных для переменной типа записи в анонимном блоке для вызова элемента set_record пакета.
- Строка кода 38: был сделан вызов set_record пакета guru99_get_set. Теперь пакет создается и сохраняется до конца сеанса.
- Часть инициализации пакета выполняется, так как это первый вызов пакета.
- Запись вставляется элементом set_record в таблицу.
- Строка кода 41: вызов элемента get_record для отображения сведений о введенном сотруднике.
- Пакет передается во второй раз во время вызова get_record. Но на этот раз часть инициализации не выполняется, поскольку пакет уже инициализирован в этом сеансе.
- Строка кода 42-45: печать сведений о сотруднике.
Зависимость в пакетах
Поскольку пакет представляет собой логическую группу связанных вещей, он имеет некоторые зависимости. Ниже приведена зависимость, которую нужно позаботиться.
Информация о пакете
Как только информация о пакете создана, информация о пакете, такая как источник пакета, детали подпрограммы и информация о перегрузке, доступна в таблицах определения данных Oracle.
Ниже в таблице приведены таблица определения данных и информация о пакете, которая доступна в таблице.
Программист может использовать это для записи файлов операционной системы любого типа, и файл будет записан непосредственно на сервер базы данных. Имя и путь к каталогу будут указаны при написании.
Резюме
Теперь мы изучили пакеты на PL / SQL, и теперь вы сможете работать следующим образом.
Существует несколько важных представлений словаря базы данных, которые можно использовать для нахождения детальной информации о любом из объектов базы данных, о которых говорилось в этой главе. Администраторы баз данных также интенсивно используют представления словаря данных, чтобы управлять различными объектами схемы. Здесь приводится краткий список важнейших представлений, часть из которых упоминалась выше. Полные данные о типах информации, которую можно получить от каждого из этих представлений, доступны по команде DESCRIBE (например, DESCRIBE DBA_CATALOG).
В этой статье блога будут описаны некоторые важные представления словаря данных, которые помогут управлять объектами, не хранящими данные (т.е. объектами, которые не относятся к таблицам и индексам). Ниже приведен список важнейших представлений словаря данных для просмотра объектов базы данных.
- DBA_SYNONYMS. Информация о синонимах базы данных.
- DBA_TRIGGERS. Информация о триггерах.
- DBA_SEQUENCES. Информация о созданных пользователем последовательностях.
- DBA_DB_LINKS. Информация о связях базы данных.
Как упоминалось ранее, представление DBA_OBJECTS предоставляет важную информацию обо всех перечисленных объектах, наряду с некоторыми другими типами объектов базы данных. Однако перечисленные представления содержат детальную информацию о каждом объекте, такую как исходный текст триггера, которую вы не получите из представления DBA_OBJECTS.
Управление такими объектами, как таблицы и представления, осуществляется ссылкой на представления словаря данных, наподобие DBA_TABLES и DBA_VIEWS. Существуют также отдельные представления для секционированных таблиц. Давайте рассмотрим ключевые представления словаря данных, относящиеся к таблицам и индексам.
DBA_OBJECTS
Представление DBA_OBJECTS содержит информацию обо всех объектах базы данных, включая таблицы, индексы, пакеты, процедуры, функции, измерения, материализованные представления, планы ресурсов, типы, последовательности, синонимы, триггеры, представления и разделы таблиц (оно же секционирование). Как несложно догадаться, это представления удобно, когда нужно знать общую информацию относительно любого объекта базы данных. В листинге ниже показан запрос, предназначенный для нахождения времени создания и времени последней модификации объекта (LAST_DDL_TIME). Этот тип запроса поможет идентифицировать время модификации определенного объекта, что часто используется в процессе аудита.
DBA_TABLES
Представление DBA_TABLES содержит информацию обо всех реляционных таблицах базы данных. Представление DBA_TABLES — основной справочник для нахождения информации о хранении, количестве строк в таблице, состоянии протоколирования, информации буферного пула и многих других деталях. Ниже приведен простой пример запроса представления DBA_TABLES:
На заметку! Представление DBA_ALL_TABLES содержит информацию обо всех объектных и реляционных таблицах в базе данных, в то время как представление DBA_TABLES ограничено только реляционными таблицами.
Представление DBA_TABLES служит для нахождения таких вещей, как включено ли сжатие и отслеживание зависимостей на уровне строки, и была ли таблица уничтожена и помещена в корзину (Recycle Bin).
DBA_EXTERNAL_TABLES
Представление DBA_EXTERNAL_TABLES показывает подробности о любой внешней таблице в базе данных, включая их тип доступа, параметры доступа и информацию о каталоге.
DBA_TAB_PARTITIONS
Представление DBA_TAB_PARTITIONS подобно представлению DBA_TABLES, но содержит детальную информацию о разделах таблиц. Благодаря DBA_TAB_PARTITIONS, можно узнать имя раздела, его максимальные значения, информацию о хранении раздела,статистику по разделу, а также прочую информацию, которая доступна в представлении DBA_TABLES. В листинге ниже показан простой запрос, использующий представление DBA_TAB_PARTITIONS.
DBA_PART_TABLES
Представление DBA_PART_TABLES содержит информацию о типе схемы раздела и прочих параметрах хранения разделов и подразделов. Узнать тип каждого раздела каждой секционированной таблицы можно с помощью следующего запроса:
DBA_TAB_MODIFICATIONS
Представление DBA_TAB_MODIFICATIONS показывает все изменения DML в таблице,произошедшие с момента последнего сбора статистики по этой таблице. Вот запрос к этому представлению:
База данных не обновляет представление DBA_TAB_MODIFICATIONS в реальном времени. Следовательно, вы можете и не увидеть изменений в различных таблицах, немедленно отраженных в этом представлении.
DBA_TAB_COLUMNS
Предположим, вы нужно узнать среднюю длину каждой строки таблицы или значение по умолчанию каждого столбца (если таковое есть). Представление DBA_TAB_COLUMNS — отличный способ быстро получить всю детальную информацию о столбцах таблиц схемы, как показано в листинге ниже.
DBA_VIEWS
Как известно, представления — это результаты запросов к некоторым таблицам базы данных. Представление словаря данных DBA_VIEWS позволяет увидеть SQL-запросы, лежащие в основе представлений. В листинге ниже показано, как получить текст представления OS_CUSTOMERS, принадлежащего пользователю oe.
Совет. Чтобы обеспечить полное отображение текста при использовании представления DBA_VIEWS, установите большое значение переменной long (например, SET LONG 2000). В противном случае вы увидите лишь несколько первых строк определения представления.
DBA_MVIEWS
Представление словаря DBA_MVIEWS сообщает все о материализованных представлениях в базе данных, в том числе информацию, включено ли для них средство переписывания запросов. В листинге ниже демонстрируется использования этого представления.
DBA_INDEXES
Представление словаря DBA_INDEXES служит для того, чтобы узнать все необходимое об индексах в базе данных, включая имя индекса, его тип, таблицу и табличное пространство, к которому он относится. Определенные столбцы, наподобие BLEVEL (сообщает уровень B-дерева индекса) и DISTINCT_KEYS (количество разных значений ключа индекс), наполняются, только если собрана статистика по индексу с использованием пакета DBMS_STATS.
DBA_IND_COLUMNS
Представления DBA_IND_COLUMNS по структуре подобно представлению DBA_CONS_COLUMNS и содержит информацию обо всех проиндексированных столбцах каждой таблицы. Эта информация важна при настройке производительности, когда вы замечаете,что запрос использует индекс, но вы не знаете точно, на каких столбцах этот индекс определен. Запрос, приведенный в листинге ниже, показывает, что таблица имеет индексы, определенные на неверных столбцах.
Совет. Взглянув на столбец INDEX_NAME, можно легко идентифицировать составные ключи. Если одно и то же вхождение INDEX_NAME появляется больше одного раза, значит, это составной ключ; и столбцы, являющиеся его частью, показаны в столбце COLUMN_NAME. Например,INVENTORY_PK — первичный ключ таблицы INVENTORIES, определенный на двух столбцах:PRODUCT_ID и WAREHOUSE_ID. Порядок столбцов в определении составного ключа можно узнать с помощью столбца COLUMN_POSITION.
INDEX_STATS
Представление INDEX_STATS полезно для того, чтобы узнать, насколько эффективно индекс использует свое пространство. Крупные индексы имеют тенденцию со временем становиться несбалансированными, если происходит много удалений данных таблицы (а, следовательно, и индекса). Ваша цель — не упускать из виду эти крупные индексы,чтобы сохранять их сбалансированными.
Обратите внимание, что представление INDEX_STATS наполняется, только когда таблица подвергается анализу с помощью команды ANALYZE, как показано ниже:
Запрос из листинга ниже, использующий представление INDEX_STATS, помогает определить необходимость в перестройке индекса. Чтобы определить, следует ли перестраивать индекс, в запросе необходимо сосредоточиться на перечисленных ниже столбцах представления INDEX_STATS.
Если сравнивать свою функцию, написанную на PL/SQL (тем более на PL/pgSQL) со стандартной, то все будет довольно печально. Так что если есть стандартная функция, то изобретать велосипед не надо (:
Если написать свою на C в PostgreSQL, то будет ровно одно и то же.
А вот если написать свою в Oracle на чем-нибудь компилируемом, то не знаю, не пробовал. Предполагаю, что будет чуть проигрывать.
Точнее, в этом примере считается итоговый процент отношения одного показателя к другому.
Ага, можно так. Мне, правда, не очень нравится этот трюк с массивом: экономим на создании типа (а зачем?), но ведь код получается непонятный.
Кстати, как вариант, можно было бы обойтись обычной суммой: sum(. ) / nullif(sum(. ), 0) .
А для отношения доли к сумме в Oracle даже есть стандартная функция ratio_to_report .
- даём пользователю возможность ввести человекопонятную формулу без необходимости проверки на 0/null
- можем проверить её корректность на этапе при сохранении
- разрешаем использовать только те агрегатные функции, которые доступны в схеме calc
А какие сложности? Отдельные функции же можно вызывать самостоятельно, вне агрегата. Так что при необходимости для них легко написать любые тесты.
В Oracle прекрасно дебажится обычными методами в PL/SQL Developer Заметил особенность реализации аналитических функций в Oracle. Если в OVER() используем ORDER BY, то в ODCIAggregateTerminate зайдёт только при смене значения в ORDER BY. Надо об этом помнить, а то можно получить совсем неожиданные результаты.Пример:
Функция GROUP_NUMBER — назначает номер группы при смене значения
Возвращает неожиданный результат
А вот ожидаемый результат
Копипаст подвёл. Первый запрос должен выглядеть так. Т.е. без ROWNUM в ORDER BY
Есть такое дело, тоже натыкался. Спасибо, что напомнили, это важный момент.И PostgreSQL себя точно так же ведет, кстати.
И это правильно, иначе попробуйте представить себе что должен был бы тогда возвращать SUM()OVER() в следующем запросе:
Если бы мы хотели добавить агрегировать и при изменении N то должны были бы и показать/определить порядок сортировки при этой агрегации, например:
Но интуитивно (а точнее — по аналогии с обычным ORDER BY) кажется, что если точный порядок не указан, то подразумевается какой-нибудь, не важно какой конкретно. Собственно, добавление ROWNUM ровно к этому и приводит.
Invoked by Oracle as the last step of aggregate computation.
У такого окна изменение LAST_DDL_TIME и есть последний шаг агрегации. Функция-то все-таки агрегатная, если бы она возвращала каждый раз разные значения, это уже не агрегатной функцией было бы.
Кстати, а чем не подошел dense_rank?
А что именно вы хотите-то? Ваш пример недетерминирован. А из словесного описания функции как раз напрашивается dense_rank.
Сейчас постараюсь придумать живой пример…
Например, есть навигационные данные с транспортного средства. Необходимо выбрать все интервалы превышения скорости и проанализировать их: время превышения, средняя скорость, пробег и т.д.
Запрос будет выглядеть примерно так:
Стандартно присвоение группы делается 2мя аналитическими надзапросами LAG + SUM. А здесь написал функцию. Кстати, проверил функция примерно в 2 раза медленнее чем LAG + SUM
Теперь давайте вернемся ко второй причине, связанной с экономным использованием разработчиком ограниченного ресурса (сегментов отката). Это проблема конфигурирования - необходимо предусмотреть достаточно места в сегментах отката для поддержки транзакций требуемого размера. Фиксация в цикле - мало того, что обычно медленнее, это одна из наиболее частых причин возникновения печально известной ошибки ORA-01555. Давайте рассмотрим эту проблему более детально.
tkyte@TKYTE81 create table t as select * all objects;
tkyte@TKrE81 create index t idx on t(object name); Index created.
Я затем отключил все сегменты отката и создал один маленький сегмент:
tkyte@TKYTE816> create rollback segment rbs small storage (initial 64k 2 next 64k minextents 2 maxextents 4) tablespace tools;
Rollback segment created.
Теперь, когда доступен только маленький сегмент отката, я ввел следующий блок кода для выполнения необходимых изменений:
2 for x in (select rowid rid, objectname, rownum r
4 where object name > chr(O))
7 set object name = lower(x.object name)
8 where rowid = x.rid;
9 if (mod(x.r,100) = 0) then
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name RBS SMALL too small ORA-06512: at line 2
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 10
ORA-06512: at line 6
Но между этими двумя ошибками есть, однако, важные отличия.
Ошибка ORA-01555 оставляет изменения в абсолютно неизвестном состоянии.
Часть изменений сделана, часть - нет.
Мы ничего не можем сделать, чтобы избежать ошибки ORA-01555, если зафиксировали изменения в цикле FOR по курсору.
Всегда можно избежать ошибки ORA-01562, в1делив системе соответствующие ресурсы. Второй ошибки можно избежать, создав сегменты отката нужного размера, а первой - нет.
Конечно, самая большая проблема подхода с фиксацией до завершения транзакции состоит в том, что в случае прекращения изменений, база данных остается в неопределенном состоянии. Если заранее не подготовиться к этому, очень сложно продолжить выполнение неудачно завершившейся транзакции с момента останова. Например, если к столбцу применяется не функция LOWER(), а другая, например
last ddl time = last ddl time + 1;
Если цикл изменения прерван по ходу, как нам его перезапустить? Нельзя просто выполнить его повторно, поскольку к некоторым датам будет добавлено значение 2, а к некоторым - 1. При повторном сбое окажется, что к некоторым добавлено значение 3, к другим - 2, к остальным - I и так далее. Необходим более сложный подход, позволяющий фрагментировать данные. Например, можно обрабатывать все значения object names, начинающиеся с А, затем - начинающиеся с В, и так далее:
Читайте также: