Oracle посмотреть размер temp
Обновлено: 04.07.2024
Типы табличных пространств (постоянные, временные и отката)
Основными типами табличных пространств в базах данных 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 tempTEMPFILE /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 DATAFILEE:\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 создаст его как локально управляемое табличное пространство.
Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Перевод: zCarot
Временное табличное пространство, в противоположность тому, что можно предположить из его наименования, существует в базе данных постоянно, как и все прочие табличные пространства, такие как System и Sysaux. Однако данные во временном табличном пространстве имеют временный характер и существуют только на протяжении существования сеанса пользователя. Oracle использует временные табличные пространства в качестве рабочих областей для выполнения таких задач, как операции сортировки при выполнении запросов пользователей, и операции сортировки при создании индексов. Oracle не позволяет пользователям создавать объекты во временном табличном пространстве.
По определению временное табличное пространство хранит данные только на протяжении существования сеанса пользователя, и его данные не могут быть разделены между всеми пользователями. Производительность временного табличного пространства исключительно важна, когда приложение использует запросы, требующие интенсивной сортировки и хеширования, что подразумевает хранение промежуточных данных во временном табличном пространстве.
На заметку! СУБД Oracle Database пишет все данные программы в локальной области (PGA) порциями по 64 Кбайт, поэтому советуют создавать табличные пространства с размерами экстентов, кратными 64 Кбайт. Для крупных хранилищ данных и баз данных, поддерживающих системы принятия решений, которые интенсивно используют временные табличные пространства, рекомендуется размер экстента в 1 Мбайт.
Самый первый оператор после запуска экземпляра базы Oracle, который использует временное табличное пространство, создает сегмент сортировки, разделяемый всеми операциями сортировки в экземпляре. Когда вы останавливаете базу данных, она освобождает этот сегмент. Вы можете запросить представление V$SORT_SEGMENT, чтобы просмотреть выделение и освобождение места для этого сегмента сортировки. Увидеть, кто в данный момент использует сегмент сортировки, можно, опросив представление V$SORT_USAGE.Используйте представления V$TEMPFILE и DBA_TEMP_FILES, чтобы ознакомиться с подробностями о временных файлах, выделенных в данный момент временному табличному пространству.
Как упоминалось ранее, вы должны использовать конструкцию TEMPFILE при указании файлов, входящих в любое временное табличное пространство. С вашей точки зрения нет разницы между конструкцией DATAFILE, которая указывается для постоянного табличного пространства, и конструкцией TEMPFILE, специфицируемой для временного табличного пространства. Однако Oracle различает эти два типа файлов. Временные файлы содержат мало или вообще не содержат данных повторного выполнения, ассоциированных с ними.
Создание временного табличного пространства
Вы создаете временное табличное пространство точно так же, как и постоянное,лишь с тем отличием, что указываете конструкцию TEMPORARY в операторе CREATE TABLESPACE и подставляете эту конструкцию TEMPFILE вместо DATAFILE. Вот пример:
Конструкция SIZE во второй строке указывает размер файла данных и, как следствие, размер временного табличного пространства — 500 Мбайт. В приведенном операторе конструкция AUTOEXTEND ON приведет к автоматическому увеличению размера временного файла и вместе с ним — размера временного табличного пространства. По умолчанию все временные табличные пространства создаются с экстентами унифицированного размера — 1 Мбайт. Тем не менее, можно указать конструкцию UNIFORM SIZE,чтобы задать другой размер, как показано в следующем операторе:
В приведенном операторе конструкция EXTENT MANAGEMENT необязательна.Конструкция UNIFORM SIZE специфицирует специальный размер экстента в 16 Мбайт вместо 1 Мбайт по умолчанию.
Совет. При выделении места временному табличному пространству применяйте конструкцию TEMPFILE вместо DATAFILE.
Обычно принято создавать одно временное табличное пространство (именуемое, как правило, Temp) для каждой базы данных, но вполне можно иметь и несколько временных табличных пространств, входящих в группу временных табличных пространств,если база данных нуждается в выполнении интенсивных операций сортировки.Чтобы уничтожить данные во временном табличном пространстве по умолчанию,необходимо сначала воспользоваться командой ALTER TABLESPACE для создания нового табличного пространства по умолчанию в базе данных. Предыдущее временное табличное пространство по умолчанию затем можно уничтожить подобно любому другому.
На заметку! Oracle рекомендует устанавливать в качестве временного табличного пространства по умолчанию управляемое локально временное табличное пространство с унифицированным размером экстента в 1 Мбайт.
Изменение временного табличного пространства
С помощью команды ALTER TEMPORARY TABLESPACE можно выполнять различные задачи управления временным табличным пространством, включая добавление временного файла для его увеличения. Вот пример того, как можно увеличить временное табличное пространство:
Аналогичным образом можно использовать команду ALTER TABLESPACE для изменения размера временного файла:
Для уничтожения временного файла и удаления соответствующего файла операционной системы служит следующий оператор:
Когда вы уничтожаете временный файл, относящийся к временному табличному пространству, то само табличное пространство остается в использовании.
Временное табличное пространство можно сократить, как и любое нормальное табличное пространство. Следующий пример показывает применение команды ALTER TABLESPACE для сокращения временного табличного пространства:
Сокращение временных табличных пространств
Иногда может понадобиться увеличить временное табличное пространство, чтобы вместить данные очень крупного задания, которое интенсивно использует это временное табличное пространство. После завершения такого задания можно сократить это временное табличное пространство, используя конструкцию SHRINK SPACE в операторе ALTER TABLESPACE. Вот пример:
Конструкция SHRINK SPACE уменьшит временные файлы до минимального размера,который составляет около 1 Мбайт. С помощью конструкции KEEP можно задать минимальный размер для временных файлов, как показано ниже:
Oracle использует специальную логику при сжатии временных файлов во временном табличном пространстве. Предположим, что имеется временное табличное пространство, содержащее два временных файла по 1 Гбайт. Для сокращения табличного пространства до 1 Гбайт выдается следующая команда:
Если запросить представление V$TEMPFILE, можно будет увидеть следующее:
База данных сократит один из двух временных файлов вплоть до 1 Мбайт, а другой — только на 1 Мбайт, оставив в нем нетронутыми 999 Мбайт пространства. Если ваша цель — сократить определенный временный файл до заданного минимума, можете сделать это, указав имя временного файла, который нужно сократить:
Приведенный выше оператор ALTER TABLESPACE сокращает только указанный временный файл до размера, заданного в конструкции KEEP. Остальные временные файлы из табличного пространства TEMP остаются нетронутыми. Конструкция KEEP в приведенном выше операторе гарантирует, что временный файл, который был специфицирован, сохранит 500 Мбайт пространства. Следующий пример демонстрирует, как сократить отдельный временный файл, не указывая сохранившегося пространства:
Поскольку в предыдущем операторе конструкция KEEP указана не была, база данных сокращает указанный временный файл до минимально возможного размера, который составляет около 1 Мбайт.
Временное табличное пространство по умолчанию
Когда вы создаете пользователей базы данных, то должны назначить каждому временное табличное пространство по умолчанию, в котором они будут выполнять свои временные работы, подобные сортировке. Если не указать явно пользователю его временное табличное пространство, для этих целей применяется табличное пространство System, что может привести к высокой степени фрагментации этого табличного пространства, помимо его заполнения и торможения всей деятельности базы данных.Избежать таких нежелательных ситуаций можно, создав временное табличное пространство по умолчанию (default) для базы данных при ее создании с помощью конструкции DEFAULT TEMPORARY TABLESPACE. Oracle затем будет использовать это временное табличное пространство по умолчанию для всех пользователей, кому таковое не будет назначено явно. Создание временного табличного пространства по умолчанию будет продемонстрировано в моей новой статье, где пойдет речь о создании новой базы данных Oracle.
Обратите внимание, что если не создать табличное пространство по умолчанию при создании базы данных, сделать его можно и впоследствии. Нужно просто создать временное табличное пространство, как показано в предыдущем примере, и сделать его временным табличным пространством по умолчанию для всей базы данных, используя оператор вроде следующего:
Узнать имя текущего временного табличного пространства по умолчанию для базы данных можно, выполнив следующий запрос:
На заметку! Для временного табличного пространства использовать конструкцию AUTOALLOCATE нельзя. По умолчанию все временные табличные пространства создаются с локально управляемыми экстентами унифицированного размера. Размер экстента по умолчанию составляет 1 Мбайт, как и для всех других табличных пространств, но при желании можно задать другой размер экстента при создании временного табличного пространства.
Группы временных табличных пространств
Крупные транзакции иногда могут приводить к переполнению временного пространства. Задачи, связанные с объемными сортировками, особенно включающие таблицы с несколькими разделами, приводят к значительной нагрузке на временные табличные пространства, от чего может пострадать производительность. В Oracle Database 10g была введена концепция группы временных табличных пространств, которая позволяет использовать временные табличные пространства в разных сеансах.
Ниже перечислены некоторые из основных характеристик группы временных табличных пространств.
- Группа временных табличных пространств должна состоять минимум из одного табличного пространства. Ограничений на максимальное их количество не существует.
- Если вы удалите все члены группы временных табличных пространств, группа также будет автоматически удалена.
- Группа временных табличных пространств имеет то же самое пространство имен,что и временные табличные пространства, являющиеся частью группы.
- Имя временного табличного пространства не может совпадать с именем любой из групп табличных пространств.
- При назначении временного табличного пространства пользователю можно применять имя группы временных табличных пространств вместо имени конкретного табличного пространства. Имя группы можно также использовать, назначая временное табличное пространство по умолчанию для всей базы данных.
Преимущества групп временных табличных пространств
Использование группы временных табличных пространств вместо обычного одиночного временного табличного пространства обеспечивает следующие преимущества.
- Запросы SQL с гораздо меньшей вероятностью приведут к переполнению места,отведенного для сортировки, потому что теперь запрос может параллельно использовать несколько временных табличных пространств для сортировки.
- Можно специфицировать несколько временных табличных пространств по умолчанию на уровне базы данных.
- Параллельно работающие серверы в параллельных операциях будут эффективно использовать несколько временных табличных пространств.
- Один пользователь может параллельно использовать несколько временных табличных пространств в разных сеансах.
Создание группы временных табличных пространств
Когда вы назначаете первое временное табличное пространство в группу, то тем самым автоматически создаете группу. Чтобы создать группу табличных пространств, просто специфицируйте конструкцию TABLESPACE GROUP в операторе CREATE TABLESPACE,как показано ниже:
Приведенный оператор SQL создаст новое временное табличное пространство temp01 вместе с новой группой табличных пространств по имени tmpgrp1. Oracle создает новую группу табличных пространств, поскольку здесь при создании нового временного табличного пространства указана ключевая конструкция TABLESPACE GROUP.
Можно также создать группу временных табличных пространств, специфицируя ту же конструкцию TABLESPACE GROUP в команде ALTER TABLESPACE, как показано ниже:
Приведенный оператор заставит Oracle создать новую группу по имени tmpgrp1, поскольку ранее не существовало группы табличных пространств с таким именем.
Если вы специфицируете пару кавычек ('') вместо имени группы табличных пространств, вы тем самым неявно указываете Oracle не включать данное временное табличное пространство в группу. Вот пример:
Приведенный оператор создает временное табличное пространство по имени temp02,которое является обычным временным табличным пространством, не относящимся ни к одной группе временных табличных пространств.
Если полностью пустить конструкцию TABLESPACE GROUP, будет создано обычное временное табличное пространство, также не относящееся ни к одной из групп:
Добавление табличного пространства к группе временных табличных пространств
Как показано в предыдущем разделе, с помощью команды ALTER TABLESPACE можно добавить временное табличное пространство в группу. Можно также изменить группу, к которой относится данное табличное пространство, используя команду ALTER TABLESPACE. Например, можно указать, что табличное пространство temp02 принадлежит группе tmpgrp2, выполнив следующую команду:
При этом база данных создаст новую группу по имени tmpgrp2, если такой группы еще не существовало.
Установка группы в качестве табличного пространства по умолчанию для базы данных
Вы можете использовать группу временных табличных пространств в качестве временного табличного пространства базы данных по умолчанию. Если выполнить показанный ниже оператор, то все пользователи, не имеющие временного табличного пространства по умолчанию, смогут использовать любое табличное пространство из группы tmpgrp1 в качестве временного табличного пространства по умолчанию:
Приведенный оператор ALTER DATABASE назначает все табличные пространства из группы tmpgrp1 в качестве временных табличных пространств по умолчанию для всей базы данных.
Назначение групп временных табличных пространств при создании или изменении пользователей
Когда вы создаете новых пользователей, вы можете назначать им группы временных табличных пространств вместо одного временного табличного пространства. Вот пример:
Создав пользователя, можно также применить оператор ALTER USER, чтобы изменить группу табличных пространств, которую он будет использовать. Вот оператор SQL,который делает это:
Просмотр информации о группах табличных пространств
Для управления группами временных табличных пространств в базе данных можно использовать новое представление словаря данных DATA_TABLESPACE_GROUPS. Ниже показан пример простого запроса к этому представлению, который отображает имена всех групп табличных пространств:
Чтобы найти соответствие между табличными пространствами и группами, к которым они принадлежат, можно также воспользоваться представлением DBA_USERS. Вот пример:
Управление и обслуживание табличного пространства ORACLE
1: Концепция табличного пространства
В базе данных ORACLE все данные хранятся в табличном пространстве из логической структуры, конечно же, есть логические структуры, такие как сегменты, области, блоки под табличным пространством. Из физической структуры он помещается в файл данных. Табличное пространство может состоять из нескольких файлов данных.
Как показано на следующем рисунке, база данных состоит из одного или нескольких табличных пространств, которые логически состоят из одного или нескольких сегментов и физически состоят из одного или нескольких файлов os.
![clip_image002[4]](https://russianblogs.com/images/867/e843b35e8839efdd8a981e21c455539b.JPEG)
1.1 базовое табличное пространство
Несколько табличных пространств, созданных по умолчанию в системе:
Сколько табличных пространств необходимо в системе?
Ответ: SYSTEM, SYSAUX, TEMP, UNDO, как USERS, ПРИМЕР Ожидание табличного пространства необязательно.
1.2 Классификация табличных пространств
Постоянное табличное пространство Храните постоянные данные, такие как таблицы, индексы и т. Д.
Временное табличное пространство Невозможно хранить постоянные объекты, используемые для сохранения временных данных, сгенерированных при сортировке и группировке базы данных.
UNDO табличное пространство Сохраните зеркальное отображение перед изменением данных.
1.3 Управление табличным пространством
Метод управления табличным пространством:
Управление словарем: все пространство, выделенное для всей библиотеки, помещается в словарь данных. Легко вызвать конфликт по словарю и вызвать проблемы с производительностью.
2: создать табличное пространство
3: Управление табличным пространством
3.1 Информация табличного пространства
Как проверить, какие табличные пространства находятся в базе данных? Как просмотреть файл данных, соответствующий табличному пространству?
Просмотр табличного пространства:
Просмотр табличного пространства позволяет просматривать основную информацию через следующие системные представления
-Содержит описательную информацию для всех табличных пространств в базе данных
SELECT * FROM DBA_TABLESPACES
-Содержит информацию описания табличного пространства текущего пользователя
SELECT * FROM USER_TABLESPACES
-Содержит имя табличного пространства и информацию о номере, полученную из контрольного файла
SELECT * FROM V$TABLESPACE;
Просмотр файлов данных
-Содержит информацию описания файла данных и табличного пространства, к которому он принадлежит
SELECT * FROM DBA_DATA_FILES
-Содержит информацию описания временного файла данных и табличного пространства, к которому он принадлежит
SELECT * FROM DBA_TEMP_FILES
- Содержит основную информацию о файле данных, полученном из контрольного файла, включая имя и номер табличного пространства, к которому он принадлежит
SELECT * FROM V$DATAFILE
- Содержит основную информацию обо всех временных файлах данных
SELECT * FROM V$TEMPFILE
3.1.1. Просмотр табличного пространства TEMP по умолчанию
Уровень базы данных
уровень пользователя
3.1.2. Просмотр постоянного табличного пространства по умолчанию
Если вы создаете пользователя без указания его постоянного табличного пространства, будет использоваться табличное пространство по умолчанию.
3.1.3. Просмотр табличного типа по умолчанию
Если вы не укажете тип табличного пространства, по умолчанию будет использован тип табличного пространства, указанный параметром DEFAULT_TBS_TYPE.
3.1.4. Просмотр табличного пространства
SELECT * FROM DBA_TABLESPACES
3.1.5. Просмотр файла данных табличного пространства
Постоянное табличное пространство / UNDO табличное пространство
SELECT * FROM DBA_DATA_FILES;
Временное табличное пространство
SELECT * FROM V$TEMPFILE;
3.1.6. Просмотр использования табличного пространства
Рассчитать использование табличного пространства (с учетом автоматического роста файлов данных)
3.2 табличное пространство по умолчанию
В базе данных ORACLE 9i, когда создается пользователь базы данных, если не указано постоянное табличное пространство по умолчанию, система использует табличные пространства SYSTME в качестве постоянного табличного пространства пользователя по умолчанию, а временное табличное пространство по умолчанию - TEMP. В ORACLE 10 / 11g, если вы не укажете постоянное табличное пространство по умолчанию, это ПОЛЬЗОВАТЕЛИ. Временное табличное пространство по умолчанию - TEMP, конечно, при условии, что вы не изменили значение постоянного табличного пространства по умолчанию или не указали постоянное табличное пространство пользователя по умолчанию. ORACLE позволяет использовать настраиваемые табличные пространства в качестве постоянного табличного пространства по умолчанию, вы можете использовать следующий SQL для просмотра постоянного табличного пространства по умолчанию и временного табличного пространства по умолчанию в базе данных.
SQL>SELECT * FROM database_properties
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
SQL>SELECT * FROM database_properties
WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE'
Вы можете использовать инструкцию ALTER DATABASE DEFAULT TABLESPACE, чтобы установить постоянное табличное пространство по умолчанию для базы данных, чтобы при создании пользователя указанное табличное пространство использовалось по умолчанию.
Уровень базы данных:
Постоянное табличное пространство
SQL>ALTER DATABASE DEFAULT TABLESPACE USER;
Временное табличное пространство
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
SQL>ALTER USER USERNAM DEFAULT TABLESPACE NEW_TABLESPACE_NAME
Просмотр табличного пространства по умолчанию, соответствующего пользователю
SELECTUSERNAME, DEFAULT_TABLESPACEFROMDBA_USERS
1: Если мы указали табличное пространство по умолчанию в качестве значения DEFAULT_PERMANENT_TABLESPACE при создании пользователя, то после изменения табличного пространства по умолчанию табличное пространство пользователя также изменится.
2: Если мы не указали табличное пространство пользователя при создании пользователя, то табличное пространство по умолчанию БД также будет использоваться по умолчанию. В это время, если мы изменим табличное пространство по умолчанию БД, табличное пространство пользователя также изменится.
Переключите табличное пространство по умолчанию для базы данных на USERS
3: Если мы создаем пользовательское табличное пространство пользователя, являющееся другим табличным пространством, то мы изменяем табличное пространство БД по умолчанию, которое не повлияет на табличное пространство пользователя.
4: табличное пространство по умолчанию БД не может быть удалено, если табличное пространство по умолчанию не указано на другие табличные пространства, прежде чем его можно будет удалить.
SQL> DROP TABLESPACE USERS;
DROP TABLESPACE USERS
ORA-12919: невозможно удалить постоянное табличное пространство по умолчанию
5: если табличное пространство пользователя по умолчанию указывает на другие табличные пространства, при удалении этого табличного пространства табличное пространство пользователя по умолчанию автоматически указывает на табличное пространство по умолчанию в БД.
SQL> DROP TABLESPACE TEST2;
3.3 удалить табличное пространство
В дополнение к табличному пространству SYSTEM любое табличное пространство в базе данных может быть удалено. При удалении табличного пространства ORACLE просто удаляет информацию, связанную с табличным пространством и файлом данных, в управляющем файле и словаре данных. По умолчанию ORACLE не удаляет соответствующий файл данных в операционной системе, поэтому после успешного выполнения операции удаления табличного пространства необходимо вручную удалить соответствующий файл данных табличного пространства в операционной системе. Если вы удаляете соответствующий файл данных при удалении табличного пространства, вы должны отобразить указанное предложение INCLUDING CONTENTS AND DATAFILES. Примечание: Текущее табличное пространство по умолчанию на уровне базы данных не может быть удалено, а пользовательское табличное пространство может быть удалено. В противном случае будет сообщено об ошибке: ORA-12919: Невозможно удалить постоянное табличное пространство по умолчанию.
DROP TABLESPACE имя табличного пространства [ВКЛЮЧАЯ СОДЕРЖАНИЕ [И ДАННЫЕ ФАЙЛЫ] [КАСКАДНЫЕ СООТВЕТСТВИЯ]]
SQL> DROP TABLESPACE URER01 INCLUDING CONTENTS;
Если объект базы данных включен в табличное пространство, указанный оператор INCLUDING CONTENTS должен отображаться в операторе DROP TABLESPACE. Если вы хотите удалить табличное пространство USER при удалении соответствующего файла данных, вы можете использовать следующий оператор
SQL>DROP TABLESPACE USER01 INCLUDING CONTENTS AND DATAFILES;
Примечание. При удалении табличного пространства параметры CONTENTS и DATAFILES не выровнены, и будет показана следующая ошибка:
SQL>DROP TABLESPACE TBS_STAGE_DAT INCLUDING DATAFILES AND CONTENTS
ORA-01911：contents keyword expected
SQL>DROP TABLESPACE TBS_STAGE_DAT INCLUDING CONTENTS AND DATAFILES
3.4 Настройте табличное пространство
3.4.1 Добавить файл данных
Если вы обнаружите, что в табличном пространстве недостаточно места для хранения, вы можете добавить новый файл данных в табличное пространство, чтобы увеличить размер табличного пространства. Однако, как правило, рекомендуется заранее оценить объем памяти, необходимый для табличного пространства, а затем создать для него несколько файлов данных соответствующего размера.
При добавлении нового файла данных, если операционная система с таким именем уже существует, инструкция ALTER TABLESPACE завершится ошибкой. Если вы хотите перезаписать файл операционной системы с тем же именем, вы должны указать предложение REUSE, показанное ниже.
SQL> ALTER TABLESPACE TBS_EDS_DAT
2 ADD DATAFILE 'G:\datafile\TBS_EDS_DAT01.DBF'
4 AUTOEXTEND ON
6 MAXSIZE 51200M;
ALTER TABLESPACE TBS_EDS_DAT
Произошла ошибка в строке 1:
ORA-03206: максимальный размер файла (6553600) блока в предложении AUTOEXTEND вне диапазона
Размер файла данных, поддерживаемый ORACLE, определяется числом db_block_size и db_block. Число db_block (блок ORACLE) является фиксированным значением 2 ** 22-1 (4194303). Емкость файла данных = количество блоков * размер блока. В следующем списке показан максимальный размер физического файла, который могут поддерживать разные базы данных блоков данных:
Размер блока данных Максимальный физический файлMМаксимальный физический файл G
16KB 65535M 64G
32KB 131072M 128G
64KB 262144M 256G
3.4.2 Настройка размера файла данных
Сбросить размер файла данных
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'
3.4.3 Удалить файлы данных
ALTER TABLESPACE TEST
DROP DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'
3.4.4 Мобильные файлы данных
Теперь есть такой случай: в прошлом на сервере базы данных был только один диск с относительно небольшой емкостью, и все файлы данных были помещены на диск D. Позже я подал заявку на диск 1T и мне нужно было освободить место для диска D (диск D заполнен). Переместите несколько больших файлов данных на диск 1T:
Под Linux / Unix
3.4.5 Файлы автономных данных
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' OFFLINE;
3.4.6 Файл данных онлайн
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' ONLINE;
4: поддерживать табличное пространство
4.1 Изменение состояния табличного пространства
Состояние табличного пространства имеет следующие состояния: онлайн, оффлайн, только чтение, чтение и запись.
Чтобы просмотреть состояние табличного пространства, вы можете просмотреть его с помощью следующего оператора SQL.
4.1.1 Табличное пространство не в сети
SQL>ALTER TABLESPACE TBS_DM_DAT OFFLINE IMMEDIATE;
Чтобы установить автономный статус, вы можете использовать следующие 4 параметра для управления автономным режимом
НОРМАЛЬНЫЙ Этот параметр указывает, что табличное пространство переключается в автономное состояние обычным способом. Затем закройте все файлы данных табличного пространства. Если во время этого процесса не возникает ошибок, вы можете использовать параметр NORMAL, который также используется по умолчанию.
TEMPORARY Этот параметр временно переводит табличное пространство в автономный режим. В настоящее время ORACLE не будет проверять состояние каждого файла данных при выполнении контрольной точки. Даже если некоторые файлы данных недоступны, ORACLE будет игнорировать эти ошибки. Таким образом, когда табличное пространство установлено в оперативное состояние, может потребоваться восстановление данных.
IMMEDIATE Этот параметр немедленно переводит табличное пространство в автономный режим, при котором ORACLE не будет выполнять контрольную точку или проверять, доступен ли файл данных. Вместо этого файлы данных, принадлежащие табличному пространству, переводятся в автономный режим. База данных должна быть восстановлена при следующем включении табличного пространства в оперативный режим.
FOR RECOVER Этот параметр переводит табличное пространство в автономное состояние для восстановления. Если вы хотите выполнить восстановление табличного пространства на основе времени, вы можете использовать этот параметр, чтобы переключить табличное пространство в автономное состояние.
Если база данных работает в неархивном режиме (NOARCHIVELOG), поскольку данные для восстановления, необходимые для восстановления табличного пространства, не могут быть сохранены, табличное пространство нельзя немедленно переключить в автономное состояние. Если табличное пространство находится в автономном режиме, запросите таблицы в табличном пространстве и сообщите об ошибке: в данный момент ORA-00376 не может прочитать файл, а ORA-01110: файл данных x .
Примечание. Автономный режим (автономный режим) обычно используется для оперативного резервного копирования базы данных, восстановления данных и других операций обслуживания. Некоторые табличные пространства не могут быть в автономном режиме, такие как: SYTEM, UNDO и т. Д.
1. SYTEM не может быть в автономном режиме или только для чтения
2. Пустое пространство текущей таблицы UNDO не может быть отключено или доступно только для чтения.
3. Текущее временное табличное пространство не может быть отключено или доступно только для чтения.
4. SYSAUX может быть в автономном режиме, но не только для чтения
SQL> ALTER TABLESPACE SYSTEM OFFLINE;
ALTER TABLESPACE SYSTEM OFFLINE
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
SQL> ALTER TABLESPACE SYSTEM OFFLINE;
ALTER TABLESPACE SYSTEM OFFLINE
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
4.1.2 Табличное пространство онлайн
SQL> ALTER TABLESPACE TBS_DM_DAT ONLINE;
4.1.3 Табличное пространство только для чтения
SQL>ALTER TABLESPACE TBS_DM_DAT READY ONLY;
Когда табличное пространство доступно только для чтения, никакие операции DML не могут быть выполнены с таблицами в нем, в противном случае будет сообщено об ошибке: ORA-00372: файл xxx не может быть изменен в данный момент
ORA-01110: файл данных xx: ********. Но таблицу можно удалить.
4.1.4 Табличное пространство для чтения и записи
SQL>ALTER TABLESPACE TBS_DM_DAT READ WRITE;
4.1.5 Переименование табличного пространства
До ORACLE 10g имя табличного пространства нельзя изменить. В ORACLE 11G, используя предложение RENAME в операторе ALTER TABLESPACE, администратор базы данных может изменить имя табличного пространства.
4.1.6 Включить автоматическое расширение
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON;
4.1.7 Отключить автоматическое расширение
ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND OFF;
5 Квота табличного пространства
Недостаточное табличное пространство и недостаточная квота пользователя - это две разные концепции. Размер табличного пространства относится к размеру фактического пользовательского табличного пространства, а размер квоты относится к размеру табличного пространства, указанного пользователем. Два решения не совпадают
3.5.1 Просмотр квоты табличного пространства пользователя
MAX_BYTES = -1 означает, что квота не установлена,
3.5.2 Управление квотой табличного пространства пользователя
Создание и изменение квоты пользовательского табличного пространства:
1. При создании пользователя укажите лимит
CREATE USER TEST IDENTIFIED BY TEST
DEFAULT TABLESPACE TS_TEST
TEMPORARY TABLESPACE TEMP
QUOTA 3M ON TS_TEST
2. Измените ограничение табличного пространства пользователя:
A: Не контролируйте ограничение табличного пространства для пользователей:
Проверьте, нет ли ограничения табличного пространства
B: отменить квоту
Этот подход является глобальным.
SQL> GRANT UNLIMITED TABLESPACE TO SCOTT;
Или для конкретного табличного пространства.
SQL>ALTER USER SCOTT QUOTA UNIMITED ON TBS_EDS_DAT;
SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE='UNLIMITED TABLESPACE'
SQL> REVOKE UNLIMITED TABLESPACE FROM SCOTT;
C: установить квоты
3. Может распространяться естественным путем или переработано:
revoke unlimited tablespace from TEST;
alter user skate quota 0 on TB;
Чтобы решить проблему недостаточного размера табличного пространства: используйте команду «ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file», чтобы увеличить табличное пространство до указанных данных, в зависимости от конкретной ситуации можно увеличить одно или несколько табличных пространств.
Интеллектуальная рекомендация
совместный запрос mysql с тремя таблицами (таблица сотрудников, таблица отделов, таблица зарплат)
1. Краткое изложение проблемы: (внизу есть инструкция по созданию таблицы, копирование можно непосредственно практиковать с помощью (mysql)) Найдите отделы, в которых есть хотя бы один сотрудник. Отоб.
[Загрузчик классов обучения JVM] Третий день пользовательского контента, связанного с загрузчиком классов
IP, сеанс и cookie
Управление временным табличным пространством (temporary tablespace) в Oracle MySQL
Давайте рассмотрим что такое временное табличное пространство (temporary tablespace) в Oracle MySQL, как им управлять и какие проблемы нас могут ждать если мы вовремя не настроим некоторые параметры.
Исходные данные: Oracle MySQL 5.7.25 на Debian Linux
Все место на диске занял файл /var/lib/mysql/ibtmp1
В данном файле MySQL содержит временное табличное пространство (temporary tablespace). Данные во временном табличном пространстве имеют временный характер и существуют только на протяжении существования сеанса пользователя. Как правило MySQL использует временное табличное пространство для хранения временных данных во время выполнении операции сортировки при выполнении запросов пользователей или операции сортировки при создании индексов и т.п. При нормальном завершении работы MySQL файл временного ТП удаляется, а при запуске создается новый файл с новым идентификатором.
Все бы хорошо, но по умолчанию размер временного табличного пространства никак не ограничен и оно может запросто скушать все дисковое пространство, достаточно лишь пользователю запустить запрос, делающий огромные выборки данных со всевозможными сортировками, что и сделал один из разработчиков моего клиента, запустил большой запрос и видя, что он долго работает, пошел попить чай.
Итак, давайте вначале посмотрим лог mysql на предмет упоминания в нем создания файла временного ТП:
Вот те самые строки в который MySQL при запуске создает файл ibtmp1, все довольно просто, быстро и лаконично.
Теперь посмотрим настройки временного ТП:
Тут мы видим, что у нас создается 1 файл для временного ТП размером 12 МБ, с авторасширением и без ограничения размера.
Более детальную информация о временном ТП можно посмотреть таким запросом:
Результаты запросов SELECT @@datadir; и SELECT @@innodb_data_home_dir; нам тоже важны и нужны, но о них мы поговорим чуть ниже.
Как Вы уже поняли, для управления настройками временного ТП используется настройка innodb_temp_data_file_path (по ссылке Вы можете прочитать детальную информацию)
Давайте ограничим размер роста временного ТП на уровне 10 GB, для этого в файле конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секции [mysqld] пропишем:
Максимальный размер временного ТП указывается последним параметром с добавлением буквы K, M или G (KB, MB или GB (1024MB)).
Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:
Мы видим, что FILE_ID стал уже 38 и MAXIMUM_SIZE_MB стал теперь 10240 МБ.
Таким образом мы ограничили рост временного ТП до разумного предела и оно уже не заполнит нам весь диск.
Конечно помимо ограничения размера нужно еще постоянно вести мониторинг временного ТП и если оно заполниться, то необходимо отстреливать сессии которые потребляют все временное ТП.
В некоторых случаях, нужно использовать 2 или более файла для временного ТП. В MySQL это можно сделать, но тут есть свои особенности. К сожалению при указании 2-х и более файлов временного ТП использовать авторасширение и указать максимальный размер можно только для последнего файла, первый и последующие файлы (кроме последнего) должны быть созданы на максимальный размер. Это может быть удобно когда Вам не желательно нагружать диск лишними операциями в результате динамического расширения файла временного ТП или когда Вам нужен точный контроль размера временного ТП при его размещении на отдельном разделе фиксированного размера.
Давайте создадим 2 файла для временного ТП: 1 файл фиксированного размером 512 MB и разместим его на отдельном разделе смонтированном к каталогу /var/lib/mysql_tmp, а второй как обычно начальным размером 12 MB и с авторасширением до 10 GB, второй файл оставим в каталоге /var/lib/mysql:
1. Смонтируем новый диск и установим владельца на каталог /var/lib/mysql_tmp:
2. Внесем настройки в файл конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секцию [mysqld]:
Я не спроста смонтировал новый диск к каталогу /var/lib/mysql_tmp, т.к. в настройке innodb_temp_data_file_path путь до файла ibtmp1 нужно указывать относительно каталога datadir (по умолчанию /var/lib/mysql).
У нас все получилось, создалось 2 файла временного ТП, один из них /var/lib/mysql_tmp/ibtmp1 фиксированного размера 512 MB без возможности дальнейшего роста, а второй /var/lib/mysql/ibtmp2 размером 12 MB с авторасширением до 10 GB.
Хочу предупредить желающих сделать размер временного ТП сразу большого размера, т.к. временные ТП создаются при старте MySQL и удаляются при остановке, то создание большого файла может занимать продолжительное время, особенно если у вас не самые быстрые диски.
Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:
Мы видим, что AUTOEXTEND_SIZE_MB стал 128 МБ.
На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите, чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Читайте также: