Oracle изменить размер datafile
.
I’ve described in previous posts how the 12c online datafile move works: your session process do the copy and tells other writers (dbwr and direct-path inserts) to write blocks to both files (old one and new one for blocks that have already been copied). Readers read old file until the copy is completed.
The target file is created at the start of the move, with the same size, and then is filled as long as the copy phase runs. What happens if the size of the source file increases?
I run a datafile move from one session. The datafile size is 14.6 GB.
We see the start of the operation in the alert.log:
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.
Setting your datafiles to extend automatically provides these advantages:
Reduces the need for immediate intervention when a tablespace runs out of space
Ensures applications will not halt or be suspended because of failures to allocate extents
To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.
The following example enables automatic extension for a datafile added to the users tablespace:
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
The next example disables the automatic extension for the datafile.
Oracle Database SQL Language Reference for more information about the SQL statements for creating or altering datafiles
file size
The source one is 19398664192=18GB which is exactly what I got in the last resize message from the alert.log but the target one is still 14.6GB which is the size when it has been created at the beginning of the move. The double write occurs only for the blocks that have already been copied and the move did not reach the 14.6 GB yet.
We can see that from disk usage. ‘ls’ displays the declared size but ‘du’ counts the actual size – only the blocks that have been written yet.
The target file has only 6894604=6.5 GB yet but it keeps increasing:
Note: I verified that if the move datafile session is suspended, the target file disk usage does not increase even when we have activity on the tablespace.
The move is continuing and at the point it reaches a block above the initial size the target file is resized:
And finally, the move is completed without any problem:
Временное табличное пространство по умолчанию
Когда вы создаете пользователей базы данных, то должны назначить каждому временное табличное пространство по умолчанию, в котором они будут выполнять свои временные работы, подобные сортировке. Если не указать явно пользователю его временное табличное пространство, для этих целей применяется табличное пространство System, что может привести к высокой степени фрагментации этого табличного пространства, помимо его заполнения и торможения всей деятельности базы данных.Избежать таких нежелательных ситуаций можно, создав временное табличное пространство по умолчанию (default) для базы данных при ее создании с помощью конструкции DEFAULT TEMPORARY TABLESPACE. Oracle затем будет использовать это временное табличное пространство по умолчанию для всех пользователей, кому таковое не будет назначено явно. Создание временного табличного пространства по умолчанию будет продемонстрировано в моей новой статье, где пойдет речь о создании новой базы данных Oracle.
Обратите внимание, что если не создать табличное пространство по умолчанию при создании базы данных, сделать его можно и впоследствии. Нужно просто создать временное табличное пространство, как показано в предыдущем примере, и сделать его временным табличным пространством по умолчанию для всей базы данных, используя оператор вроде следующего:
Узнать имя текущего временного табличного пространства по умолчанию для базы данных можно, выполнив следующий запрос:
На заметку! Для временного табличного пространства использовать конструкцию AUTOALLOCATE нельзя. По умолчанию все временные табличные пространства создаются с локально управляемыми экстентами унифицированного размера. Размер экстента по умолчанию составляет 1 Мбайт, как и для всех других табличных пространств, но при желании можно задать другой размер экстента при создании временного табличного пространства.
manual resize
If I want to resize the datafile manually, I can’t:
automatic resize
So what happens if the datafile is autoextensible and I add data to it? I’ve run some ‘allocate extent’ and inserts and got the resize to occur:
and let that continue
until the filesystem is full
My datafile is now 18GB.
Группы временных табличных пространств
Крупные транзакции иногда могут приводить к переполнению временного пространства. Задачи, связанные с объемными сортировками, особенно включающие таблицы с несколькими разделами, приводят к значительной нагрузке на временные табличные пространства, от чего может пострадать производительность. В Oracle Database 10g была введена концепция группы временных табличных пространств, которая позволяет использовать временные табличные пространства в разных сеансах.
Ниже перечислены некоторые из основных характеристик группы временных табличных пространств.
- Группа временных табличных пространств должна состоять минимум из одного табличного пространства. Ограничений на максимальное их количество не существует.
- Если вы удалите все члены группы временных табличных пространств, группа также будет автоматически удалена.
- Группа временных табличных пространств имеет то же самое пространство имен,что и временные табличные пространства, являющиеся частью группы.
- Имя временного табличного пространства не может совпадать с именем любой из групп табличных пространств.
- При назначении временного табличного пространства пользователю можно применять имя группы временных табличных пространств вместо имени конкретного табличного пространства. Имя группы можно также использовать, назначая временное табличное пространство по умолчанию для всей базы данных.
Update 11-AUG-2018
Установка группы в качестве табличного пространства по умолчанию для базы данных
Вы можете использовать группу временных табличных пространств в качестве временного табличного пространства базы данных по умолчанию. Если выполнить показанный ниже оператор, то все пользователи, не имеющие временного табличного пространства по умолчанию, смогут использовать любое табличное пространство из группы tmpgrp1 в качестве временного табличного пространства по умолчанию:
Приведенный оператор ALTER DATABASE назначает все табличные пространства из группы tmpgrp1 в качестве временных табличных пространств по умолчанию для всей базы данных.
Изменение временного табличного пространства
С помощью команды ALTER TEMPORARY TABLESPACE можно выполнять различные задачи управления временным табличным пространством, включая добавление временного файла для его увеличения. Вот пример того, как можно увеличить временное табличное пространство:
Аналогичным образом можно использовать команду ALTER TABLESPACE для изменения размера временного файла:
Для уничтожения временного файла и удаления соответствующего файла операционной системы служит следующий оператор:
Когда вы уничтожаете временный файл, относящийся к временному табличному пространству, то само табличное пространство остается в использовании.
Временное табличное пространство можно сократить, как и любое нормальное табличное пространство. Следующий пример показывает применение команды ALTER TABLESPACE для сокращения временного табличного пространства:
Manually Resizing a Datafile
You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.
Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.
In the next example, assume that the datafile /u02/oracle/rbdb1/stuff01.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.
The following statement decreases the size of datafile /u02/oracle/rbdb1/stuff01.dbf :
It is not always possible to decrease the size of a file to a specific value. It could be that the file contains data beyond the specified decreased size, in which case the database will return an error.
Когда ваше табличное пространство заполняется данными таблиц или индексов, необходимо увеличить его размер. Это делается добавлением файлов в команде ALTER TABLESPACE:
Можно также увеличивать или уменьшать размер табличного пространства, увеличивая или уменьшая размер файлов данных табличного пространства опцией RESIZE.Обычно опция RESIZE применяется для исправления ошибок, допущенных при задании размера файла данных. Обратите внимание, что размер файла данных нельзя сделать меньше того, что уже занят объектами, хранящимися в нем.
Следующий пример показывает, как изменить размер файла данных вручную.Изначально файл имеет размер 250 Мбайт, а следующая команда удваивает его размер до 500 Мбайт. Заметьте, что для изменения размера файла данных необходимо использовать команду ALTER DATABASE, а не ALTER TABLESPACE.
При создании табличного пространства или при добавлении к табличному пространству файлов данных можно указывать конструкцию AUTOEXTEND, чтобы заставить Oracle автоматически расширять размер файлов данных в табличном пространстве до заданного максимума.
Вот как выглядит синтаксис использования средства AUTOEXTEND:
В предыдущем примере экстенты в 10 Мбайт будут добавляться к табличному пространству, когда понадобится дополнительное место, как указано в параметре AUTOEXTEND. Параметр MAXSIZE ограничивает табличное пространство размером в 1000 Мбайт. При желании можно также специфицировать MAXSIZE UNLIMITED; в этом случае не устанавливается максимальный размер данного файла данных, а следовательно и всего табличного пространства. Однако необходимо убедиться в наличии достаточного пространства на диске операционной системы.
Oracle также предоставляет средство Resumable Space Allocation, временно приостанавливающее операции, которые могут в противном случае завершиться сбоем из-за нехватки места, а затем возобновляет их выполнение после того, как вы добавите место для объекта базы данных. Это делает использование средства AUTOEXTEND менее привлекательным.
.
Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent.
But if you try to get lower, you will get:
So, how do you find this minimum value, which is the datafile’s high water mark?
You have the brute solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one.
Or there is the smart solution: find the datafile high water mark.
You can query DBA_EXTENTS to know that. But did you try on a database with a lot of datafiles? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don’t need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.
Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablepaces. And you have to read all of them.
Here is my query:
and here is a sample output:
You get directly the resize statements, with the reclaimable space in comments.
A few remarks about my query:
- I generate the resize statements only for datafiles which are autoextensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.
- When datafile is not autoextensible, or maxsize is not higher than the current size, I only generate a comment.
- When a datafile has no extents at all I generate a resize to 5MB. I would like to find the minimum possible size (without getting ORA-3214) but my test do not validate yet what is documented in MOS. If anyone has an idea, please share.
- There is probably a way to get that high water mark in a cheaper way. Because the alter statement gives the ORA-03297 much quicker. Information is probably available in the datafile headers, without going to segment headers, but I don’t know if it is exposed in a safe way. If you have an idea, once again, please share.
Note that I’m using that query for quite a long time. I even think that it was my first contribution to Oracle community on the web, about 9 years ago, in the dba-village website. Since then my contribution has grown to forums, blogs, articles, presentations, … and tweets. Sharing is probably addictive 😉
Leave a Reply Cancel Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
This section describes the various ways to alter the size of a datafile, and contains the following topics:
Добавление табличного пространства к группе временных табличных пространств
Как показано в предыдущем разделе, с помощью команды ALTER TABLESPACE можно добавить временное табличное пространство в группу. Можно также изменить группу, к которой относится данное табличное пространство, используя команду ALTER TABLESPACE. Например, можно указать, что табличное пространство temp02 принадлежит группе tmpgrp2, выполнив следующую команду:
При этом база данных создаст новую группу по имени tmpgrp2, если такой группы еще не существовало.
manual resize
If I want to resize the datafile manually, I can’t:
Создание группы временных табличных пространств
Когда вы назначаете первое временное табличное пространство в группу, то тем самым автоматически создаете группу. Чтобы создать группу табличных пространств, просто специфицируйте конструкцию TABLESPACE GROUP в операторе CREATE TABLESPACE,как показано ниже:
Приведенный оператор SQL создаст новое временное табличное пространство temp01 вместе с новой группой табличных пространств по имени tmpgrp1. Oracle создает новую группу табличных пространств, поскольку здесь при создании нового временного табличного пространства указана ключевая конструкция TABLESPACE GROUP.
Можно также создать группу временных табличных пространств, специфицируя ту же конструкцию TABLESPACE GROUP в команде ALTER TABLESPACE, как показано ниже:
Приведенный оператор заставит Oracle создать новую группу по имени tmpgrp1, поскольку ранее не существовало группы табличных пространств с таким именем.
Если вы специфицируете пару кавычек ('') вместо имени группы табличных пространств, вы тем самым неявно указываете Oracle не включать данное временное табличное пространство в группу. Вот пример:
Приведенный оператор создает временное табличное пространство по имени temp02,которое является обычным временным табличным пространством, не относящимся ни к одной группе временных табличных пространств.
Если полностью пустить конструкцию TABLESPACE GROUP, будет создано обычное временное табличное пространство, также не относящееся ни к одной из групп:
Создание временного табличного пространства
Вы создаете временное табличное пространство точно так же, как и постоянное,лишь с тем отличием, что указываете конструкцию 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 Мбайт.
Преимущества групп временных табличных пространств
Использование группы временных табличных пространств вместо обычного одиночного временного табличного пространства обеспечивает следующие преимущества.
- Запросы SQL с гораздо меньшей вероятностью приведут к переполнению места,отведенного для сортировки, потому что теперь запрос может параллельно использовать несколько временных табличных пространств для сортировки.
- Можно специфицировать несколько временных табличных пространств по умолчанию на уровне базы данных.
- Параллельно работающие серверы в параллельных операциях будут эффективно использовать несколько временных табличных пространств.
- Один пользователь может параллельно использовать несколько временных табличных пространств в разных сеансах.
Update May 18th, 2016
Просмотр информации о группах табличных пространств
Для управления группами временных табличных пространств в базе данных можно использовать новое представление словаря данных DATA_TABLESPACE_GROUPS. Ниже показан пример простого запроса к этому представлению, который отображает имена всех групп табличных пространств:
Чтобы найти соответствие между табличными пространствами и группами, к которым они принадлежат, можно также воспользоваться представлением DBA_USERS. Вот пример:
44 Comments
this is great stuff Frank!
Hello.
I have a tablespace with 4 datafiles. The name is mail_ts.dbf. It had grown to over 90gb. I reviewed the application owner and discovered I could truncate a table. This reduced the size to 4GB. The resize will not work due o the high water mark. How can I resize the mail_ts.dbf to 4GB and use only one datafile?
Basically, you need to move (table move or index rebuild) extents that are above the free space. You can check which table/index it is from dba_extents.
Or much simple – but need 2x space – just move everything to another tablespace. This is the only solution if you want to reduce the number of datafiles rather than reduce their size but keep all.
Regards,
Franck.
Great script Franck! Just saved me a ton of ball ache.
Cheers
Ralph
Very usefull information! Thank you so much!
I have a doubt, the following queries (1 and 2) should return the same information?
1) select max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue where ktfbuefno = 8;
2)select max(block_id + blocks-1) hwm_blocks from dba_extents where file_id=8;
I am asking that because I have seen several places saying that the second one could be used to find the datafile HWM but I can’t resize my datafiles based on it (ORA-03297). From x$ktfbue I found a realistic number but unfortunatelly I dont’t know how to map it to database objects so i can move/shrink then to release free space and reduce fragmentation.
Hi Mark,
DBA_EXTENTS doesn’t show extents from tables in recyclebin. That may be the reason for the difference with querying directly x$ktfbue.
The mapping to objects is done by ktfbuesegfno,ktfbuesegbno which maps to HEADER_FILE, HEADER_BLOCKS in DBA_SEGMENTS.
Regards,
Franck.
I had a sql use to reduce some datafile and i loose it.
I find your page and really, it s so excellent. Simple, quick and efficient, i like it.
Thanks a lot Laurent for your feedback.
A good way to avoid to loose your sql is to put them in a blog post 😉
Regards,
Franck.
Your query is excellent, but it only checks for datafiles which are autoextend on. I tried to modify it for all datafiles but it still returning for autoextend on datafiles.
Can you help me modify your query for all datafiles?
Hi Rhaul,
Thanks for your feedback.
It’s on purpose that I generate resize statements only for autoextensible files that can reach back their original size, to avoid the risk of errors or monitoring alerts. The statements are commented out when datafile is not autoextensible.
So you should see them but commented out.
Regards,
Franck
Thanks for the quick response Frank, There are around 663 datafiles in my database.I want to resize all the datafiles which autoexend off as there are they have too much space. My plan is to do not resize the datafiles which are autoextend on.
I am trying to modify your query for this, but still unable to fetch the commands for the datafiles which are autoextend off.
Can you help me out with modifying your code?
Hi Frank, I was able to modify your query for autoextend off files. Also, this one will exclude UNDO tablespace datafiles which we should not resize.
Hi Rahul,
Thanks for sharing.
Regards,
Franck.
Incredible
Thanks for sharing
Many thanks for sharing this! It’s saved my a lot of time!.
Thank you, Frank!
I’ve left my headache!
Excellent! I have seen several versions of similar scripts in the past but this is the most elegantly efficient of them
This is indeed commendable work Frank.
Something not many people would share that easily.
Thanks for sharing this SQL. It performed very well even in a very huge database.
I made some changes to your query :
Thanks Amer.
You take the responsibility for the ‘execute immediate’ 😉
(I prefer to have people copy/paste so that they have a look at it)
Regards,
Franck.
Временное табличное пространство, в противоположность тому, что можно предположить из его наименования, существует в базе данных постоянно, как и все прочие табличные пространства, такие как 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 различает эти два типа файлов. Временные файлы содержат мало или вообще не содержат данных повторного выполнения, ассоциированных с ними.
Назначение групп временных табличных пространств при создании или изменении пользователей
Когда вы создаете новых пользователей, вы можете назначать им группы временных табличных пространств вместо одного временного табличного пространства. Вот пример:
Создав пользователя, можно также применить оператор ALTER USER, чтобы изменить группу табличных пространств, которую он будет использовать. Вот оператор SQL,который делает это:
Сокращение временных табличных пространств
Иногда может понадобиться увеличить временное табличное пространство, чтобы вместить данные очень крупного задания, которое интенсивно использует это временное табличное пространство. После завершения такого задания можно сократить это временное табличное пространство, используя конструкцию SHRINK SPACE в операторе ALTER TABLESPACE. Вот пример:
Конструкция SHRINK SPACE уменьшит временные файлы до минимального размера,который составляет около 1 Мбайт. С помощью конструкции KEEP можно задать минимальный размер для временных файлов, как показано ниже:
Oracle использует специальную логику при сжатии временных файлов во временном табличном пространстве. Предположим, что имеется временное табличное пространство, содержащее два временных файла по 1 Гбайт. Для сокращения табличного пространства до 1 Гбайт выдается следующая команда:
Если запросить представление V$TEMPFILE, можно будет увидеть следующее:
База данных сократит один из двух временных файлов вплоть до 1 Мбайт, а другой — только на 1 Мбайт, оставив в нем нетронутыми 999 Мбайт пространства. Если ваша цель — сократить определенный временный файл до заданного минимума, можете сделать это, указав имя временного файла, который нужно сократить:
Приведенный выше оператор ALTER TABLESPACE сокращает только указанный временный файл до размера, заданного в конструкции KEEP. Остальные временные файлы из табличного пространства TEMP остаются нетронутыми. Конструкция KEEP в приведенном выше операторе гарантирует, что временный файл, который был специфицирован, сохранит 500 Мбайт пространства. Следующий пример демонстрирует, как сократить отдельный временный файл, не указывая сохранившегося пространства:
Поскольку в предыдущем операторе конструкция KEEP указана не была, база данных сокращает указанный временный файл до минимально возможного размера, который составляет около 1 Мбайт.
Conclusion: no problem
Online datafile move is compatible with autoextensible datafile resize, without any problem. Of course, you should not plan a resize at the same time as a large load, for performance reasons, but it is works. Actually this feature is very reliable: no locks, efficient, and cleanup is well done even in case of crash (info is in controlfile).
Читайте также: