Oracle перенести индекс в другое табличное пространство
Мы уже рассказывали о том, почему секционирование баз данных очень важно для производительности DLP-системы и как мы реализовывали его в PostgreSQL. В этой статье речь пойдет об Oracle.
Специфика использования СУБД в DLP-решениях состоит в том, что объем данных прирастает очень быстро. Их невозможно держать в оперативном архиве, и долговременное хранение – это необходимость в компании численностью свыше хотя бы 50 человек. При этом оперативный архив наполняется так быстро, что отдавать информацию в долгосрочный архив приходится раз в 2 недели или чаще. Использование только встроенных средств СУБД требует знаний и опыта. Это главная сложность, и она, в общем-то, очевидна «на берегу».
Кроме того, возникают проблемы, не очевидные сразу. Как вернуть из долгосрочного архива партицию с данными более старой версии приложения и прицепить к более свежей? Что делать, если у них разных формат хранения данных? Что делать, если подключение секции было прервано, и она «зависла» между долговременным и оперативным архивом?
В целом, решение этих вопросов сводится к двум основным техническим задачам: автоматизация управления секциями в СУБД Oracle (отключение и подключение) и система «отката» секций в случае, если при подключении что-то пошло не так.
Что не так со встроенными механизмами Oracle DB
Убирать данные на ленту и возвращать их из долговременного архива помогает опция Partitioning, с ее помощью можно разделить таблицу на части по какому-то принципу, например, по диапазону дат. Кроме управляемости и доступности, такое разделение еще позволяет повысить производительность. Каждый период хранится в отдельном табличном пространстве, что позволяет с помощью технологии transportable tablespace, достаточно быстро перемещать табличные пространства между различными отчетными и архивными БД с различными версиями и платформами. Но проблема в том, что стандартных механизмов не всегда достаточно: они позволяют только создавать базовые структуры без учета специфики приложения. А дальше администратор вынужден создавать вокруг них кучу инструментов управления. Да и сам процесс отключения-подключения-переноса требует навыков администрирования БД. Поэтому задача минимум – автоматизировать этот процесс, сделать его доступным для администраторов приложений.
Мы разработали набор скриптов, с помощью которых можно управлять секционированными таблицами, получать любую информацию о них и т.д. Не нужно знание команд, опыта работы с СУБД. Администратор приложения просто запускает скрипт или выбирает в интерфейсе нужное действие, указывает нужную партицию, и все происходит само собой.
(Не)совместимость версий
Итак, мы автоматизировали отключение секций и отправку их в долгосрочный архив. Но с долгосрочным архивом есть проблема: иногда его нужно вернуть.
Допустим, администратор перенес в него несколько секций в старой версии. Через год вышла новая версия, в которой добавились новые поля в таблицы, новые индексы, и в долгосрочный архив ушло еще некоторое количество секций. А потом безопасник расследует некий инцидент, и ему необходимо поднять данные двухлетней давности, т.е. поднять секцию несколько версий назад и каким-то образом подключить ее к БД.
Структура таблиц новой версии иногда отличается от исторической. Необходим ряд проверок и изменений для архивной секции. Проверка всегда начинается со сравнения текущей версии Solar Dozor и версии СУБД, и подключаемой партиции. Если есть различия, запускаются процедуры, корректирующие метаданные, добавляются необходимые поля, индексы, ключи, проверяется консистентность подключаемых данных, и пр., удаляется лишнее.
Дополнительные сложности приносит и использование для поиска в Solar Dozor текстовых индексов. Есть некоторые баги, связанные с EXCHANGE PARTITION для текстовых индексов, созданных в разных версиях СУБД или при использовании transportable tablespace (до 12 версии index metadata corruption). Патчи не всегда есть для нужной версии или платформы. Пересоздавать индексы при подключении – не быстрая и достаточно ресурсоемкая процедура. Пришлось «впилить» workaround-ы в процедуры подключения партиции. Структура DR$ таблиц текстовых индексов подключаемой партиции «выравнивается» с текущей, апдейтится поле таблицы ctxsys.dr$index.
Есть и защита от разных ошибок администраторов. Например, на уровне приложения запрещены любые действия с партицией, в которую в данный момент заливаются данные и имеющую статус «current».
«Хьюстон, у нас проблема»
В ходе реализации этих механизмов мы столкнулись с еще одной проблемой, неожиданно часто возникающей у заказчиков. В процессе отключения что-то может пойти не так, вплоть до банального отключения электричества, так что подключение секции может в любой момент прерваться. В результате получаем базу, которая находится в «промежуточном» состоянии.
В СУБД Oracle есть DDL и DML. В DML реализован механизм для обеспечения транзакционной целостности, который откатывает назад результаты, если транзакция не прошла. В DDL такого механизма нет, и любые действия с секцией – это путь в один конец.
Мы разработали механизм, который проверяет выполнение всех шагов по отключению-подключению партиции и корректирует возникающие проблемы. В случае возникновения проблем механизм перезапускает операции с партицией с того момента, когда что-то пошло не так. Ошибки при отключении-подключении логируются, и это позволяет в любой момент узнать, какие проблемы и когда возникали.
Эта статья посвящена сравнению эффективности различных способов переноса таблицы в другое табличное пространство. По мотивам ответа Тома Кайта на вопрос, первоначально заданный еще в апреле 2001 года.
Как выполняется alter table . move tablespace?
Не Мог бы ты разъяснить, как реализован оператор alter table t_name move tablespace . Это можно делать в оперативном режиме (online) и без журнализации (с опцией nologging). Но как данные переностяся из одного табличного пространства в другое? Приходится ли серверу формировать операторы insert и передавать данные через буферный кэш как при обычной вставке или происходит что-то типа непосредственной вставки?
Я столкнулся со следующей проблемой, для которой хотел бы найти быстрый способ решения:
Есть таблица размером 2,5 Гбайта. Я хочу перенести ее из табличного пространства a в b . Все файлы a и b разбиты на полосы и находятся на разных дисках. Эта таблица - независима. Я имею ввиду, что для нее не заданы триггеры и ограничения целостности.
Первый способ:
Второй способ:
Будет ли второй способ работать быстрее, чем первый, за счет использования непосредственной вставки?
Какой сопособ будет работать быстрее, и почему? Допускает ли первый способ распараллеливание? Булет ли существенная разница в использовании сегмента отката и пространства для сортировки?
Ответ Тома Кайта
Действие move в оперативном режиме может выполняться ТОЛЬКО для таблицы, организованной по индексу (index organized table - IOT), но не для обычной таблицы, организованной в виде кучи.
Имеет смысл делать так:
Оператор alter table t move tablespace b nologging; перенесет таблицу (с журнализацией, если она была установлена), а затем установит атрибут nologging .
При выполнении move для переноса таблицы SQL не используется. Никакие вставки не выполняются.
Такой перенос хорош тем, что все индексы, привилегии и т.п. остаются. Необходимо только перестроить (но не пересоздать) индексы после переноса.
Второй способ может сработать быстрее, если использовать параллельные вставки (проверьте, что для задания подсказок оптимизатору используется /*+ , а не просто /* .) Но для этого вам придется больше потрудиться. Для таблицы размером 2,5 Гбайт я не уверен, что оно того стоит - может потребоваться больше времени на разработку процедуры переноса, чем на сам перенос.
Перенос табличного пространства с опцией nologging
В Oracle Enterprise Edition 8.1.7.2 я делаю следующее:
Я не знаю, работает это с журнализацией или без, но после переноса таблица в режим nologging не переводится. А в версии Oracle 8.1.6 результат другой?
Ответ Тома Кайта
Хорошо, параметр logging / nologging имеет два значения, в зависимости от контекста.
В том контексте, где вы использовали его выше, вы попросили перенос выполнять без журнализации, если объект допускает работу без журналлизации.
Если выполнить оператор " alter table t nologging ", происходит изменение атрибута logging / nologging .
Как всегда, понять это поможет пример. Мы создадим таблицу, перенесем ее и посмотрим, сколько данных повторного выполнения будет сгенерировано при использовании различных методов переноса:
Итак, есть тестовая таблица. Она первоначально создана в табличном пространстве UTILS и:
ее режим журнализации - YES (журнализация установлена). Теперь давайте посмотрим, сколько данных повторного выполнения уже сгенерировал сеанс, и сохраним это значение в подставляемой переменной V
А теперь выполним вашу команду. Эту команду можно на русском языке сформулировать так: "Перенести таблицу T в табличное пространство users, и, кстати, если можно, БЕЗ ЖУРНАЛИЗАЦИИ". В частности, эта команда НЕ говорит: "Перенести таблицу и изменить режим журнализации".
Итак, мы видим, что сгенерировано порядка 4 Мбайт данных повторного выполнения - кажется, что действие, фактически, журнализируется. Это можно подтвердить, ИЗМЕНИВ режим журнализации:
и снова перенеся эту таблицу:
Теперь мы сгенерировали всего лишь 26 Кбайт данных повторного выполнения - этого достаточно для регистрации изменений в словаре данных, но не изменений перенесенных блоков. Мы перенесли объект без журнализации всех изменений.
Фактически мы выяснили, что нельзя одновременно переносить объект и изменять его любым другим способом (эти опции взаимоисключающие - вы либо переносите объект, либо изменяете его другим образом - одновременно это делать нельзя)
Можно ли восстановить действие, если используется nologging?
Если происходит сбой базы данных и придется восстанавливать ее после использования nologging для переноса, можно ли будет восстановить это действие. А после восстановления будет ли тавблица в исходном табличном пространстве?
Ответ Тома Кайта
Это зависит от причины сбоя и других обстоятельств.
После выполнения НЕ ЖУРНАЛИЗИРУЕМОГО действия в базе данных, работающей в режиме ARCHIVELOG (в котором и должны работать практически все производственные базы данных) рекомендуется выполнить горячее резервное копирование затронутых табличных пространств. Это позволит выполнить восстановление после сбоя носителя (media recovery) для этих табличных пространств.
Если этого не сделать И произойдет сбой носителя (а не просто сбой экземпляра) после выполнения не журнализируемого действия - данные будут потеряны. Их нельзя восстановить из архива, поскольку их в архиве нет. При выполнении действий без журнализации следует быть осторожным и согласовать действия с теми, кто отвечает за резервное копирование базы данных. В противном случае, легко потерять данные.
В базе данных, работающей в режиме noarchivelog , поскольку восстанавливаться можно только на момент последней полной резервной копии, выполненной в холодном режиме, при сбое носителя этот вопрос вообще не актуален (потому и надо работать в режиме archivelog !).
Что значит "таблица, огранизованная в виде кучи"?
Ответ Тома Кайта
Вот небольшая цитата из моей книги на эту тему, подробнее - читайте книгу ( Я привожу цитату по моему переводу на русский - В.К. ):
Таблицы, организованные в виде кучи
Таблицы, организованные в виде кучи, используются приложениями в 99 (если не более) процентах случаев, хотя со временем это может измениться за счет более интенсивного использования таблиц, организованных по индексу, - ведь по таким таблицам теперь тоже можно создавать дополнительные индексы. Таблица, организованная в виде кучи, создается по умолчанию при выполнении оператора CREATE TABLE . Если необходимо создать таблицу другого типа, это надо явно указать в операторе CREATE .
"Куча" - классическая структура данных, изучаемая в курсах программирования. Это по сути большая область пространства на диске или в памяти (в случае таблицы базы данных, конечно же, на диске), используемая произвольным образом. Данные размещаются там, где для них найдется место, а не в определенном порядке. Многие полагают, что данные будут получены из таблицы в том же порядке, в каком туда записывались, но при организации в виде кучи это не гарантировано. Фактически гарантировано как раз обратное: строки будут возвращаться в абсолютно непредсказуемом порядке. Это очень легко продемонстрировать. Создадим такую таблицу, чтобы в моей базе данных в блоке помещалась одна полная строка (я использую блоки размером 8 Кбайт). Совсем не обязательно создавать пример с одной строкой в блоке. Я просто хочу продемонстрировать предсказуемую последовательность событий. Такое поведение будет наблюдаться для таблиц любых размеров и в базах данных с любым размером блока:
.
Списки свободных мест
Я перенес таблицы в новое локально управляемое табличное пространство, а затем проанализировал таблицы. Мне интересно, почему столбец NUM_FREELIST_BLOCKS=0 в dba_tables ? Во всех таблицах есть неиспользуемые блоки, а в одном блоке - лишь несколько строк.
Ответ Тома Кайта
Потому, что блоки, в которых НИКОГДА не было данных, будут выше отметки максимального уровня, а не в списках свободных мест.
В списки свободных мест блоки попадают после использования - если они никогда не использовались, то в списке свободных мест их не будет.
Сразу после пересоздания, как в вашем случае, вполне естественно, что в списке свободных мест блоков МАЛО, если вообще они там есть. Это просто означает, что все существующие блоки данных "упакованы" - в них больше нельзя вставлять строки. После изменения/удаления данных некоторые блоки окажутся в списке свободных мест.
Рассмотрим следующий пример (табличное пространство system управляется по словарю, а табличное пространство users - локально управляемое):
Плотно упакованная таблица - никаких блоков в списке свободных мест пока нет.
А теперь - есть; мы добавили блоки в списки свободных мест, удалив некоторые строки.
Теперь их снова нет - все свободные блоки находятся выше отметки максимального уровня (HWM), а не в списках свободных мест.
а вот опять появились - таблица больше не "упакована", так как при удалении часть места освободилась
Перенос таблиц в 7.3.4 Parallel Server
Мы используем Oracle 7.3.4 Parallel Server в ОС NCR SVR4 (на неформатированных дисках). Я также использовал второй подход для переноса таблиц в другие табличные пространства, поскольку в версии 7.3.4 оператор alter table move tablespace . Я делал так:
Затем я снова создал индексы по таблице orgfoo . Мне хотелось бы знать:
a) Хорошее ли это решение для версии 7.3.4? Я нашел твое решение на сайте, где рекомендуется:
- Экспортировать схему пользователя
- Удалить все объекты пользователя
- Отобрать привилегию unlimited tablspace у пользователя
- Изменить стандартное табличное пространство для пользователя
- Импортировать данные пользователя
Но я хочу перенести только одну большую таблицу, а не все таблицы. После удаления всех объектов, как мне импортировать данные в два различных табличных пространства?
b) После переименования таблицы, надо ли пересоздать все представления до запуска приложения?
c) Для таблицы orgfoo (в табличном пространстве EHISTDAT ) выделено 250 Мбайт. Эту информацию я получил из dba_data_files и dba_free_space перед удалением таблицы orgfoo .
А при запросе после создания таблицы tempfoo в EKATSDAT и удаления таблицы orgfoo , я получил следующий результат:
Количество экстентов и блоков в представлении dba_extents тоже отличается. В табличном пространстве EHISTDAT освободилось 250 Мбайт, а в табличном пространстве было выделено не 250, а всего лишь 110 Мбайт. Не могли бы это объяснить? Считаете ли вы подобные действия полезными для экономии места на диске?
Ответ Тома Кайта
Ваш метод вполне приемлем. Можно сделать экспорт отдельной ТАБЛИЦЫ, а не всей схемы - это тоже подойдет, но и ваш метод отлично подходит (если только пересоздать все ограничения, триггеры, привилегии и т.п. - все это утилита EXP делает автоматически).
После переименования таблицы ничего делать не нужно. Представления сами о себе позаботятся, как и хранимые процедуры.
Что касается различия "размеров" - вновь созданная таблица заново "упакована". В результате, она вполне может оказаться "меньше". А вот насчет "полезности для экономии места" - я так не думаю. Через пару недель/месяцев таблица снова вырастет до прежнего размера. Это как когда садятся на диету - вес немного уменьшается, но в конечном итоге он снова увеличивается до "комфортного". Регулярная реорганизация таблиц:
a) мне не кажется нужной
b) мною не рекомендуется (при этом часто приходится слышать "блин, часть данных потеряна" из-за ошибок по ходу реогранизации)
c) место на диске "экономит" на пару дней, а со временем размер снова увеличивается до прежнего стабильного уровня.
Сбой экземпляра при переносе таблицы с опцией nologging
Что произойдет при сбое экземпляра по ходу переноса таблицы с опцией nologging? Мы данные не потеряем? Это не опасно?
Ответ Тома Кайта
Нет, nologging влияет только на восстановление после сбоя НОСИТЕЛЯ, но не после сбоя экземпляра.
При переносе таблицы с опцией nologging таблица копируется из постоянного сегмента во ВРЕМЕННЫЙ сегмент. В самом конце этого действия, временный сегмент преобразуется в постоянный - вото тогда копия и становится реальной таблицей.
Если сбой экземпляра произойдет по ходу переноса, процесс SMON просто очистит временный сегмент, и все будет выглядеть так, как если бы мы вообще таблицу не трогали (постоянный сегмент остается на месте).
Если сбой экземпляра произойдет после переноса - все в порядке, поскольку данные писались непосредственно на диск и восстанавливать их при восстановлении экземпляра не нужно.
Если после переноса часть данных изменена, изменение зафиксировано и произошел сбой - тоде все в порядке, поскольку данные повторного выполнения для этих изменений доступны и их можно восстановить.
Если после переноса и ДО резервного копирования файлов, которые были затронуты действием с опцией nologging произойдет СБОЙ ДИСКА - тогда да, "у нас проблемы". Вот почему в производственной среде есть основания опцию NOLOGGING не использовать, а если уж использовать, то:
- сначала создать резервную копию объектов;
- выполнить действие без журнализации;
- снова создать резервную копию объектов.
Итак, проблем при сбое экземляра вообще не возникает!
У меня есть вопрос вдогонку: поскольку вы сказали, что по ходу переноса постоянный сегмент не трогают, это означает, что запросы продолжают читать данные из постоянной таблицы, а не из временной, не так ли? А можно ли применять к таблице операторы ЯМД? Или таблица блокируется исключительной блокировкой?
Ответ Тома Кайта
Да, по ходу выполнения alter table move данные таблицы можно читать.
Выполнять операторы ЯМД можно только если действие выполняется "online" ( alter index rebuild online , например, alter table move online - но только для таблиц, организованных по индексу).
В Oracle9i есть пакет dbms_redefinition для пересоздания в режиме online большинства объектов (что позволяет выполнять операторы ЯМД по ходу переноса).
Комментарий читателя от 4 октября 2002 года
Я видел твой пример переноса таблицы. В нем вместо 4 Мбайт данных повторного выполнения (если при переносе была включена журнализация) генерировалось всего 26 Кбайт.
Я попытался сделать то же самое, но не увидел разницы. Не мог бы ты сказать, что я делаю не так. Вот мои результаты:
Как видишь, когда таблица журнализировалась, было сгенерировано 54320 байта данных повторного выполнения, в без журнализации - 53908 байт. Даже больше на 412 байт.
Я использую Oracle 8.1.7 на Windows 2000.
Ответ Тома Кайта
Вы работаете в режиме noarchivelog .
В этом режиме для этого действия не нужно генерировать данные повторного выполнения - вот они и не генерируются, независимо от установки logging/nologging.
Распараллеливание?
Итак, если необходимо "перенести" таблицу в другое табличное пространство (например, из управляемого по словарю в локально управляемое) быстрее будет использовать INSERT /*+ APPEND*/ , перевести таблицу в режим nologging , а не использовать move (с опцией nologging )?
А как насчет распараллеливания в Oracle 8.1.6 STANDARD? Можно ли использовать нечто вроде:
Команда работает, но я не знаю, как проверить, было ли распараллеливание при выполнении.
Посоветуйте, с точки зрения только производительности, что лучше - INSERT с APPEND nologging или move nologging .
Ответ Тома Кайта
Распараллеливание возможно только в EE и PE. См. в документации
Так что, в SE распараллеливание недоступно.
Но почему вам показалось, что insert /*+ append */ должно быть лучше?
Я бы просто перевел таблицу T в режим nologging и перенес ее:
Это проще, чем insert append , при этом не теряются привилегии и индексы.
(Пороверить, что действие распараллеливается, можно выполнив запрос к v$px_processes по ходу выполнения действия)
Комментарий читателя от 12 июня 2003 года
Я протестировал оба способа, но не в среде SQL*Plus, так что прощу прощения, что не могу просто вырезать и вставить "всю правду".
Я создал два табличных пространства. Я создал таблицу на базе dba_objects и удваивал ее пока в ней не оказалось ~ 1,8 миллиона строк. ~350 Мбайт при размере блока 16 Кбайт.
Затем я перевел таблицу в режим nologging (без распараллеливания).
Тестовая машина - двухпроцессорная, с Oracle 8.1.6 EE и обычными дисками (без RAID). При тестировании каждый тест выполнялся минимум дважды:
Если надо, я повторю эти же тесты в среде SQL*Plus и скопирую результаты.
Итак, почему insert /*+ append*/ выполняется быстрее, чем move ?
Данных повторного выполнения генерировалось от 200 до 350 Kбайт.
Ответ Тома Кайта
Я бы сказал, что разница между 137 и 125 секундами (общего времени выполнения) не существенна. 12 секунд ни о чем не говорят - особенно на компьютере, выполняющем еще какие-то действия.
Но, как показывапет ваш же тест, alter table move parallel 2 работает в 2 раза быстрее (первая попытка распараллеливания могла работать медленнее из-за того, что пришлось запускать дочерние процессы PQ - а поскольку запуск 4 процессов занял так много времени, возможно, были конфликты при доступе к исходному или целевому диску).
Я бы не делал на этом основании вывода, что insert append работает быстрее. Я бы сказал, что это намного сложнее, менее удобно, и вообще неправильно.
Комментарий читателя от 18 июня 2003 года
Интересно, раз таблица была перенесена, то ее индексы стали недействительными и их надо перестраивать, - а как индекс перестраивается? По таблице с новыми значениями rowid (я так думаю) или по существующему индексу (rowid в котором больше нельзя использовать).
Представим себе, что у нас была некая большая таблица с индексом, в которую мы писали и удаляли много данных. Индекс при записи данных рос, а при удалении он не уменьшался, но в нём появлялись "пустоты", которые не использовались при вставке новых данных.
Теперь сегмент, содержащий индекс, разросся и надо решить, что с ним делать.
Сначала узнаем, насколько он велик:
Прежде чем решить, что нам делать с этим индексом, разберёмся в различиях между rebuild и coalesce .
Rebuild
- Позволяет менять физические параметры хранения сегмента (например, менять табличное пространство).
- Требует много места, по сути создаёт новый сегмент, куда упорядоченно копирует данные, после чего переименовывает его, а старый удаляет.
- Требует много времени.
- Блокирует таблицу. Будучи запущенным с параметром online , всё рано блокирует таблицу ненадолго.
- Уменьшает размер сегмента, т.е. освбождённое место появляется в dba_free_space и может быть использовано для других объектов.
Coalesce
- Не позволяет менять параметры хранения сегмента.
- Дополнительного места не требует.
- Работает быстро.
- Не блокирует таблицу вообще.
- Не уменьшает размер сегмента, а просто реорганизует свободное место внутри него, перемещая "пустоты" к концу. Для других объектов высвобожденное место будет недоступно, но при добавлении записей в таблицу оно будет использоваться, т.е. сегмент некоторое время не будет расти.
Вот картинка, иллюстрирующая работу coalesce :
Блоки (leaf blocks) нашего индекса были заполнены частично. После операции coalesce первый блок заполнен на 100%, а второй - на 25% (в нашем примере PCTFREE =0).
Вывод: coalesce нам поможет, если индекс сильно фрагментирован за счёт многократных удалений, и к таблице часто выполняются запросы вида select * from table order by that_field . Запросы такого типа будут работать быстрее, и не потребуется перестройка индекса.
Если надо высвободить место или перенести индекс в другое табличное пространство, то нужно использовать rebuild .
Эта команда перемещает HWM в таблице до уровня занятых блоков. Например, в таблице HWM было на уровне 16 блоков, но потом в рез-те разных операций, занятых блоков осталось 12, но HWM остаётся на уровне 16, вот чтобы переместить HWM на уровень 12 блоков и увеличить кол-во unused blocks используется alter table move.
Но кол-во пространства, которое занимает таблица не меняется.
Перенести таблицу в другое табличное пространство
-- создаём табличное пространство
CREATE TABLESPACE GUPPI_TEST
DATAFILE '/data/GUPPI/guppi_test.dbf' SIZE 1000M AUTOEXTEND OFF;
-- проверяем в каком tablespace таблица до MOVE
select * from dba_segments where segment_name='T';
select * from dba_tables where table_name='T';
-- проверяем индексы
-- check index status
select index_name,status from dba_indexes where table_name
in ('T','CDR_UNBILLED','CDR_DATA_DUC','CDR_BILLED');
-- check index status for partition
select index_name,partition_name,status from dba_ind_partitions
where partition_name like 'T%' ;
-- перемещаем таблицу в другое tablespace
alter table guppi.t move tablespace guppi_test;
-- если много таблиц, то можно сгенерить команды
select 'alter table quest.'|| table_name || ' move;' from dba_tables where owner='QUEST';
-- проверяем в каком tablespace таблица после MOVE
select * from dba_segments where segment_name='T';
select * from dba_tables where table_name='T';
-- пересоздаём индексы таблицы, если они там были
-- в этом примере пересоздаём индексы и переносим их в другое табличное пространство
alter index guppi.object_id_idx rebuild tablespace guppi_test online;
-- если много таблиц, то можно сгенерить команды
select 'alter index quest.'|| index_name || ' rebuild online;' from dba_indexes where owner='QUEST';
-- проверяем индексы
select index_name,status from dba_indexes where table_name='T';
Привет хабровчане, в этой статье я расскажу о перемещаемых табличных пространствах(Transportable tablespaces) в Oracle 11g. Табличное пространство можно клонировать и затем включить в другую базу данных путем копирования, а также можно исключить из одной базы данных Oracle и включить в другую базу данных Oracle на той же платформе с помощью перемещения.
Перенос данных с помощью перемещения табличных пространств выполняется на порядок быстрее, чем при операциях экспорта/импорта или выгрузки/загрузки, поскольку перемещение табличных пространств состоит только из операций копирования файлов данных и интегрирования метаданных табличных пространств. Перемещение табличных пространств позволяет также перемещать соответствующие индексы, так что после импорта или загрузки табличных данных не требуется перестраивать индексы.
Работа с утилитами экспорта и импорта
Oracle Database позволяет копировать данные между базами данных, а также обмениваться ими с внешними файлами. Копирование осуществляется посредством экспорта и импорта.
Для осуществления данной задачи есть утилиты imp.exe и exp.exe
Переносимые табличные пространства
Оперативное предоставление разработчикам свежей копии данных производственной системы. Использование информации, публикуемой на неперезаписываемых носителях (CD-ROM, DVD и т.п.). Быстрое перемещение данных из оперативной системы в хранилище или в витрину данных.
Ускорение переноса информации достигается за счет замены ресурсоемких процессов экспорта-импорта или выгрузки-загрузки значительно более быстрым копированием файлов данных с одной вычислительной установки на другую.
Выбор самодостаточного набора табличных пространств
Существует ряд условий ограничивающих применение описываемой возможности.
Можно перемещать табличные пространства только между такими базами данных, которые:
1. Имеют одинаковый размер блока (db_block_size), созданы с одинаковой кодировкой (character set), в файлах инициализации (INIT.ORA) исходной и целевых баз данных параметр COMPATIBLE должен быть установлен в значение, работают на совместимых платформах одного и того же производителя оборудования.
Удовлетворение требований можно проверить, выполнив в исходной и целевой БД запрос:
Результаты запросов в исходной и целевой БД, должны быть одинаковые.
2. В целевой базе данных не должно быть табличного пространства с таким же именем, как у подключаемого.
3. Не поддерживается транспортировка: снапшотов и тиражируемых таблиц, функциональных индексов, локальных ссылок на объекты, доменных индексов.
4. Выбор самодостаточного набора табличных пространств
Самодостаточный набор табличных пространств – это совокупность табличных пространств, объекты которых не ссылаются на какие-либо объекты, не содержащиеся в данном наборе.
Для проверки самодостаточности удобно использовать процедуру TRANSPORT_SET_CHECK(для выполнения процедуры требуется роль EXECUTE_CATALOG_ROLE).
Результаты ее работы записываются во временную таблицу и их можно посмотреть через системное представление SYS.TRANSPORT_SET_VIOLATIONS:
Перенос набора табличных пространств
Сначала следует перевести табличные пространства в состояние READ ONLY(далее в тексте XXX — имя табличного пространства):
Затем с помощью утилиты EXP экспортируются метаданные словаря.
В командной строке следует изменить кодировку для корректного отображения информации.
oracle попросит ввести имя пользователя и пароль:
Имя пользователя: sys/system@orcl2012 as sysdba
orcl2012 – строка подключения к исходной БД.
TRANSPORT_TABLESPACE=Y — указывает, что выполняется экспорт метаданных транспортируемых табличных пространств, TABLESPACES=(USERS, USER_DATA, INDX) — задает список транспортируемых табличных пространств, TRIGGERS=Y – Экспортировать табличные триггеры (если указать N, то триггеры экспортироваться не будут) CONSTRAINTS=Y – Экспортировать ограничения целостности (при N не экспортируются ограничения типов PRIMARY KEY, UNIQUE, FOREIGN KEY и CHECK, однако ограничения NOT NULL экспортируются), GRANTS=Y – Экспортировать привилегии доступа к таблицам (N отменяет экспорт привилегий), FILE=exp_tts и log=exp_tts имя файла с данными и имя файла журнала экспорта.
После того как утилита успешно экспортирует метаданные на выходе будет файл с расширением .dmp.(exp_xxx.dmp)
После экспорта метаданных, можно перевести табличные пространства назад в состояние READ WRITE:
Подключение набора табличных пространств к целевой БД
Перед импортом, необходимо перенести данные от исходной базы данных к целевой:
Это можно сделать средствами ftp, командной строки или копированием средствами Windows.
Нужно копировать файл табличного пространства(.dbf) от исходной БД к целевой.
В командной строке.
\\server1\oradata\orcl\TS_XXX.dbf — путь к файлу перемещаемого табличного пространства на исходной базе данных
\\server2\oradata\orcl\ — путь, где будет хранится перемещаемое табличное пространство на целевой базе данных
Теперь можно подключать набор табличных пространств к целевой БД
Читайте также: