Oracle увеличить размер поля varchar2
Обновлено: 06.07.2024
Оператор ALTER TABLE позволяет переминовывать существующую таблицу. Так же он может быть использован для добавления, модификации, или удаления столбцов существующей таблицы.
Переименование таблицы
Простейщий синтаксис переименования таблицы:
ALTER TABLE table_name
RENAME TO new_table_name;
ALTER TABLE suppliers
RENAME TO vendors;
Переименуем таблицу suppliers в таблицу vendors.
Добавление столбца (столбцов) в таблицу
Синтаксис оператора ALTER TABLE для добавления столбца в существующую таблицу:
ALTER TABLE table_name
ADD column_name column-definition;
ALTER TABLE supplier
ADD supplier_name varchar2(50);
Добавим столбец supplier_name в таблицу supplier.
Синтаксис оператора ALTER TABLE для добавления нескольких столбцов в существующую таблицу:
ALTER TABLE supplier ADD ( supplier_name varchar2(50), city varchar2(45) );
Добавим два столбца (supplier_name and city) в таблицу supplier.
Модификация столбца(-ов) в таблице
Синтаксис оператора ALTER TABLE для изменеия одного столбца в существующей таблице:
ALTER TABLE table_name
MODIFY column_name column_type;
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
Синтаксис оператора ALTER TABLE для изменения нескольких столбцов в существующей таблице:
ALTER TABLE supplier MODIFY ( supplier_name varchar2(100) not null, city varchar2(75) );
Изменим столбцыsupplier_name и city.
Удаление столбца(-ов) в таблице
Синтаксис оператора ALTER TABLE для удаления одного столбца в существующей таблице:
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE supplier
DROP COLUMN supplier_name;
Удалим столбец supplier_name в таблице supplier.
Переименование столбца(-ов) в таблице(НОВОЕ в Oracle 9i Release 2)
Начиная с Oracle 9i Release 2, вы можете переименовывать столбцы.
Синтаксис оператора ALTER TABLE для переименовывания одного столбца в существующей таблице::
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;
Переименуем столбец supplier_name в sname.
Переименовать таблицу departments, созданную ниже, в depts.
CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
ALTER TABLE departments
RENAME TO depts;
Добавить в таблицу employees, созданную ниже, столбец salary с типом данных number(6).
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
ADD salary number(6);
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
ALTER TABLE customers ADD ( contact_name varchar2(50), last_contacted date );
Поменять тип данных столбца employee_name на varchar2(75) в таблице employees.
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
MODIFY employee_name varchar2(75);
В таблице customers запретить запись NULL-значений в столбец customer_name и поменять тип данных поля state на varchar2(2).
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50), address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
ALTER TABLE customers MODIFY ( customer_name varchar2(50) not null, state varchar2(2) );
Удалить столбец salary в таблице employees.
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), salary number(6), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
DROP COLUMN salary;
В таблице departments переименовать столбец department_name в dept_name.
CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
ALTER TABLE departments
RENAME COLUMN department_name to dept_name;
Запись опубликована 10.12.2009 в 3:20 дп и размещена в рубрике Таблицы. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
у меня есть вопрос о ALTER TABLE команда на действительно большой таблице (почти 30 миллионов строк). Один из его столбцов - varchar(255) и я хотел бы изменить его размер на varchar(40) . В принципе, я хотел бы изменить свой столбец, выполнив следующую команду:
у меня нет проблем, если процесс очень длинный, но кажется, что моя таблица больше не читается во время команды ALTER TABLE. Есть ли способ поумнее? Может быть, Добавить новый столбец, скопировать данные из старого столбца старый столбец и, наконец, переименовать новый?
любой ключ будет очень признателен! Заранее спасибо,
Примечание: я использую PostgreSQL 9.0.
есть описание того, как это сделать в изменение размера столбца в таблице PostgreSQL без изменения данных. Вы должны взломать данные, каталога базы данных. Единственный способ сделать это официально - с помощью ALTER TABLE, и, как вы заметили, change заблокирует и перепишет всю таблицу во время ее работы.
убедитесь, что Вы читаете Типы Символов раздел документов перед изменением этого. Здесь много всяких странных случаев. Длина проверка выполняется, когда значения хранятся в строках. Если вы взломаете нижний предел, это не уменьшит размер существующих значений вообще. Было бы разумно выполнить сканирование по всей таблице, ища строки, где длина поля составляет >40 символов после внесения изменений. Вам нужно будет выяснить, как обрезать их вручную-так что вы вернетесь к некоторым замкам только на больших-потому что, если кто-то попытается обновить что-либо в этой строке, он отклонит его как слишком большой сейчас, в точка он идет, чтобы сохранить новую версию строки. Веселье следует за пользователем.
VARCHAR-ужасный тип, который существует в PostgreSQL только для того, чтобы соответствовать связанной с ним ужасной части стандарта SQL. Если вы не заботитесь о совместимости с несколькими базами данных, подумайте о сохранении данных в виде текста и добавьте ограничение для ограничения его длины. Ограничения, которые вы можете изменить без этой проблемы блокировки/перезаписи таблицы, и они могут сделать больше проверки целостности, чем просто слабые проверка длины.
в PostgreSQL 9.1 есть более простой способ
хорошо, я, вероятно, опаздываю на вечеринку, но.
НЕТ НЕОБХОДИМОСТИ ИЗМЕНЯТЬ РАЗМЕР СТОЛБЦА В ВАШЕМ СЛУЧАЕ!
Postgres, в отличие от некоторых других баз данных, достаточно умен, чтобы использовать только достаточно места, чтобы соответствовать строке (даже используя сжатие для более длинных строк), поэтому, даже если ваш столбец объявлен как VARCHAR(255) - если вы храните 40-символьные строки в столбце, использование пространства будет 40 байт + 1 байт накладных расходов.
требование к хранения для короткая строка (до 126 байт) 1 байт плюс фактическая строка, которая включает пробел в случае характера. Более длинные строки имеют 4 байта вместо 1. Длинные строки сжимаются системой автоматически, поэтому физическая потребность на диске может быть меньше. Очень длинные значения также хранится в фоновых таблицах, чтобы они не мешали rapid доступ к более короткой колонке ценности.
спецификация размера в VARCHAR используется только для проверки размера вставленных значений, это не влияет на макет диска. На самом деле,VARCHAR и текстовые поля хранятся таким же образом в Postgres.
я столкнулся с той же проблемой, пытаясь усечь VARCHAR от 32 до 8 и получить ERROR: value too long for type character varying(8) . Я хочу оставаться как можно ближе к SQL, потому что я использую самодельную JPA-подобную структуру, которую нам, возможно, придется переключать на разные СУБД в соответствии с выбором клиента (PostgreSQL по умолчанию). Следовательно, я не хочу использовать трюк изменения системных таблиц.
Я закончил использовать USING заявление в ALTER TABLE :
как отметил @raylu, ALTER приобретает исключительную блокировку таблицы, поэтому все остальные операции будут отложены до ее завершения.
здесь кэш страницы, описанной Грегом Смитом. В случае, если это также умирает, оператор alter выглядит следующим образом:
где ваша таблица-TABLE1, столбец-COL1, и вы хотите установить его в 35 символов (+4 необходим для устаревших целей в соответствии со ссылкой, возможно, накладные расходы, упомянутые A. H. В комментариях).
Если вы поместите alter В транзакцию, таблица не должна быть заблокирована:
это сработало для меня быстро, несколько секунд на столе с более чем 400k строк.
Я нашел очень простой способ изменить размер, т. е. аннотацию @Size (min = 1, max = 50), которая является частью "import javax.утверждение.ограничения " т. е. "импорт пакета javax.утверждение.ограничения.Размер;"
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
Тогда, если в таблице существуют данные, для них для column Address добавится значение "Неизвестно".
Удаляем столбец
Теперь можно удалить column Address:
Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название "FK_Orders_To_Customers". Здесь для удаления внешнего подойдёт такое выражение:
Переменные символьных типов предназначены для хранения текста, а для работы с ними используются символьные функции. Работа с символьными данными значительно различается по сложности от простой до весьма нетривиальной. В этой статье базовые средства PL/SQL по работе со строками рассматриваются в контексте однобайтовых наборов символов — например, тех, которые традиционно используются в Западной Европе и США. Если вы работаете в Юникоде или в другой многобайтовой кодировке или ваше приложение должно поддерживать несколько языков.
Хотя типы CLOB (Character Large OBject) и LONG теоретически тоже можно отнести к символьным типам, по принципам использования они отличаются от символьных типов, рассматриваемых в этой статье.
Строковые типы данных
Oracle поддерживает четыре строковых типа данных (табл. 1). Выбор типа зависит от двух факторов:
Типы данных фиксированной длины — CHAR и NCHAR — в приложениях Oracle используются очень редко. Их вообще не рекомендуется применять, если нет особых причин работать именно со строкой фиксированной длины. Далее, в разделе «Смешение значений CHAR и VARCHAR2 » рассказывается о проблемах, которые могут возникнуть при совместном использовании строковых переменных фиксированной и переменной длины.
Тип данных VARCHAR2
В переменных типа VARCHAR2 хранятся символьные строки переменной длины. При объявлении такой строки для нее определяется максимальная длина в диапазоне от 1 до 32 767 байт. Максимальная длина может задаваться в байтах или символах, но в любом случае компилятор определяет ее в байтах. Общий синтаксис объявления VARCHAR2 :
Здесь имя_переменной — имя объявляемой переменной, макс_длина — ее максимальная длина, а CHAR и BYTE — аргументы, указывающие, что максимальная длина выражается в символах или в байтах соответственно.
Если максимальная длина строковой переменной VARCHAR2 задается в символах (спецификатор CHAR ), то ее реальная длина в байтах вычисляется на основе максимального количества байтов, используемых для представления одного символа. Например, набор символов Юникода UTF-8 использует для представления некоторых символов до 4 байтов; следовательно, если вы работаете с UTF-8, объявление переменной типа VARCHAR2 , максимальная длина которой составляет 100 символов, эквивалентно объявлению этой же переменной с максимальной длиной 300 байт.
Спецификатор длины CHAR используется в основном при работе с многобайтовыми наборами символов — такими, как UTF-8.
Если в объявлении переменной VARCHAR2 опустить спецификатор CHAR или BYTE , тогда заданное значение длины будет интерпретировано в байтах или символах в зависимости от параметра инициализации NLS_LENGTH_SEMANTICS . Текущее значение можно узнать, обратившись с запросом к NLS_SESSION_PARAMETERS . Несколько примеров объявления строк типа VARCHAR2 :
Итак, максимальная длина переменной типа VARCHAR2 в PL/SQL составляет 32 767 байт. Это ограничение действует независимо от того, определяется ли длина строки в байтах или символах. До выхода версии 12c максимальная длина типа данных VARCHAR2 в SQL была равна 4000; в 12c она была увеличена до максимума PL/SQL: 32 767 байт. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED ; по умолчанию используется значение STANDARD .
Если вам понадобится работать со строками длиной более 4000 байт, рассмотрите возможность их хранения в столбцах типа CLOB .
Тип данных CHAR
Тип данных CHAR определяет строку фиксированной длины. При объявлении такой строки необходимо задать ее максимальную длину в диапазоне от 1 до 32 767 байт. Длина может задаваться как в байтах, так и в символах. Например, следующие два объявления создают строки длиной 100 байт и 100 символов соответственно:
Реальный размер 100-символьной строки в байтах зависит от текущего набора символов базы данных. Если используется набор символов с переменной длиной кодировки, PL/SQL выделяет для строки столько места, сколько необходимо для представления заданного количества символов с максимальным количеством байтов. Например, в наборе UTF-8, где символы имеют длину от 1 до 4 байт, PL/SQL при создании строки для хранения 100 символов зарезервирует 300 байт (3 байта ? 100 символов).
Мы уже знаем, что при отсутствии спецификатора CHAR или BYTE результат будет зависеть от параметра NLS_LENGTH_SEMANTICS . При компиляции программы эта настройка сохраняется вместе с ней и может использоваться повторно или заменяться при последующей перекомпиляции. С настройкой по умолчанию для следующего объявления будет создана строка длиной 100 байт:
Если длина строки не указана, PL/SQL объявит строку длиной 1 байт. Предположим, переменная объявляется так:
Как только этой переменной присваивается строка длиной более одного символа, PL/SQL инициирует универсальное исключение VALUE_ERROR . Но при этом не указывается, где именно возникла проблема. Если эта ошибка была получена при объявлении новых переменных или констант, проверьте свои объявления на небрежное использование CHAR . Чтобы избежать проблем и облегчить работу программистов, которые придут вам на смену, всегда указывайте длину строки типа CHAR . Несколько примеров:
Поскольку строка типа CHAR имеет фиксированную длину, PL/SQL при необходимости дополняет справа присвоенное значение пробелами, чтобы фактическая длина соответствовала максимальной, указанной в объявлении.
До выхода версии 12c максимальная длина типа данных CHAR в SQL была равна 2000; в 12c она была увеличена до максимума PL/SQL: 32 767 байт. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED .
Строковые подтипы
PL/SQL поддерживает некоторые строковые подтипы (табл. 2), которые тоже могут использоваться для объявления символьных строк. Многие из этих подтипов определены только для обеспечения совместимости со стандартом ANSI SQL. Вряд ли они вам когда-нибудь понадобятся, но знать о них все же нужно.
Каждый из перечисленных в таблице подтипов эквивалентен одному из базовых типов данных PL/SQL, указанных в правом столбце. Например:
Подтип VARCHAR заслуживает особого внимания. Уже на протяжении нескольких лет корпорация Oracle собирается изменить определение подтипа данных VARCHAR (в результате чего он перестанет быть эквивалентным VARCHAR2 ) и предупреждает, что пользоваться им не следует. Я согласен с этой рекомендацией: если существует опасность, что Oracle (или комитет ANSI) изменит поведение VARCHAR , неразумно полагаться на его поведение. Используйте вместо него VARCHAR2 .
Читайте также: