Oracle перенести таблицу в другую схему

Содержание
  1. Oracle: Как перенести схему из одной базы данных в другую с помощью exp / imp
  2. Как перенести схему из одной базы данных в другую
  3. Метаданные о схеме
  4. Экспорт схемы
  5. В целевой базе данных
  6. Импортировать схему в целевую базу данных
  7. Перенос таблицы в другое табличное пространство
  8. Перенос таблицы в другое табличное пространство
  9. Как выполняется alter table . move tablespace?
  10. Первый способ:
  11. Второй способ:
  12. Ответ Тома Кайта
  13. Перенос табличного пространства с опцией nologging
  14. Ответ Тома Кайта
  15. Можно ли восстановить действие, если используется nologging?
  16. Ответ Тома Кайта
  17. Что значит «таблица, огранизованная в виде кучи»?
  18. Ответ Тома Кайта
  19. Списки свободных мест
  20. Ответ Тома Кайта
  21. Перенос таблиц в 7.3.4 Parallel Server
  22. Ответ Тома Кайта
  23. Сбой экземпляра при переносе таблицы с опцией nologging
  24. Ответ Тома Кайта
  25. Итак, проблем при сбое экземляра вообще не возникает!
  26. Ответ Тома Кайта
  27. Комментарий читателя от 4 октября 2002 года
  28. Ответ Тома Кайта
  29. Распараллеливание?
  30. Ответ Тома Кайта
  31. Комментарий читателя от 12 июня 2003 года
  32. Ответ Тома Кайта
  33. Комментарий читателя от 18 июня 2003 года
  34. Ответ Тома Кайта

Oracle: Как перенести схему из одной базы данных в другую с помощью exp / imp

Здесь я дам подробные инструкции по миграции схемы с использованием exp / imp, pipe, gz между двумя базами данных.

Как перенести схему из одной базы данных в другую

Следующие шаги необходимо выполнить в исходной базе данных.

Метаданные о схеме

Экспорт схемы

Скопируйте SOURCE_schemas.dmp.gz на сервер целевой базы данных.

В целевой базе данных

Создать метаданные в целевой базе данных

1. Создайте табличные пространства в соответствии с результатами, полученными в исходной базе данных.

2. Создать пользователей в целевой базе данных

Запустите следующий сценарий в исходной базе данных и выполните полученный сценарий в целевой базе данных.

3. Предоставление квот в целевой базе данных

Запустите следующий сценарий в исходной базе данных и выполните результирующий сценарий в целевой базе данных.

4. Предоставление ролей в целевой базе данных

Запустите следующий сценарий в исходной базе данных и выполните полученный сценарий в целевой базе данных.

5. Предоставление системных привилегий в целевой базе данных

Запустите следующий сценарий в исходной базе данных и выполните полученный сценарий в целевой базе данных.

Импортировать схему в целевую базу данных

  1. Проверьте журналы импорта на наличие ошибок
  2. Запустите метаданные, которые были запущены в источнике, чтобы сравнить количество объектов
  3. Предоставление таблицы Priv на целевой объект

Запустите следующий сценарий в исходной базе данных и выполните результирующий сценарий в целевой базе данных

4. Cкомпилируйте импортированную схему

5. Соберите статистику по всем импортированным схемам

Источник

Перенос таблицы в другое табличное пространство

Перенос таблицы в другое табличное пространство

Эта статья посвящена сравнению эффективности различных способов переноса таблицы в другое табличное пространство. По мотивам ответа Тома Кайта на вопрос, первоначально заданный еще в апреле 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 делает автоматически).

После переименования таблицы ничего делать не нужно. Представления сами о себе позаботятся, как и хранимые процедуры.

Что касается различия «размеров» — вновь созданная таблица заново «упакована». В результате, она вполне может оказаться «меньше». А вот насчет «полезности для экономии места» — я так не думаю. Через пару недель/месяцев таблица снова вырастет до прежнего размера. Это как когда садятся на диету — вес немного уменьшается, но в конечном итоге он снова увеличивается до «комфортного». Регулярная реорганизация таблиц:

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 и удваивал ее пока в ней не оказалось

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 в котором больше нельзя использовать).

Ответ Тома Кайта

Необходимо обратиться к таблице, чтобы получить значения rowid .

Источник

Оцените статью
REMNABOR
Adblock
detector