Как скопировать таблицу Oracle в другую базу Oracle

← →
ruslan_as (2010-03-25 22:33) [0]

Идея следующая (и думаю не новая). Есть мои таблицы на сервере Oracle предприятия. Имея доступ к ним я хочу копировать их (с данными) на виртуальную машину, где установлен свой Oracle (чистый без таблиц).
Зная имя таблицы (например Z_OMTS), нужно скопировать структуру таблицы с базы предприятия в мою базу. Как это реализовать?
Примеры искал, но находил не Oracle таблицы, так что сильно не судите.

← →
Правильный$Вася (2010-03-25 22:45) [1]

можно из одной БД подключиться к другой и перелить

← →
Игорь Шевченко © (2010-03-25 23:09) [2]

тынц:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apb.htm

← →
Кщд (2010-03-26 07:52) [3]

>Игорь Шевченко © (25.03.10 23:09) [2]
The COPY command will be obsoleted in future releases of SQL*Plus. No new datatypes will be supported by COPY.

и оттуда же:
In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases. You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases.

да и чем плох exp/imp?

← →
Sergey13 © (2010-03-26 08:47) [4]

Экспорт/импорт ИМХО самое простое.

← →
Игорь Шевченко © (2010-03-26 11:43) [5]

Кщд (26.03.10 07:52) [3]

> да и чем плох exp/imp?

Не в службу,а в дружбу - научи, как в базу с кодировкой AL32UTF8 и NLS_LENGTH_SEMANTICS = CHAR импортировать базу в кириллице с длинными значениями строковых полей ?

Импорт считает своим долгом выставить NLS_LENGTH_SEMANTICS = BYTE и длинные значения не помещаются, например "Фигня" в varchar2(5)

← →
Кщд (2010-03-26 11:55) [6]

>Игорь Шевченко © (26.03.10 11:43) [5]
>Импорт считает своим долгом выставить NLS_LENGTH_SEMANTICS = BYTE и длинные значения не помещаются, например "Фигня" в varchar2(5)
про самозамену импортом семантики слышу впервые, честно - можно подробности: конкретная кодировка в БД экспорта, NLS_LENGTH_SEMANTICS в БД экспорта?
когда принесли базу с семантикой byte и потребовали проимпортировать, изменили FAR"ом BYTE на CHAR прямо в файле экспорта, протестировали, залили
это, конечно, не рекомендация к использованию

PS У автора как-будто проблема с разными кодировками и семантиками не стояла?

← →
Игорь Шевченко © (2010-03-26 12:33) [7]

Кщд (26.03.10 11:55) [6]

> PS У автора как-будто проблема с разными кодировками и семантиками
> не стояла?

Это у меня проблема

> когда принесли базу с семантикой byte и потребовали проимпортировать,
> изменили FAR"ом BYTE на CHAR прямо в файле экспорта, протестировали,
> залили
> это, конечно, не рекомендация к использованию

Беда в том, что файл экспорта гигабайт так на 200, да и CHAR там не в одном месте находится.

> про самозамену импортом семантики слышу впервые, честно
> - можно подробности: конкретная кодировка в БД экспорта,
> NLS_LENGTH_SEMANTICS в БД экспорта?

Можно подробности:
Исходная база данных в CL8MSWIN1251, надо импортировать в AL32UT8, NLS_LENGTH_SEMANTICS в базе данных экспорта - BYTE, в базе данных импорта CHAR. Так сделано для того, чтобы импортируемые таблицы создавались во время иморта, с учетом семантики CHAR, таблиц много, данных тоже.

Я к чему задал вопрос - может, способ имеется какой.

← →
Кщд (2010-03-26 12:56) [8]

>Игорь Шевченко © (26.03.10 12:33) [7]
>Так сделано для того, чтобы импортируемые таблицы создавались во время иморта, с учетом семантики CHAR, таблиц много, данных тоже.
признаться, не понял: база с WIN1251 создана с NLS_LENGTH_SEMANTICS=BYTE специально? если "да", то причина от меня ускользает - можно на примере?

← →
Игорь Шевченко © (2010-03-26 13:02) [9]

