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

Обновлено: 02.07.2024

В этом руководстве по Oracle объясняется, как использовать оператор Oracle CREATE TABLE AS с синтаксисом и примерами.

Описание

Вы также можете использовать оператор Oracle CREATE TABLE AS для создания таблицы из существующей таблицы путем копирования столбцов существующей таблицы.

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

Создать таблицу — путем копирования всех столбцов из другой таблицы

Синтаксис

Синтаксис оператора CREATE TABLE AS, копирующего все столбцы в Oracle/PLSQL, следующий:

Пример

Давайте рассмотрим пример CREATE TABLE AS, в котором показано, как создать таблицу путем копирования всех столбцов из другой таблицы.

В этом примере создается новая таблица с именем suppliers, которая включает все столбцы из таблицы companies.

Если в таблице companies есть записи, новая таблица suppliers будет заполнена записями, возвращенными оператором SELECT.

Создать таблицу — путем копирования выбранных столбцов из другой таблицы

Синтаксис

Синтаксис оператора CREATE TABLE AS, копирующего выбранные столбцы в Oracle/PLSQL, следующий:

Пример

Давайте рассмотрим пример CREATE TABLE AS, в котором показано, как создать таблицу путем копирования выбранных столбцов из другой таблицы.

В этом примере будет создана новая таблица с именем suppliers, но новая таблица будет включать только указанные столбцы (например, company_id, адрес, город, штат и почтовый индекс) из компаний. таблица.

Опять же, если в таблице companies есть записи, новая таблица suppliers будет заполнена записями, возвращенными оператором SELECT.

Создать таблицу — путем копирования выбранных столбцов из нескольких таблиц

Синтаксис

Синтаксис оператора CREATE TABLE AS, копирующего столбцы из нескольких таблиц в Oracle/PLSQL, следующий:

Пример

Давайте рассмотрим пример CREATE TABLE AS, в котором показано, как создать таблицу путем копирования выбранных столбцов из нескольких таблиц.

В этом примере создается новая таблица с именем suppliers на основе определений столбцов из обеих таблиц companies и categories (например: company_id, адрес и категория_тип).

Часто задаваемые вопросы

Вопрос. Как создать таблицу Oracle из другой таблицы, не копируя значения из старой таблицы?

Ответ: Для этого используется синтаксис Oracle CREATE TABLE:

Это создаст новую таблицу с именем suppliers, которая будет включать все определения столбцов из таблицы companies, но не данные из таблицы companies.

Благодарности. Мы хотели бы поблагодарить Daniel W. за предоставление этого решения!

Прочитайте эту главу, сидя за компьютером, и попробуйте показанный пример. Прежде чем начать, убедитесь, что у вас есть доступ к примерам таблиц, описанным в SQL*Plus Quick Start.

Команда COPY будет устаревшей в будущих выпусках SQL*Plus. Никакие новые типы данных не будут поддерживаться COPY.

Синтаксис команды COPY

где база данных имеет следующий синтаксис:

имя пользователя [/ пароль ]@connect_identifier

Копирует данные из запроса в таблицу в той же или другой базе данных. COPY поддерживает следующие типы данных:

СИМВОЛ ДАТЫ ДЛИННЫЙ НОМЕР VARCHAR2

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

Условия

Описание каждого термина или пункта см. в следующем списке:

База данных, содержащая копируемые данные. Если вы опустите предложение FROM, источником по умолчанию будет база данных, к которой подключен SQL*Plus (то есть база данных, к которой обращаются другие команды). Вы должны использовать предложение FROM, чтобы указать исходную базу данных, отличную от базы данных по умолчанию. Предложение команды COPY FROM не поддерживает привилегированные соединения SYSDBA или SYSOPER.

База данных, содержащая целевую таблицу. Если вы опускаете предложение TO, по умолчанию в качестве места назначения используется база данных, к которой подключен SQL*Plus (то есть база данных, к которой обращаются другие команды). Вы должны использовать предложение TO, чтобы указать целевую базу данных, отличную от базы данных по умолчанию. Предложение TO команды COPY не поддерживает привилегированные соединения SYSDBA или SYSOPER.

Указывает имя пользователя [/ пароль ] @ connect_identifier исходной или целевой базы данных Oracle, которую вы хотите КОПИРОВАТЬ ИЗ или КОПИРОВАТЬ В. Команда COPY не поддерживает привилегированные соединения SYSDBA или SYSOPER. Вы должны указать имя пользователя. SQL*Plus запрашивает пароль, связанный с именем пользователя, указанным в предложениях COPY FROM или COPY TO. SQL*Plus подавляет отображение ответа на ваш пароль.

