казинакприязнь к тебе могла бы возникнуть у меня при условии, что ты симпатишный, и что ты - ДЕВУШКА
А что важней, если не секрет?

казинакMakar4ik...Поясню, зачем этот зверь:
Есть джавовская библиотека Hibernate. Она строит в мозгах объектную модель БД.
Строит на основе слепка модели, которая хранится в 7-ми таблицах в БД.

А скрипт должен проверить этот слепок на 30+ разных недомоганий, несоответствий, и кривых рук разработчиков.
Итого, мне 30+ раз вычитать системные вьюшки приходится, часто с довольно сложными джоинами.

Через скрипт, при предварительной вычитке в таблицу, самый тяжкий из запросов выполняется 2 секунды.
Без этой вычитки и индексации (прям по вьюшкам) - минут 40 выходит...
запросы к этим all_tables, all_views, ALL_CONS_COLUMNS, ALL_CONSTRAINTS и т.д., материализуй.
И сделай рефреш раз в 5-10 мин.

у нас помогло...

зы
Там, мало того, что данных немало, так и еще эти словарные вьюхи нехилые планы дают, с кучей путей разных типов и с афигенными стоимостями.
И если к этим словарным вьюхам обращаются десятки и сотни юзеров в сек/мин, то бд вешается напрочь

в общем, типичная проблема для веб приложения и ORM....Раз я уж отметился в этой теме (всех с Рождеством Христовым!), то парочку наблюдений для ТС (я цитирую не его, но, видимо казинак просто более подробно описал, куда надо смотреть, да и сам я с этим немного сталкивался)

1. Если уж действительно интересуют именно ALL_TABLES, ALL_что-то-еще и т.д., то я вижу следущие варианты:
-- во-первых, в принципе отказаться от ALL_* и юзать DBA_*. Насколько я понял -- права у ТС соответствующие есть. Причина -- дополнительные (многие избыточные) проверки
-- как предложили, можно материализовать их в отдельные таблички (насколько понимаю, простые MV здесь не сработают, поэтому, это велосипед)
-- можно юзать with с кляузой /*+ materialize */ (это просто для набора решений)
-- можно подсмотреть, из чего состоят требуемые вьюшки и составить свои над базовыми таблицами (убрав всякие лишние проверки, появившиеся после всяких DBVault и Editions), возможно будет достаточно родных DBA_INTERNAL_* вьюшек (с 11g?). Выглядит сложновато, но на самом деле достаточно просто (даже для 30 экземпляров). Следует отметить, что пересоздание ALL_OBJECTS и ALL_SYNONYMS из более старых версий в свое время было приведено как workaround от support при смене версии именно из-за усложнившихся проверок

2. По поводу возвращения датасета:
Оно конечно можно вернуть или как REF_CURSOR, или VIEW, PIPELINED функция или даже заполненная временная таблица, но зачем?
Я так понимаю, тебе нужен протокол [не]соответствия и, возможно, команды по привидению в порядок
Т.е. либо сформировать скрипт, чтоб ты проглядел глазками и запустил на выполнение (возможно, поправив), либо они уже вообще были запущены в твоей процедурке.
Для этого датасет как-то лишний