Кщд (26.03.10 12:56) [8]

База с WIN1251 создана с NLS_LENGTH_SEMANTICS по умолчанию. А это был BYTE на момент создания. Хотелось бы максимально безболезненно перелить ее в юникодную базу.
Таблиц много, данных много, размер файла полного эскпорта я озвучил.

← →
Кщд (2010-03-26 13:33) [10]

>Игорь Шевченко © (26.03.10 13:02) [9]
Задача разовая или периодическая?

1. на базе с NLS_LENGTH_SEMANTICS=BYTE скриптом для всех таблиц с char/varchar2: alter table modify с увеличением размера поля в 4 раза, после чего экспорт и заливка в базу NLS_LENGTH_SEMANTICS=CHAR.

2. в базе с NLS_LENGTH_SEMANTICS=CHAR создать все таблицы(например, из экспорта метаданных базы с BYTE) и увеличить размеры всех полей char/varchar2 в раза, после чего сделать экспорт из базы BYTE и залить в базу CHAR.

Чем не подошли два этих варианта?

← →
Кщд (2010-03-26 13:34) [11]

>Кщд (26.03.10 13:33) [10]
>и увеличить размеры всех полей char/varchar2 в раза
в четыре раза))

← →
Игорь Шевченко © (2010-03-26 13:42) [12]

> 1. на базе с NLS_LENGTH_SEMANTICS=BYTE скриптом для всех
> таблиц с char/varchar2: alter table modify с увеличением
> размера поля в 4 раза, после чего экспорт и заливка в базу
> NLS_LENGTH_SEMANTICS=CHAR.

Невозможно по определению. База readonly

> 2. в базе с NLS_LENGTH_SEMANTICS=CHAR создать все таблицы(например,
> из экспорта метаданных базы с BYTE) и увеличить размеры
> всех полей char/varchar2 в раза, после чего сделать экспорт
> из базы BYTE и залить в базу CHAR.

Невозможно. Размер поля VARCHAR2(4000) куда увеличивать ?

← →
Кщд (2010-03-27 09:01) [13]

>Игорь Шевченко © (26.03.10 13:42) [12]
вопрос весьма интересен

http://forums.oracle.com/forums/thread.jspa?messageID=2371685
похоже на Ваш(и на наш) случай

что скажете?

← →
Игорь Шевченко © (2010-03-27 11:19) [14]

Кщд (27.03.10 09:01) [13]

Спасибо за ссылку.

Как минимум прочитал, что:
"You cannot just export data, and import it into a character semantics database, because export/import preserves original semantics of the exported tables."

Последняя ссылка в дискуссии, увы, не открывается.

В свое время (небольшую базу) пришлось переносить потаблично, PL/SQL Developer-ом, через INSERT-ы. Объем побольше, тоже потаблично, через INSERT INTO SELECT FROM database link.

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

В предыдущих вариантах слегка поменьше...

← →
Anatoly Podgoretsky © (2010-03-27 11:38) [15]

> Игорь Шевченко (27.03.2010 11:19:14) [14]

Разве Оракл не поддерживает такой формы как INSERT INTO db1... SELECT FROM db2...

--

← →
Игорь Шевченко © (2010-03-27 11:44) [16]

Anatoly Podgoretsky © (27.03.10 11:38) [15]

Целиком все объекты за один раз ? Поддерживает, называется экспорт/импорт :)

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

← →
Anatoly Podgoretsky © (2010-03-27 12:20) [17]

> Игорь Шевченко (27.03.2010 11:44:16) [16]

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

--

← →
Кщд (2010-03-27 15:26) [18]

>Игорь Шевченко © (27.03.10 11:19) [14]
вот это:

To migrate semantics of an original schema you need to create a script that will contain all CREATE statements needed to recreate this schema (at least CREATE {TYPE | TABLE | MATERIALIZED VIEW | PROCEDURE | FUNCTION | PACKAGE [BODY]}). Then, you can just add the ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR after any CONNECT command in this script. You can than run the script in the target database. How you create the script is irrelevant. You can use any reverse-engineering tool available (e.g. SHOW=Y option of import, DBMS_METADATA package, etc.)

