Как удалить партицию oracle
Мы уже рассказывали о том, почему секционирование баз данных очень важно для производительности DLP-системы и как мы реализовывали его в PostgreSQL. В этой статье речь пойдет об Oracle.
Специфика использования СУБД в DLP-решениях состоит в том, что объем данных прирастает очень быстро. Их невозможно держать в оперативном архиве, и долговременное хранение – это необходимость в компании численностью свыше хотя бы 50 человек. При этом оперативный архив наполняется так быстро, что отдавать информацию в долгосрочный архив приходится раз в 2 недели или чаще. Использование только встроенных средств СУБД требует знаний и опыта. Это главная сложность, и она, в общем-то, очевидна «на берегу».
Кроме того, возникают проблемы, не очевидные сразу. Как вернуть из долгосрочного архива партицию с данными более старой версии приложения и прицепить к более свежей? Что делать, если у них разных формат хранения данных? Что делать, если подключение секции было прервано, и она «зависла» между долговременным и оперативным архивом?
В целом, решение этих вопросов сводится к двум основным техническим задачам: автоматизация управления секциями в СУБД Oracle (отключение и подключение) и система «отката» секций в случае, если при подключении что-то пошло не так.
Что не так со встроенными механизмами Oracle DB
Убирать данные на ленту и возвращать их из долговременного архива помогает опция Partitioning, с ее помощью можно разделить таблицу на части по какому-то принципу, например, по диапазону дат. Кроме управляемости и доступности, такое разделение еще позволяет повысить производительность. Каждый период хранится в отдельном табличном пространстве, что позволяет с помощью технологии transportable tablespace, достаточно быстро перемещать табличные пространства между различными отчетными и архивными БД с различными версиями и платформами. Но проблема в том, что стандартных механизмов не всегда достаточно: они позволяют только создавать базовые структуры без учета специфики приложения. А дальше администратор вынужден создавать вокруг них кучу инструментов управления. Да и сам процесс отключения-подключения-переноса требует навыков администрирования БД. Поэтому задача минимум – автоматизировать этот процесс, сделать его доступным для администраторов приложений.
Мы разработали набор скриптов, с помощью которых можно управлять секционированными таблицами, получать любую информацию о них и т.д. Не нужно знание команд, опыта работы с СУБД. Администратор приложения просто запускает скрипт или выбирает в интерфейсе нужное действие, указывает нужную партицию, и все происходит само собой.
(Не)совместимость версий
Итак, мы автоматизировали отключение секций и отправку их в долгосрочный архив. Но с долгосрочным архивом есть проблема: иногда его нужно вернуть.
Допустим, администратор перенес в него несколько секций в старой версии. Через год вышла новая версия, в которой добавились новые поля в таблицы, новые индексы, и в долгосрочный архив ушло еще некоторое количество секций. А потом безопасник расследует некий инцидент, и ему необходимо поднять данные двухлетней давности, т.е. поднять секцию несколько версий назад и каким-то образом подключить ее к БД.
Структура таблиц новой версии иногда отличается от исторической. Необходим ряд проверок и изменений для архивной секции. Проверка всегда начинается со сравнения текущей версии Solar Dozor и версии СУБД, и подключаемой партиции. Если есть различия, запускаются процедуры, корректирующие метаданные, добавляются необходимые поля, индексы, ключи, проверяется консистентность подключаемых данных, и пр., удаляется лишнее.
Дополнительные сложности приносит и использование для поиска в Solar Dozor текстовых индексов. Есть некоторые баги, связанные с EXCHANGE PARTITION для текстовых индексов, созданных в разных версиях СУБД или при использовании transportable tablespace (до 12 версии index metadata corruption). Патчи не всегда есть для нужной версии или платформы. Пересоздавать индексы при подключении – не быстрая и достаточно ресурсоемкая процедура. Пришлось «впилить» workaround-ы в процедуры подключения партиции. Структура DR$ таблиц текстовых индексов подключаемой партиции «выравнивается» с текущей, апдейтится поле таблицы ctxsys.dr$index.
Есть и защита от разных ошибок администраторов. Например, на уровне приложения запрещены любые действия с партицией, в которую в данный момент заливаются данные и имеющую статус «current».
«Хьюстон, у нас проблема»
В ходе реализации этих механизмов мы столкнулись с еще одной проблемой, неожиданно часто возникающей у заказчиков. В процессе отключения что-то может пойти не так, вплоть до банального отключения электричества, так что подключение секции может в любой момент прерваться. В результате получаем базу, которая находится в «промежуточном» состоянии.
В СУБД Oracle есть DDL и DML. В DML реализован механизм для обеспечения транзакционной целостности, который откатывает назад результаты, если транзакция не прошла. В DDL такого механизма нет, и любые действия с секцией – это путь в один конец.
Мы разработали механизм, который проверяет выполнение всех шагов по отключению-подключению партиции и корректирует возникающие проблемы. В случае возникновения проблем механизм перезапускает операции с партицией с того момента, когда что-то пошло не так. Ошибки при отключении-подключении логируются, и это позволяет в любой момент узнать, какие проблемы и когда возникали.
I have a big table with lot of data partitioned into multiple partitions. I want to keep a few partitions as they are but delete the rest of the data from the table. I tried searching for a similar question and couldn't find it in stackoverflow. What is the best way to write a query in Oracle to achieve the same?
2 Answers 2
It is easy to delete data from a specific partition: this statement clears down all the data for February 2012:
A quicker method is to truncate the partition:
- Oracle won't let us truncate partitions if we have foreign keys referencing the table.
- The operation invalidates any partitioned Indexes so we need to rebuild them afterwards.
Also, it's DDL, so no rollback.
If we never again want to store data for that month we can drop the partition:
The problem arises when we want to zap multiple partitions and we don't fancy all that typing. We cannot parameterise the partition name, because it's an object name not a variable (no quotes). So leave only dynamic SQL.
As you want to remove most of the data but retain the partition structure truncating the partitions is the best option. Remember to invalidate any integrity constraints (and to reinstate them afterwards).
You should definitely run the loop first with execute immediate call commented out, so you can see which partitions your WHERE clause is selecting. Obviously you have a back-up and can recover data you didn't mean to remove. But the quickest way to undertake a restore is not to need one.
Afterwards run this query to see which partitions you should rebuild:
You can automate the rebuild statements in a similar fashion.
" I am thinking of copying the data of partitions I need into a temp table and truncate the original table and copy back the data from temp table to original table. "
That's another way of doing things. With exchange partition it might be quite quick. It might also be slower. It also depends on things like foreign keys and indexes, and the ratio of zapped partitions to retained ones. If performance is important and/or you need to undertake this operation regularly then you should to benchmark the various options and see what works best for you.
Для работы с партициями доступны следующие операции:
-
— перенести партицию в директорию detached ; — удалить партицию; — добавить партицию/кусок в таблицу из директории detached ; — скопировать партицию из другой таблицы; — скопировать партицию из другой таблицы с заменой; — переместить партицию в другую таблицу; — удалить все значения в столбце для заданной партиции; — очистить построенные вторичные индексы для заданной партиции; — создать резервную копию партиции; — удалить резервную копию партиции; — скачать партицию/кусок с другого сервера; — переместить партицию/кускок на другой диск или том. — обновить данные внутри партиции по условию. — удалить данные внутри партиции по условию.
DETACH PARTITION|PART
Перемещает заданную партицию в директорию detached . Сервер не будет знать об этой партиции до тех пор, пока вы не выполните запрос ATTACH.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
После того как запрос будет выполнен, вы сможете производить любые операции с данными в директории detached . Например, можно удалить их из файловой системы.
Запрос реплицируется — данные будут перенесены в директорию detached и забыты на всех репликах. Обратите внимание, запрос может быть отправлен только на реплику-лидер. Чтобы узнать, является ли реплика лидером, выполните запрос SELECT к системной таблице system.replicas. Либо можно выполнить запрос DETACH на всех репликах — тогда на всех репликах, кроме реплик-лидеров (поскольку допускается несколько лидеров), запрос вернет ошибку.
DROP PARTITION|PART
Удаляет партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос реплицируется — данные будут удалены на всех репликах.
DROP DETACHED PARTITION|PART
Удаляет из detached кусок или все куски, принадлежащие партиции.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
ATTACH PARTITION|PART
Добавляет данные в таблицу из директории detached . Можно добавить данные как для целой партиции, так и для отдельного куска. Примеры:
Как корректно задать имя партиции или куска, см. в разделе Как задавать имя партиции в запросах ALTER.
Этот запрос реплицируется. Реплика-иницатор проверяет, есть ли данные в директории detached .
Если данные есть, то запрос проверяет их целостность. В случае успеха данные добавляются в таблицу.
Если реплика, не являющаяся инициатором запроса, получив команду присоединения, находит кусок с правильными контрольными суммами в своей собственной папке detached , она присоединяет данные, не скачивая их с других реплик.
Если нет куска с правильными контрольными суммами, данные загружаются из любой реплики, имеющей этот кусок.
Вы можете поместить данные в директорию detached на одной реплике и с помощью запроса ALTER . ATTACH добавить их в таблицу на всех репликах.
ATTACH PARTITION FROM
Копирует партицию из таблицы table1 в таблицу table2 .
Обратите внимание, что данные не удаляются ни из table1 , ни из table2 .
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
REPLACE PARTITION
Копирует партицию из таблицы table1 в таблицу table2 с заменой существующих данных в table2 . Данные из table1 не удаляются.
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
MOVE PARTITION TO TABLE
Перемещает партицию из таблицы table_source в таблицу table_dest (добавляет к существующим данным в table_dest ) с удалением данных из таблицы table_source .
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования.
- Движки таблиц должны быть одинакового семейства (реплицированные или нереплицированные).
- Для таблиц должна быть задана одинаковая политика хранения.
CLEAR COLUMN IN PARTITION
Сбрасывает все значения в столбце для заданной партиции. Если для столбца определено значение по умолчанию (в секции DEFAULT ), то будет выставлено это значение.
CLEAR INDEX IN PARTITION
Работает как CLEAR COLUMN , но сбрасывает индексы вместо данных в столбцах.
FREEZE PARTITION
Создаёт резервную копию для заданной партиции. Если выражение PARTITION опущено, резервные копии будут созданы для всех партиций.
Создание резервной копии не требует остановки сервера.
Для таблиц старого стиля имя партиций можно задавать в виде префикса (например, 2019 ). В этом случае, резервные копии будут созданы для всех соответствующих партиций. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос формирует для текущего состояния таблицы жесткие ссылки на данные в этой таблице. Ссылки размещаются в директории /var/lib/clickhouse/shadow/N/. , где:
- /var/lib/clickhouse/ — рабочая директория ClickHouse, заданная в конфигурационном файле;
- N — инкрементальный номер резервной копии.
- если задан параметр WITH NAME , то вместо инкрементального номера используется значение параметра 'backup_name' .
При использовании нескольких дисков для хранения данных таблицы директория shadow/N появляется на каждом из дисков, на которых были куски, попавшие под выражение PARTITION .
Структура директорий внутри резервной копии такая же, как внутри /var/lib/clickhouse/ . Запрос выполнит chmod для всех файлов, запрещая запись в них.
Обратите внимание, запрос ALTER TABLE t FREEZE PARTITION не реплицируется. Он создает резервную копию только на локальном сервере. После создания резервной копии данные из /var/lib/clickhouse/shadow/ можно скопировать на удалённый сервер, а локальную копию удалить.
Резервная копия создается почти мгновенно (однако, сначала запрос дожидается завершения всех запросов, которые выполняются для соответствующей таблицы).
ALTER TABLE t FREEZE PARTITION копирует только данные, но не метаданные таблицы. Чтобы сделать резервную копию метаданных таблицы, скопируйте файл /var/lib/clickhouse/metadata/database/table.sql
Чтобы восстановить данные из резервной копии, выполните следующее:
- Создайте таблицу, если она ещё не существует. Запрос на создание можно взять из .sql файла (замените в нём ATTACH на CREATE ).
- Скопируйте данные из директории data/database/table/ внутри резервной копии в директорию /var/lib/clickhouse/data/database/table/detached/ .
- С помощью запросов ALTER TABLE t ATTACH PARTITION добавьте данные в таблицу.
Восстановление данных из резервной копии не требует остановки сервера.
Подробнее о резервном копировании и восстановлении данных читайте в разделе Резервное копирование данных.
UNFREEZE PARTITION
Удаляет с диска "замороженные" партиции с указанным именем. Если секция PARTITION опущена, запрос удаляет резервную копию всех партиций сразу.
FETCH PARTITION|PART
Загружает партицию с другого сервера. Этот запрос работает только для реплицированных таблиц.
Запрос выполняет следующее:
- Загружает партицию/кусок с указанного шарда. Путь к шарду задается в секции FROM (‘path-in-zookeeper’). Обратите внимание, нужно задавать путь к шарду в ZooKeeper.
- Помещает загруженные данные в директорию detached таблицы table_name . Чтобы прикрепить эти данные к таблице, используйте запрос ATTACH PARTITION|PART.
Следует иметь в виду:
- Запрос ALTER TABLE t FETCH PARTITION|PART не реплицируется. Он загружает партицию в директорию detached только на локальном сервере.
- Запрос ALTER TABLE t ATTACH реплицируется — он добавляет данные в таблицу сразу на всех репликах. На одной из реплик данные будут добавлены из директории detached , а на других — из соседних реплик.
Перед загрузкой данных система проверяет, существует ли партиция и совпадает ли её структура со структурой таблицы. При этом автоматически выбирается наиболее актуальная реплика среди всех живых реплик.
Несмотря на то что запрос называется ALTER TABLE , он не изменяет структуру таблицы и не изменяет сразу доступные данные в таблице.
MOVE PARTITION|PART
Перемещает партицию или кусок данных на другой том или диск для таблиц с движком MergeTree . Смотрите Хранение данных таблицы на нескольких блочных устройствах.
Запрос ALTER TABLE t MOVE :
- Не реплицируется, т.к. на разных репликах могут быть различные конфигурации политик хранения.
- Возвращает ошибку, если указан несконфигурированный том или диск. Ошибка также возвращается в случае невыполнения условий перемещения данных, которые указаны в конфигурации политики хранения.
- Может возвращать ошибку в случае, когда перемещаемые данные уже оказались перемещены в результате фонового процесса, конкурентного запроса ALTER TABLE t MOVE или как часть результата фоновой операции слияния. В данном случае никаких дополнительных действий от пользователя не требуется.
UPDATE IN PARTITION
Манипулирует данными в указанной партиции, соответствующими заданному выражению фильтрации. Реализовано как мутация mutation.
Пример
Смотрите также
DELETE IN PARTITION
Удаляет данные в указанной партиции, соответствующие указанному выражению фильтрации. Реализовано как мутация mutation.
Пример
Смотрите также
Как задавать имя партиции в запросах ALTER
Чтобы задать нужную партицию в запросах ALTER . PARTITION , можно использовать:
- Имя партиции. Посмотреть имя партиции можно в столбце partition системной таблицы system.parts. Например, ALTER TABLE visits DETACH PARTITION 201901 .
- Кортеж из выражений или констант, совпадающий (в типах) с кортежем партиционирования. В случае ключа партиционирования из одного элемента, выражение следует обернуть в функцию tuple(. ) . Например, ALTER TABLE visits DETACH PARTITION tuple(toYYYYMM(toDate('2019-01-25'))) .
- Строковый идентификатор партиции. Идентификатор партиции используется для именования кусков партиции на файловой системе и в ZooKeeper. В запросах ALTER идентификатор партиции нужно указывать в секции PARTITION ID , в одинарных кавычках. Например, ALTER TABLE visits DETACH PARTITION ID '201901' .
- Для запросов ATTACH PART и DROP DETACHED PART: чтобы задать имя куска партиции, используйте строковой литерал со значением из столбца name системной таблицы system.detached_parts. Например, ALTER TABLE visits ATTACH PART '201901_1_1_0' .
Использование кавычек в имени партиций зависит от типа данных столбца, по которому задано партиционирование. Например, для столбца с типом String имя партиции необходимо указывать в кавычках (одинарных). Для типов Date и Int* кавычки указывать не нужно.
Замечание: для таблиц старого стиля партицию можно указывать и как число 201901 , и как строку '201901' . Синтаксис для таблиц нового типа более строг к типам (аналогично парсеру входного формата VALUES).
Правила, сформулированные выше, актуальны также для запросов OPTIMIZE. Чтобы указать единственную партицию непартиционированной таблицы, укажите PARTITION tuple() . Например:
IN PARTITION указывает на партицию, для которой применяются выражения UPDATE или DELETE в результате запроса ALTER TABLE . Новые куски создаются только в указанной партиции. Таким образом, IN PARTITION помогает снизить нагрузку, когда таблица разбита на множество партиций, а вам нужно обновить данные лишь точечно.
Всем привет.
Есть некий большой лог, партиционированный по дате с интервалом в 1 день. Возник вопрос его периодической чистки, но учитывая что даже пустая партиция резервирует под себя дисковое пространство (насколько я помню), думаю имеет смысл именно дропать ненужные партиции, а не просто удалять данные. Возник вопрос, как определить список партиций подходящих под критерии удаления по значению ключа. Все что пока пришло на ум, это нечто типа
Есть подозрение, что я изобретаю велосипед с квадратными колесами и у оракла должна быть штатная функция определения имени партиции(й) по значению(диапазону).
Действительно ли такая функция есть?
Не понятно - секции динамически добавляются? Ты не имеешь контроля над присваиваемым им именам? Или секции статические, скажем по номеру дня (31 штука) и просто периодически удаляются данные за прошлые месяцы?
Просто я не вижу особой нужды в функции "определения имени партиции по значению" Тем более что схем секционирования хватает и помимо Range - где вообще теряет смысл подобная задача.
Как добавляются - не уверен, из дому нет доступа к базе. Имена генерятся ораклой автоматически. Секций по количеству дней, похоже что их там чуть менее 2 тыс.(период около 4-5 лет). Ни данные, ни секции до сегодняшнего дня не удалялись.
Igor KorolyovПросто я не вижу особой нужды в функции "определения имени партиции по значению" А как тогда сделать чтобы больше туда руками не лезть? Чтобы все чистилось автоматом по джобе в любой момент времени нужно как-то определять список секций-кандидатов на удаление.
Очевидно что проще всего генерить их имена явно, включая в имя YYYYMMDD (если критерий секционирования это range на 1 день) и соответственно удалять старые будет несложно не залазя в high_value - по тем же самым именам. Равно и создавать новые.
Сделать это будет примерно так же легко, как увидеть в полдень 20-летнюю девственницу верхом на единороге.Мы на аутсорсе, база нам не принадлежит. Нужно убедить заказчика в необходимости этих телодвижений ПМу, согласовать изменения, аналитикам написать ТЗ, создать задачи в джире, мне переделать, нашим тестерам протестировать, их тестерам протестировать, внедрить. Это месяца полтора, в лучшем случае, а дисковое пространство закончилось уже сейчас.
Потом, переименовать будет тоже не так уж просто. Легче всего было бы переименовать старую таблицу, создать новую с таким же именем и перелить данные, но на диске нет свободного места. Переименовывать секции с автоматически сгенерированными именами (имена которых еще и отличаются на дев, тест и прод) вручную - то еще удовольствие, а чтобы написать скрипт автоматически переименовывающий имена по какому-то правилу нужно опять же определить текущее имя секции по значению. В общем, не вариант. В перспективе может кто-нибудь когда-нибудь и переделает, но сейчас придется, видимо, заморачиваться с выковыриванием значения из high_value.
Сейчас - да. На будущее - как хотите, можно сделать получше, можно оставить как есть. Решение же есть Если владелец базы дурак, то вполне логично что и решения для его базы будут дурацкие
Хм, задача осложнилась тем, что user_tab_partitions.high_value имеет тип LONG, как выяснилось. Пришлось прокачать свой велосипед, добавив к нему еще один велосипед (чтобы я мог крутить педали пока не дали пока кручу педали). В общем, может еще кому пригодится:
Да, я когда-то функцию на pl/sql городил (точнее искал на просторах сети готовое решение ) чтобы с long полем более-менее нормально работать (в clob его загонял, потом можно было в запрос такую функцию вставлять) - через DBMS_SQL. Тоже для метаданных - но мне нужен был доступ к исходникам триггеров и ещё чему-то такого же рода, в long хранящемуся. Наверное тогда ещё с xml не было варианта, или не набрёл просто
Читайте также: