Как увеличить размер таблицы в oracle

Обновлено: 03.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 TABLESPACE:

Можно также увеличивать или уменьшать размер табличного пространства, увеличивая или уменьшая размер файлов данных табличного пространства опцией RESIZE.Обычно опция RESIZE применяется для исправления ошибок, допущенных при задании размера файла данных. Обратите внимание, что размер файла данных нельзя сделать меньше того, что уже занят объектами, хранящимися в нем.

Следующий пример показывает, как изменить размер файла данных вручную.Изначально файл имеет размер 250 Мбайт, а следующая команда удваивает его размер до 500 Мбайт. Заметьте, что для изменения размера файла данных необходимо использовать команду ALTER DATABASE, а не ALTER TABLESPACE.

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

Вот как выглядит синтаксис использования средства AUTOEXTEND:

В предыдущем примере экстенты в 10 Мбайт будут добавляться к табличному пространству, когда понадобится дополнительное место, как указано в параметре AUTOEXTEND. Параметр MAXSIZE ограничивает табличное пространство размером в 1000 Мбайт. При желании можно также специфицировать MAXSIZE UNLIMITED; в этом случае не устанавливается максимальный размер данного файла данных, а следовательно и всего табличного пространства. Однако необходимо убедиться в наличии достаточного пространства на диске операционной системы.

Oracle также предоставляет средство Resumable Space Allocation, временно приостанавливающее операции, которые могут в противном случае завершиться сбоем из-за нехватки места, а затем возобновляет их выполнение после того, как вы добавите место для объекта базы данных. Это делает использование средства AUTOEXTEND менее привлекательным.

Команда 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 таблицы, которые между собой не связаны:

1-1801-24e407.jpg

Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):

Добавляем первичный ключ

Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:

Добавляем ограничения с именами

Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):

2-1801-9d8180.jpg

Удаляем ограничения

Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:

3-1801-68d176.jpg

Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.

Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название "FK_Orders_To_Customers". Здесь для удаления внешнего подойдёт такое выражение:

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

Дано: таблица размером в несколько десятков гигабайт данных. Задача — изменить структуру таблицы.

Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте — «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

Предположим что структура нашей таблицы такая:


Мы хотим добавить в эту таблицу поле last_login.

Какие у нас есть варианты.

В лоб

Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.

Включаем мозг

Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:

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

И все таки — нужно добавить поле

Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами. Если честно я таким никогда не занимался, может это и проще следующего способа, но не всегда есть возможность поднять репликацию.

Мой способ заключается в следующем

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

Итак, подготавливаем 2 таблицы — первая с нужной структурой, вторая для логирования изменений.

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

Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного — скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:

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

Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо

нужно перелить через mysqldump:

Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

Кстати, велосипед уже изобретен Facebook и называется Online Schema Change for MySQL.

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