Невозможно увеличить таблицу oracle

Обновлено: 15.01.2025

Типы табличных пространств (постоянные, временные и отката)

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

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

Хотя табличные пространства отката также могут содержать сегменты, которые будут сохранены и после окончания сеанса или транзакции, они обеспечивают согласованность по чтению (read consistency) для операторов выборки данных (select), которые обращаются к модифицируемым таблицам, а также данные отката для значительного количества ретроспективных (flashback) возможностей базы данных. Однако главным назначением сегментов отката является сохранение старых значений модифицируемых (обновляемых и удаляемых) столбцов, или указание на то, что не существует строки для вставки, так что, если сеанс пользователя аварийно закончится еще до того, как пользователь задаст команду commit или rollback, все обновления, вставки или удаления будут автоматически отменены. Прямой доступ к сегментам отката из сеанса пользователя всегда запрещен, и в табличных пространствах отката могут содержаться только управляемые автоматически сегменты отката (undo segments).

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

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

Табличные пространства создаваемые в процессе стандартной инсталляции Oracle с использованием универсального инсталлятора Oracle (OUI).

SYSTEM
SYSAUX
TEMP
UNDOTBS1
USERS
EXAMPLE

Управление табличными пространствами и файлами данных

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

SQL> select file_name, tablespace_name, bytes/1024/1024 as megs from dba_data_files;

Описание логической структуры базы данных

Три основных компонента мира логических дисковых ресурсов Oracle - табличные пространства, сегменты и экстенты. Табличное пространство (tablespace) логическая структура базы данных, предназначенная для хранения других логических структур базы данных. Oracle представляет табличное пространство как большую область пространства, в котором Oracle может размещать новые объекты. Место в табличных пространствах выделяется в сегментах.

Сегмент (segment) выделение пространства, используемого для хранения данных таблиц, индексов, сегментов отмены или временного объекта. Когда объект базы данных исчерпает пространство в своем сегменте, а ему потребуется добавить данные, Oracle позволяет выделить ему дополнительное пространство в форме экстента.

Экстент (extent) аналогичен сегменту в части хранения информации, относящейся к таблице, индексу, сегменту отмены или временному объекту.

Другая организация, представленная в базе данных Oracle, та, которая обеспечивается операционной системой хост-машины. В основе логической памяти в Oracle лежит физический метод, используемый хост-системой для хранения данных, краеугольным камнем которых является блок (block). Сегменты и экстенты состоят из блоков данных, а взятые вместе блоки, в свою очередь, содержат файл данных (datafile).

Значение параметра инициализации DB_BLICK_SIZE задается в байтах. Этот параметр определил стандартный размер каждого блока Oracle. Как правило, размер блока кратен размеру блока операционной системы. Блоки Oracle обычно раны 2, 4, 8 и иногда 16 Кбайт.

Табличное пространство может состоять из одного или многих файлов данных, а объекты табличного пространства Oracle может хранить где-нибудь в многочисленных файлах данных, составляющих табличное пространство. Хотя у табличного пространства может быть много файлов данных, каждый из них должен принадлежать только одному табличному пространству.

Как Oracle осуществляет управление местом в табличном пространстве

Управление свободным местом важная задача, так как без этого Oracle не знала бы, где размещать таблицы или индексы, когда вам потребуется создать и изменить их.
Начиная с версии Oracle8i, существует тип табличного пространства, называемый локально управляемым табличным пространством (locally managed tablespace). Локально управляемые табличные пространства используются для битовых массивов, которые хранятся в заголовках составляющих табличное пространство файлов данных, в целях отслеживания использования свободного пространства. Этот битовый массив представляет каждый блок в файле данных, а каждый бит в карте показывает, является этот блок свободным или нет.
Внутри табличных пространств Oracle управляет свободным пространством, объединяя его в непрерывные сегменты. Этим объединением в Oracle автоматически управляет системный монитор, или фоновый процесс SMON. При создании новых объектов базы данных, Oracle получает необходимый объем непрерывного пространства в памяти в виде сегмента для нового объекта. Объем используемого SMON пространства зависит от собственной конструкции объекта storage, от конструкции default storage для этого табличного пространства или от назначения однородного экстента, сконфигурированного для данного табличного пространства. SMON процесс, управляющий текущим объединением свободного пространства в непрерывные участки памяти во время работы таблицы словаря в табличное пространство SYSTEM, чтобы Oracle знала, где в управляемом словарем пространстве есть свободное место, или это поддерживает битовый массив в файлах данных табличного пространства, если используются локально управляемые табличные пространства.

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

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

Постоянные табличные пространства и временные табличные пространства.

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

Временные табличные пространства по умолчанию

DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE /u04/oradata/oracle/temp01.dbf SIZE 100M;

Создание табличного пространства