Вы должны включить предложение connect_identifier, чтобы указать исходную или целевую базу данных. Точный синтаксис зависит от конфигурации Oracle Net. Для получения дополнительной информации обратитесь к руководству по Oracle Net или обратитесь к своему администратору баз данных.

Вставляет строки из запроса в target_table, если таблица существует. Если целевая_таблица не существует, COPY создает ее.

Вставляет строки из запроса в целевую_таблицу после первого создания таблицы. Если target_table уже существует, COPY возвращает ошибку.

Вставляет строки из запроса в target_table . Если target_table не существует, COPY возвращает ошибку. При использовании INSERT запрос USING должен выбирать по одному столбцу для каждого столбца в таблице_назначения.

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

Представляет собой таблицу, которую вы хотите создать или в которую хотите добавить данные.

( столбец , столбец , столбец , . )

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

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

Указывает SQL-запрос (команда SELECT), определяющий, какие строки и столбцы копируются COPY.

Использование

Чтобы включить копирование данных между базами данных Oracle и другими базами данных, столбцы NUMBER заменяются столбцами DECIMAL в таблице назначения. Следовательно, если вы копируете между базами данных Oracle, столбец NUMBER без точности будет заменен столбцом DECIMAL(38). При копировании между базами данных Oracle вы должны использовать команды SQL (CREATE TABLE AS и INSERT) или убедиться, что ваши столбцы имеют указанную точность.

Переменная SQL*Plus SET LONG ограничивает длину копируемых столбцов LONG. Если какие-либо столбцы LONG содержат данные длиннее, чем значение LONG, COPY усекает данные.

SQL*Plus выполняет фиксацию в конце каждой успешной операции COPY. Если для переменной SQL*Plus SET COPYCOMMIT установлено положительное значение n, SQL*Plus выполняет фиксацию после копирования каждых n пакетов записей. Переменная SQL*Plus SET ARRAYSIZE определяет размер пакета.

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

Примеры

Следующая команда копирует всю таблицу EMPLOYEES в таблицу с именем WESTEMPLOYEES. Обратите внимание, что таблицы расположены в двух разных базах данных. Если WESTEMPLOYEES уже существует, SQL*Plus заменяет таблицу и ее содержимое. Столбцы в WESTEMPLOYEES имеют те же имена, что и столбцы в исходной таблице EMPLOYEES.

Следующая команда копирует выбранные записи из EMPLOYEES в базу данных, к которой подключен SQL*Plus. SQL*Plus создает ПРОДАВЦОВ через копию. SQL*Plus копирует только столбцы EMPLOYEE_ID и LAST_NAME, а в месте назначения называет их EMPLOYEE_ID и SA_MAN.

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

Используйте команду SQL*Plus COPY для копирования данных CHAR, DATE, LONG, NUMBER или VARCHAR2 между базами данных и между таблицами в одной базе данных. С помощью команды COPY вы можете копировать данные между базами данных следующими способами:

Скопируйте данные из удаленной базы данных в локальную базу данных.

Скопируйте данные из вашей локальной базы данных (по умолчанию) в удаленную базу данных (большинство систем).

Копировать данные из одной удаленной базы данных в другую удаленную базу данных (большинство систем).

В целом, команда COPY предназначена для копирования данных между базами данных Oracle и другими базами данных. Вы должны использовать команды SQL (CREATE TABLE AS и INSERT) для копирования данных между базами данных Oracle.

Понимание синтаксиса команды COPY

Вы вводите команду COPY в следующей форме:

Вот пример команды COPY:

Чтобы указать базу данных в предложении FROM или TO, необходимо иметь действительное имя пользователя и пароль для локальной и удаленной баз данных и знать соответствующие имена служб Oracle Net. COPY подчиняется требованиям безопасности Oracle Database, поэтому указанному вами имени пользователя должен быть предоставлен доступ к таблицам, чтобы у вас был доступ к таблицам. Чтобы узнать, какие базы данных вам доступны, обратитесь к своему администратору базы данных.

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

Команда COPY ведет себя по-разному в зависимости от того, существует ли уже целевая таблица, и от введенного вами условия действия (CREATE в примере). Дополнительную информацию см. в разделе Управление обработкой таблицы назначения.

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

Предложение USING задает запрос, который называет исходную таблицу и указывает данные, которые COPY копирует в целевую таблицу. Вы можете использовать любую форму команды SQL SELECT для выбора данных, копируемых командой COPY.

