Как обрезать разделы с данными старше 2 месяцев?
Например, у меня есть имена таблиц / разделов ниже:
select table_name, partition_name from all_tab_partitions where table_name='TABLENAME';
TABLENAME partitionname1_P30 30
TABLENAME partitionname2_P60 60
TABLENAME partitionname3_P90 90
TABLENAME partitionname4_P120 120
TABLENAME partitionname5_P150 150
TABLENAME partitionname6_P180 180
TABLENAME partitionname7_210 210
TABLENAME partitionname8_P240 240
TABLENAME partitionname9_P270 270
TABLENAME partitionname10_P300 300
TABLENAME partitionname11_P330 330
TABLENAME partitionname12_P360 360
Таблица разбивается по месяцам. Если сейчас сентябрь, как мне обрезать разделы старше 2 месяцев?
Ожидается, что останутся только записи с августа по сентябрь (имя раздела8-9), а остальные будут усечены.
CREATE TABLE dbo1.TABLENAME
( PARTITION_ID NUMBER(4, 0) NOT NULL,
TABLE_DATE DATE NOT NULL,
TABLE_TIMESTAMP NUMBER(19, 0) NOT NULL,
TABLE_BUNDLE_ID VARCHAR2(240 BYTE) NOT NULL,
TABLE_TYPE NUMBER(8, 0) NOT NULL,
TABLE_SEVERITY NUMBER(19, 0) NOT NULL,
TABLE_FACILITY NUMBER(19, 0) NOT NULL,
TABLE_HOST VARCHAR2(120 BYTE) NOT NULL,
TABLE_PROCESS VARCHAR2(240 BYTE) NOT NULL,
TABLE_SYSTEM VARCHAR2(240 BYTE) NOT NULL,
TABLE_SESSION_ID VARCHAR2(240 BYTE) NOT NULL,
TABLE_PRINCIPAL VARCHAR2(120 BYTE) NOT NULL,
OBJECT_ID VARCHAR2(120 BYTE),
OBJECT_TYPE VARCHAR2(2 BYTE),
CLIENT_HOST VARCHAR2(120 BYTE),
ACCESS_HOST VARCHAR2(120 BYTE),
SCOPE_ID VARCHAR2(120 BYTE),
STATUS NUMBER(19, 0),
OBJECT_HISTORY NUMBER(19, 0),
TABLE_DETAILS VARCHAR2(4000 BYTE)
)
PARTITION BY RANGE (PARTITION_ID)
(
PARTITION partitionname1_P30 VALUES LESS THAN (30)
,<repeat partition by 30s up to 360, total of 12 partitions>
Возможный дубликат Удалить разделы старше 2 месяцев
Для интервальное разбиение см. Решение здесь
Ваши определения разделов довольно странные. Насколько я понимаю, раздел partitionname2_P60
содержит данные с 30 января по 1 марта (в случае листового года), то есть охватывает три месяца - что вы имеете в виду под «старше 2 месяцев» ?. Создайте простой раздел INTERVAL
на TABLE_DATE
или на виртуальном столбце EXTRACT(MONTH FROM TABLE_DATE)
Не могли бы вы запустить select table_name, partition_name, HIGH_VALUE from all_tab_partitions where table_name='TABLENAME';
Я имел в виду, что есть 12 разделов (1 раздел в месяц). Когда я запускаю запрос, ниже показан результат: table_name = TABLENAME' partition_name='partitionname1_P30' high_value='30
. обновление вопроса снова ...
Что происходит с последними днями года? В одном году 365/366 дней, а не 360!
Сделать это можно так:
DECLARE
CURSOR PartTables IS
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'TABLENAME';
highValue TIMESTAMP;
BEGIN
FOR aTab IN PartTables LOOP
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
IF highValue < ADD_MONTHS(SYSDATE, -2) THEN
EXECUTE IMMEDIATE 'ALTER TABLE TABLENAME TRUNCATE PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
END IF;
END LOOP;
END;
Это будет работать для разделов на основе RANGE или INTERVAL, однако в этом случае ваше требование бесполезно, потому что вы навсегда сохраните пустые разделы. Обычно старые разделы удаляются, для этого просто заменяют TRUNCATE
на DROP
.
Если ваш раздел основан на СПИСКЕ, то есть на номере месяца, решение будет следующим:
DECLARE
CURSOR PartTables IS
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'TABLENAME';
highValue INTEGER;
BEGIN
FOR aTab IN PartTables LOOP
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
IF highValue NOT IN (
EXTRACT(MONTH FROM SYSDATE),
EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -1))
)
THEN
EXECUTE IMMEDIATE 'ALTER TABLE TABLENAME TRUNCATE PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
END IF;
END LOOP;
END;
Исходя из вашего странного определения раздела, условия будут такими:
IF highValue NOT IN (
30*CEIL(TO_CHAR(SYSDATE, 'fmddd')/30),
30*CEIL(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'fmddd')/30)
)
THEN
но вы можете столкнуться с проблемами, если запустите процедуру в период с 26 по 31 декабря.
Не могли бы вы объяснить это? Поскольку я раньше не пробовал использовать TRUNCATE ..
Что ты имеешь в виду? Вы просили «Обрезать разделы ...» - это одно из решений.
Я попробовал первый, но он выдает `DECLARE` *
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 12
Покажите, пожалуйста, определение вашей таблицы, т.е. оператор CREATE TABLE. Как вы определили разделы?
пожалуйста, смотрите обновления в вопросах для определения таблицы. Я также добавил ограничения таблицы для нескольких столбцов
Виноват. Последний диапазон на самом деле 330, затем 367
какая у вас СУБД, кажется Oracle ...?