Исторически в Oracle табличное пространство SYSTEM было единственным табличным пространством, которое можно было создать во время создания базы данных. Это делалось в явном виде; определялось местоположение файла данных табличного пространства SYSTEM в команде create database. Все это можно делать и до сих пор, но начиная с Oracle9i одновременно можно создавать два других типа табличных пространств. Мы уже рассматривали пример, где в команде create database создавалось временное табличное пространство по умолчанию. Другой пример табличное пространство сегмента отмены. Тем, кто переходит на Oracle, имея опыт работы администратором в предыдущих версиях Oracle, следует учесть, что отмена (undo) то же самое, что откат (rollback). В Oracle 9i, при создании базы данных можно создать следующие табличные пространства: SYSTEM, временное и UNDOTDS.

Какие табличные пространства могут понадобиться?

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

SYSTEM - Каждая база данных должна иметь табличное пространство SYSTEM. Оно создается во время создания базы данных.

DATA - табличное пространство DATA используется для размещения данных таблиц.

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

UNDOTBS - табличное пространство UNDOTBS содержит сегменты отмены (в предыдущих версиях Oracle они назывались сегментами отката). Их нужно хранить отдельно от других типов объектов иза непостоянства назначения экстентов.

TEMP табличное пространство TEMP содержит временные сегменты. Их также следует хранить отдельно от других типов объектов из-за непостоянства назначения экстентов.

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

Создание табличного пространства для хранения постоянных сегментов

Все дополнительные табличные пространства должны создаваться с помощью команды create tablespace после создания базы данных.Рассмотрим пример создания локально управляемого табличного пространства для хранения табличных данных в базе данных, работающей под Windows. Откуда может быть известно, что в этом табличном пространстве будут содержаться данные таблицы? Из-за спецификации обратите внимание на имя табличного пространства DATA. Как правило, это говорит о том, что табличное пространство будет использоваться для хранения данных таблицы. А теперь рассмотрим саму команду:

SQL>CREATE TABLESPACE DATA DATAFILE
E:\oradata\oracle\data01.dat SIZE 20M,
F:\oradata\oracle\data01.dat SIZE 30M,
AUTOOEXTEND ON NEXT 10M MAXSIZE 50M
MINIMUM EXTENT 150K
EXTENT MANAGEMENT LOCAL
PERMANENT ONLINE;

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

Как видим, в команде create tablespace существует несколько компонентов. Сначала с помощью абсолютных имен путей определяются файлы данных табличного пространства. Учтите, что в одном из файлов данных для этого определена фраза autoextend. Это свойство позволяет файлу данных автоматически выходить за пределы первоначально определенного размера, чтобы соответствовать росту данных.

На следующем шаге необходимо определить фразу default storage, чтобы задать применяемые к созданию объекта базы данных опции, если для созданного объекта не определены параметры памяти. Если для помещенного в это табличное пространство объекта задана своя собственная фраза storage, то настройки фразы storage объекта отменят заданные по умолчанию настройки фразы storage табличного пространства за одним исключением. Существует одна опция памяти, которую нельзя отменить, если она определена в табличном пространстве. Это опция minimum extent - обеспечивает кратность каждого используемого в табличном пространстве размера экстента целочисленному значению. Вскоре мы рассмотрим параметры default storage более подробно.

Постоянные табличные пространства, аналогичные созданным в предыдущем блоке кода, могут содержать постоянные и временные сегменты. Эта функциональная возможность обеспечивается Oracle для обратной совместимости. Однако вам следует помещать временные сегменты во временные табличные пространства.
Обратите также внимание на то, что фраза extent management local позволяет определить это табличное пространство как локально управляемое. В версиях Oracle9i и выше, эта настройка управления пространством для табличного пространства задается по умолчанию, поэтому нет необходимости указывать эту фразу, чтобы задать локальное управлении экстентом. Если бы потребовалось определить табличные пространства, управляемые словарем, вместо этого мы воспользовались бы фразой extent management dictionary. Можно указать, что табличное пространство содержит постоянные объекты базы с помощью ключевого слова permanent; однако Oracle предполагает, что табличное пространство является постоянным, даже если ключевое слово permanent опущено. И наконец, ключевое слово online дает Oracle указание перевести созданное табличное пространство в оперативных режим. Оперативное (online) это задаваемое по умолчанию состояние созданного табличного пространства. Если в команде create tablespace ключевое слово online опущено, табличное пространство все равно будет оперативным. Впоследствии его также можно сделать оперативным, введя команду alter tablespace name online.

Если в команде create tablespace нет никаких указаний на локальное управление или управление словарем. Oracle9i создаст его как локально управляемое табличное пространство.