After you pre-create the schema with the new semantics, you can import the data from the original (source) database with IGNORE=Y. The original semantics saved in the export file will be ignored for pre-created objects.

чем не решение?

в понедельник проверю на нашей тестовой, но пока всё выглядит более, чем съедобно)

← →
Игорь Шевченко © (2010-03-27 16:53) [19]

Кщд (27.03.10 15:26) [18]

Вот это место имеется в виду:

"The original semantics saved in the export file will be ignored for pre-created objects."

?

Надо попробовать.

Страшно не хочется создавать все объекты вручную, много их там :)

← →
Кщд (2010-03-27 17:02) [20]

Игорь Шевченко © (27.03.10 16:53) [19]
Вот это место имеется в виду:
"The original semantics saved in the export file will be ignored for pre-created objects."?

да, оно
отпишитесь о результатах?

← →
Игорь Шевченко © (2010-03-27 19:36) [21]

Кщд (27.03.10 17:02) [20]

Боюсь, что замучаюсь. Сейчас посмотрел файл от дампа небольшой базы, imp show=y нагенерировал 2 мегабайта SQL, пока я из него вытащу нужные объекты, с меня семь потов сойдет.

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

Юникодную базу на полсотни гиг оказалось создать гораздо быстрее.

← →
Игорь Шевченко © (2010-03-27 20:03) [22]

По одной таблице все прошло нормально

Написан скрипт

CREATE TABLE foobar ( id NUMBER(12, 0) NOT NULL, code VARCHAR2(2) NOT NULL, name_int VARCHAR2(32), name_nat VARCHAR2(32) ) / CREATE UNIQUE INDEX ... ALTER TABLE FOOBAR ADD CONSTRAINT foobar_pk PRIMARY KEY (id) .... и т.п.

в SQL*Plus первой командой выдана ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR

выполнен скрипт на создание таблицы

imp foo/bar@ora11u ignore=Y tables=(FOOBAR) fromuser=FROMUSER touser=TOUSER file=expdat.dmp log=foobar.log

import done in CL8MSWIN1251 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing FROMUSER"s objects into TOUSER . . importing table "FOOBAR" 210 rows imported Import terminated successfully without warnings.

ну и, соответственно, данные оттуда

select length(name_nat),lengthb(name_nat) from foobar where rownum < 10 LENGTH(NAME_NAT) LENGTHB(NAME_NAT) -------------------- --------------------- 6 12 8 16 6 12 7 14 8 16 7 14 6 12 3 6 7 14 10 20 24 45 LENGTH(NAME_NAT) LENGTHB(NAME_NAT) -------------------- --------------------- 9 18 15 28 7 14 7 14 14 26 7 13 10 18 4 8

Видно, что как минимум одно из значений длины в байтах (45) больше, чем объявленное при создании таблицы 32
а максимальное значение длины в байтах для этого поля - 48.

Для меня резюме такое - можно импортировать таким образом, но уж очень напряжно. Делать файл со скриптом для пары тысяч таблиц, немерянного количества пакетов, триггеров, типов - проще удавиться сразу :)
Или может, PL/SQL Developer умеет делать такое по всей схеме, с учетом зависимостей, не знаю.

← →
Кщд (2010-03-28 07:49) [23]

>Игорь Шевченко © (27.03.10 20:03) [22]
>По одной таблице все прошло нормально
спасибо
для нас - оптимальное решение, т.к. импортируемая схема содержит с десяток таблиц - без пакетов, процедур и т.п.

Может я не все проблемы понял, но PL/SQL Developer может:
1) скопировать/поправить структуры (Tools - Compare User Objects...)
2) сгенерить скрипт для создания структуры базы с выбором объектов, кот. необходимо туда включить (Tools - Export User Objects)

Далее пишется процедурка для переноса данных:
1) отключаются все констрейнты, триггера
2) пишется курсор для формирования строк str = "insert into <table> select * from <table@dblink>"
3) выполняется execute immediate str
4) включаются констрейнты, триггера
если нужны детали, могу и подробнее.