Вот пример команды COPY, которая копирует только два столбца из исходной таблицы и копирует только те строки, в которых значение DEPARTMENT_ID равно 30:

Возможно, вам будет проще вводить и редактировать длинные команды COPY в сценариях или в области ввода рабочей области i SQL*Plus, чем пытаться вводить их непосредственно в командной строке.

Управление обработкой таблицы назначения

Вы управляете обработкой целевой таблицы, вводя одно из четырех управляющих предложений — REPLACE, CREATE, INSERT или APPEND.

Предложение REPLACE называет таблицу, которая должна быть создана в целевой базе данных, и определяет следующие действия:

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

Если целевая таблица еще не существует, COPY создает ее, используя скопированные данные.

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

Если целевая таблица уже существует, COPY сообщает об ошибке и останавливается.

Если целевая таблица еще не существует, COPY создает таблицу, используя скопированные данные.

Используйте INSERT для вставки данных в существующую таблицу. INSERT определяет следующие действия:

Если целевая таблица уже существует, COPY вставляет скопированные данные в целевую таблицу.

Если целевая таблица еще не существует, COPY сообщает об ошибке и останавливается.

Используйте APPEND, если вы хотите вставить данные в существующую таблицу или создать новую таблицу, если целевая таблица не существует. APPEND определяет следующие действия:

Если целевая таблица уже существует, COPY вставляет скопированные данные в целевую таблицу.

Если таблица еще не существует, COPY создает таблицу, а затем вставляет в нее скопированные данные.

Пример B-1 Копирование из удаленной базы данных в локальную базу данных с помощью CREATE

Чтобы скопировать HR из удаленной базы данных в таблицу EMPLOYEE_COPY в вашей собственной базе данных, введите следующую команду:

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

Интерпретация сообщений, отображаемых COPY

Первые три сообщения, отображаемые COPY, показывают значения переменных команды SET, которые влияют на операцию COPY. Наиболее важным из них является LONG, который ограничивает длину значения столбца LONG. (LONG — это тип данных, аналогичный CHAR.) Если исходная таблица содержит столбец LONG, COPY усекает значения в этом столбце до длины, заданной системной переменной LONG.

Переменная ARRAYSIZE ограничивает количество строк, которые SQL*Plus одновременно извлекает из базы данных. Это количество строк составляет партию. Переменная COPYCOMMIT задает количество пакетов, после которых COPY фиксирует изменения в базе данных. (Если вы установите COPYCOMMIT равным нулю, COPY зафиксирует изменения только после того, как все пакеты будут скопированы.) Для получения дополнительной информации о переменных SET, в том числе о том, как изменить их настройки, см. команду SET.

После перечисления трех системных переменных и их значений функция COPY сообщает, была ли таблица удалена, создана или обновлена во время копирования. Затем COPY перечисляет количество выбранных, вставленных и зафиксированных строк.

Указание таблицы другого пользователя

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

Например, чтобы создать локальную копию таблицы с именем DEPARTMENT, принадлежащей пользователю ADAMS, в базе данных, связанной с идентификатором подключения Oracle Net BOSTONDB, введите

Конечно, вы могли бы получить тот же результат, указав COPY войти в удаленную базу данных как ADAMS. Однако вы не можете этого сделать, если не знаете пароль, связанный с именем пользователя ADAMS.

Копирование данных между таблицами в одной базе данных

Вы можете копировать данные из одной таблицы в другую в одной базе данных (локальной или удаленной). Чтобы выполнить копирование между таблицами в вашей локальной базе данных, укажите собственное имя пользователя и имя службы для вашей локальной базы данных в предложении FROM или TO (пропустите другое предложение):

Для копирования между таблицами в удаленной базе данных укажите одно и то же имя пользователя и имя службы в предложениях FROM и TO:

Должен признать, что мои "навыки работы с Google" подвели меня, и, возможно, я упускаю здесь что-то очевидное, но позвольте мне объяснить.
Если у меня есть таблица, которая заполняется или используется типичным приложением OLTP, то со временем данные в таблице могут стать «фрагментированными».
Я знаю, что это очень спорный термин, который я использовал, но вопрос здесь не в этом. Простой способ описать состояние таблицы может заключаться в том,
что таблица всегда заполнялась множеством мелких операций INSERT, изменялась множеством небольших операций UPDATE, а данные удалялись небольшими операциями DELETE.
Это означает, что данные не хранятся ни тесно в блоках данных, ни в каком-либо определенном порядке (поэтому в HWM есть несколько пустых блоков).
Теперь мой вопрос заключается в том, как мне создать точную копию этой таблицы и ее индексов, ТАК КАК ОНИ СУЩЕСТВУЮТ В НАСТОЯЩИЙ МОМЕНТ, которая включает
1) ее данные
2) ее ограничения/индексы и т. д.
/>3) его параметры хранения
4) данные хранятся ТОЧНО так же, как и в исходной таблице.