Когда ваше табличное пространство заполняется данными таблиц или индексов, необходимо увеличить его размер. Это делается добавлением файлов в команде 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 < mytable > enable ROW movement; --включить перемещение строк
ALTER TABLE < mytable > shrink SPACE ; --уплотнение данных таблицы ( shrink_clause)
ALTER TABLE < mytable > disable ROW movement; --отключить перемещение строк

ANALYZE TABLE < mytable > compute STATISTICS ;
COMMIT ;

shrink_clause

Для уплотнения сегмента необходимо перемещение строк, следовательно необходимо:

Разрешить перемещение строк для таблицы которую необходимо сжать.
Отключить триггеры которые используют ROWID.

Команда не может быть выполнена кластерных таблиц и любого объекта со столбцом типа LONG .

Не поддерживается для сегментов таблиц с индексами типа:

function-based indexes
domain indexes
bitmap join indexes

The shrink_clause is subject to the following restrictions:

You cannot combine this clause with any other clauses in the same ALTER TABLE statement.
You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
Segment shrink is not supported for tables with function-based indexes , domain indexes , or bitmap join indexes .
This clause does not shrink mapping tables of index-organized tables , even if you specify CASCADE .
You cannot specify this clause for a compressed table .
You cannot shrink a table that is the master table of an ON COMMIT materialized view . Rowid materialized views must be rebuilt after the shrink operation.

Путь 2: Расширения табличного пространства

Запрос размеров табличных пространств

SELECT
df.tablespace_name "Tablespace" ,
totalusedspace "Used MB" ,
( df.totalspace - tu.totalusedspace ) "Free MB" ,
df.totalspace "Total MB" ,
ROUND ( 100 * ( ( df.totalspace - tu.totalusedspace ) / df.totalspace ) )
"Pct. Free"
FROM
( SELECT tablespace_name ,
ROUND ( SUM ( bytes ) / 1048576 ) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name ) df ,

( SELECT ROUND ( SUM ( bytes ) / ( 1024 * 1024 ) ) totalusedspace , tablespace_name
FROM dba_segments
GROUP BY tablespace_name ) tu
WHERE df.tablespace_name = tu.tablespace_name ;

Tablespace	Used MB	Free MB	Total MB	Pct. Free
INDX	117	383	500	77
INDX1	575	175	750	23
INDX2	19100	150	19250	1
SYSAUX	427	373	800	47
SYSTEM	488	312	800	39
TBL_CLIENTBASKET	20478	2	20480	0
TBL_CONTAINER	25859	7	25866	0
TBL_CONTAINERADDRESS	4672	376	5048	7
TBL_MONEYDIST	1092	3408	4500	76
TBL_TAXBASKET	648	1352	2000	68
UNDOTBS1	175	5945	6120	97
USERS	3050	128	3178	4
USERS1	615	135	750	18
USERS2	17350	150	17500	1
USERS3	3205	11795	15000	79

Получить имена и пути файлов данных

Расширить файл данных

Добавим 1024 мегабайта к файлу данных, т.е. приведем размер файла к 21G

ALTER DATABASE DATAFILE ' D: \O RACLE \O RADATA \V ICTORY \C LIENT_BASKET.DBF ' RESIZE 21504M;

Пример ошибки

ORA-01654: unable to extend index REVELATION.SYS_C004650 by 8192 in tablespace TBL_CLIENTBASKET

Привожу простой пример заполнения и последующего сжатия tablespace .

И так в чем нюансы при сжатии табличного пространства.

В табличном пространстве всего одна таблица. В таблицу можно загрузить всего 100 строк. Загружаем построчно в таблицу с 1 по 100 строки. Табличное пространство заполнено под завязку.

Теперь удалим с 1 по 50 строку. Осталось половина - 50 строк. Смотрим на размер файла, а он остался те же 100 мб.

Т.е. при заполнении таблицы (и соответственно табличного пространства) файл увеличивается, но не уменьшается при их удалении.

Почему так происходит? Дело в том, что табличное пространство уменьшается (режется) с конца. А в конце у нас лежат данные.

CREATE TABLESPACE MGMT DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MGMT.DBF'

SIZE 1 M REUSE AUTOEXTEND ON NEXT 10 M MAXSIZE 100 M

Делаем дефрагментацию данных. Для этого нужно разрешить перемещение строк в таблице.

Опция COMPACT проводит дефрагментацию, но не корректирует HWM, и не освобождает высвободившееся пространство. Опция CASCADE сжимает не только названную таблицу, но и любые зависимые объекты, например, индексы.

ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MGMT.DBF' RESIZE 50 M

Получить ссылку
Facebook
Twitter
Pinterest
Электронная почта
Другие приложения

Это здорово работает, когда в табличном пространстве всего одна таблица. Добавьте 2-ю таблицу и данные в нее после добавления данных в 1-ю, а потом покажите, как шринкануть пространство.

T-SQL. Почему пишут WHERE 1=1?

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