← →
Кщд (2010-03-30 17:42) [25]

>evvcom © (30.03.10 15:44) [24]
>Далее пишется процедурка для переноса данных:
2) PL/SQL Dev умеет выгружать таблицы в виде insert into
т.е. создавать dblink(если его, конечно, нет) вовсе не обязательно

кстати, если есть поля пользовательского типа (create type), то метод и вовсе не сработает

кроме прочего, к чему эти сложности, если есть файл экспорта, в который можно включить все объекты определенной схемы(а можно и потаблично, коли нужда такая есть)?

evvcom © (30.03.10 15:44) [24]

PL/SQL умеет делать объектов базы данных с учетом зависимостей. Я в [14] писал, что пользовался. Беда в том, что на больших объемах этот процесс слегка затруднителен.

> Далее пишется процедурка для переноса данных:

И это он тоже умеет, сам.

На мелких базах это срабатывает на ура, на больших - не очень на ура

> 2) PL/SQL Dev умеет выгружать таблицы в виде insert into
> т.е. создавать dblink(если его, конечно, нет) вовсе не обязательно

Да, умеет. Но многомегабайтный sql, состоящий из кучи insert values и работать будет не один час. Гораздо быстрее отработает insert select, а dblink сделать не так уж и затруднительно :)

> Игорь Шевченко © (30.03.10 18:01) [26]

Ну понятие больших баз весьма неопределенно, не очень ура, значит не очень. Ну а если стандартный exp/imp нельзя из-за описаной выше проблемы, то что еще остается?

evvcom © (30.03.10 19:23) [27]

> Да, умеет. Но многомегабайтный sql, состоящий из кучи insert
> values и работать будет не один час. Гораздо быстрее отработает
> insert select, а dblink сделать не так уж и затруднительно
> :)

ты пост [14] почитай :)

> Ну понятие больших баз весьма неопределенно, не очень ура,
> значит не очень

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

> Но многомегабайтный sql

Ты хотел сказать - многогигабайтный :)

объем базы, которую хотелось бы перенести таким образом - под терабайт.
Хотелось бы перенести при жизни :)

← →
Кщд (2010-03-31 08:10) [29]

>evvcom © (30.03.10 19:23) [27]

Да, умеет. Но многомегабайтный sql, состоящий из кучи insert values и работать будет не один час. Гораздо быстрее отработает insert select, а dblink сделать не так уж и затруднительно :)

ещё раз: у select from dblink - масса ограничений - пользовательские типы - лишь одно из них
во-вторых, создание dblink может быть недопустимо как по требованиям безопасности, так и чисто физически - из-за отсутствия доступа
в-третьих, что может select from dblink, чего не может exp/imp?

> ты пост [14] почитай :)

Почитал. Учитывая это, я написал [24]

> Далее пишется процедурка для переноса данных:
> 1) отключаются все констрейнты, триггера

после этого последовательность не важна

> из них пара тысяч - таблицы

- А 10 бананов это куча?
- 10? 10 - это куча...
Согласен, база большая :)
Поэтому и пишется курсор, на выборке которого строится цикл execute immediate "insert into ... select"

> Ты хотел сказать - многогигабайтный :)

Или так :)

> у select from dblink - масса ограничений - пользовательские
> типы - лишь одно из них

Ну я не претендую на единственность предложенного решения. Если можно сделать через imp/exp, то, конечно, лучше воспользоваться стандартными средствами, но я так понял из [14], что с этим засада:

> Как минимум прочитал, что:
> "You cannot just export data, and import it into a character
> semantics database, because export/import preserves original
> semantics of the exported tables."

← →
Кщд (2010-04-01 07:31) [31]

>evvcom © (31.03.10 13:37) [30]

Ну я не претендую на единственность предложенного решения. Если можно сделать через imp/exp, то, конечно, лучше воспользоваться стандартными средствами, но я так понял из [14], что с этим засада:

дальше прочитайте: для pre-created таблиц при импорте семантика полей из дампа будет проигнорирована

Как скопировать таблицу Oracle в другую базу Oracle Найти похожие ветки