Во избежание сложностей рассматриваемая таблица представляет собой обычную таблицу кучи без какого-либо разделения. В то время как CREATE TABLE AS SELECT (или CREATE
TABLE с последующим INSERT) позаботится о пунктах (1)–(3) выше, он не достигнет пункта (4) выше.

Может кто-нибудь помочь?

Заранее спасибо.

Ответы

user503699 написал:
Здравствуйте,

Теперь мой вопрос заключается в том, как мне создать точную копию этой таблицы и ее индексов, ТАК КАК ОНИ СУЩЕСТВУЮТ В НАСТОЯЩИЙ МОМЕНТ, которая включает
1) ее данные
2) ее ограничения/индексы и т. д.
3) его параметры хранения
4) данные хранятся ТОЧНО так же, как и в исходной таблице.

Может кто-нибудь помочь?

Заранее спасибо.

Я считаю, что вам следует прибегнуть к DBMS_METADATA.GET_DDL, чтобы получить точные характеристики вашей базовой таблицы. При использовании CTAS, AFAIK, неявные ограничения не копируются, и также будут включены требования к параметрам хранения.
Затем вы можете скопировать данные, используя INSERT INTO TABLE SELECT FROM Base_Table. (выбрав подсказку APPEND, если требуется)

Я не понял, что вы имели в виду под пунктом 4. Не могли бы вы уточнить его?

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

Да, вы не ослышались, oracle предоставляет простой способ/команду для создания копии таблицы с данными или без данных. Это называется командой «Создать таблицу как».

«Создать таблицу как» — это команда DDL, которая позволяет создать точную копию таблицы вашей базы данных с данными или без них

Синтаксис:

Синтаксис довольно прост. CREATE TABLE — это зарезервированная фраза оракула, за которой следует определяемое пользователем имя таблицы 'table_name_1', с помощью которой вы хотите создать новую таблицу, затем у нас есть обязательное ключевое слово 'AS', за которым следует простой оператор SELECT DML.

Имя_таблицы_2 — это имя таблицы, копию которой вы хотите создать, а имя_таблицы_1 — это имя вашей новой таблицы.

Примеры:

Для демонстрации мы будем использовать примерную схему таблицы "Сотрудники отдела кадров" базы данных Oracle.

Например. 1: Как скопировать структуру и данные таблицы.

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

Скопировать точную структуру и данные таблицы сотрудников можно, написав очень простой оператор Create table. Это утверждение будет

Успешное выполнение приведенной выше команды создаст Employee_copy, который будет иметь ту же структуру и данные, что и таблица Employees схемы HR.

Например. 2. Как скопировать определенные столбцы таблицы вместе с их данными.

Теперь предположим, что вы хотите скопировать только столбцы first_name, last_name или адрес электронной почты с данными таблицы сотрудников, в этом случае будет оператор CREATE TABLE.

Успешное выполнение вышеуказанной команды приведет к созданию таблицы employee_copy, на этот раз содержащей только столбцы first_name, last_name, адрес электронной почты и данные.

Например. 3. Как скопировать только структуру таблицы без данных.

Иногда вам может потребоваться скопировать только структуру таблицы без данных. В этом случае вы можете скопировать всю таблицу, как показано в приведенных выше примерах, а затем обрезать ее позже, но для одной задачи потребуются две операции или две разные команды: такая пустая трата времени и ресурсов, когда вы можете выполнить то же самое, просто написав простую команду «Создать таблицу как».

Команда «Создать таблицу» останется такой же, как показано в приведенных выше примерах. Вам просто нужно добавить предложение where, которое никогда не будет установлено как истинное или всегда ложное, например, предложение where, такое как «Кошки» = «Собака» или 0 = 1. Посмотрим, как

Это несколько полезных советов и приемов, которые, надеюсь, вам понравилось читать. Если это так, пожалуйста, поддержите нас, поделившись этим блогом с друзьями в социальных сетях или любым удобным для вас способом. Если у вас есть какие-либо вопросы, не стесняйтесь подписаться на меня и написать мне в Твиттере @RebellionRider. Спасибо и хорошего дня!

Читайте также: