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

    PROCEDURE purge_partitions
   (
      p_owner            IN VARCHAR2
     ,p_name             IN VARCHAR2
     ,p_retention_period IN NUMBER
   ) IS
   BEGIN
      FOR partition_rec IN (SELECT partition_name
                                  ,high_value
                              FROM dba_tab_partitions
                             WHERE table_owner = p_owner
                               AND table_name = p_name)

      --drop partitions older than specified retention preriod
      LOOP
         IF SYSDATE >= add_months(to_date(substr(partition_rec.high_value
                                                ,12
                                                ,19)
                                         ,'YYYY-MM-DD HH24:MI:SS')
                                 ,p_retention_period)
         THEN
            execute_immediate('ALTER TABLE ' || p_owner || '.' ||
                              p_name || ' DROP PARTITION ' ||
                              partition_rec.partition_name);
         END IF;
      END LOOP;
   END purge_partitions;

Я пытаюсь добавить дополнительную детализацию в оператор цикла, поэтому вместо того, чтобы просто отбрасывать разделы ежемесячно, я хотел бы также продлить это на дни. Кроме того, просто к сведению, записи извлекаются из вкладки «Конфигурация», которая включает сведения о секционированных таблицах (владелец таблицы, имя и период хранения — тип данных NUMBER). Поэтому я не уверен, как это сделать. Любая помощь высоко ценится.


oracle partitioning sysdate
person p_eazy    schedule 15.01.2019    source источник
comment
Почему вы снова и снова задаете один и тот же вопрос? stackoverflow.com/questions/54148136/ stackoverflow.com/questions/53936930/ stackoverflow.com/questions/53837937/ (и даже снова и снова с одной и той же ошибкой)?   -  person Wernfried Domscheit    schedule 15.01.2019
comment
Что такое p_retention_period? Дни или месяцы? Или даже недели, годы, часы, ...?   -  person Wernfried Domscheit    schedule 15.01.2019
comment
@WernfriedDomscheit, прежде всего, хотя я задаю вопросы об одной и той же процедуре, вопросы, которые я задавал ранее, отличались от тех, которые я задаю в этом посте, поэтому я не совсем уверен, что вы имеете в виду, задавая одни и те же вопросы снова и снова с одним и тем же вина?. Также p_retention_period — это месяцы, однако я хотел бы изменить это на день, неделю и т. д.   -  person p_eazy    schedule 16.01.2019
comment
Под неисправностью я подразумеваю to_date(substr(partition_rec.high_value, .... Если вы называете свою процедуру, например, как purge_partitions('OWNER_NAME', 'TABLE_NAME', 6); - как вы узнаете, означает ли 6 6 недель или 6 дней?   -  person Wernfried Domscheit    schedule 16.01.2019
comment
Yh, так что это мой актуальный вопрос, как я могу различать 6 месяцев, 6 недель, 6 дней в коде? Я знаю о проблеме HIGH_VALUE - я скоро внесу изменения.   -  person p_eazy    schedule 16.01.2019

Ответы (2)


arrow_upward
0
arrow_downward

ИМХО, столбец HIGH_VALUE имеет тип данных LONG, поэтому его довольно сложно использовать в PL/SQL. Но вам все равно не нужно использовать это, есть конструкция ALTER ТАБЛИЦА ... УДАЛИТЬ РАЗДЕЛ ДЛЯ (to_date(....)). Таким образом, вам вообще не нужно иметь дело с сгенерированным именем раздела.

В следующем примере раздел интервала за сентябрь 2007 г. удаляется из таблицы продаж. Существуют только локальные индексы, поэтому никакие индексы не становятся недействительными.

ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));

PS: кажется, что это расширение SQL было запатентовано Oracle.

person ibre5041    schedule 15.01.2019
comment
Поскольку TO хочет написать общую процедуру, я бы не рекомендовал использовать расширенное имя раздела. Представьте, что вы запускаете ... DROP PARTITION FOR (DATE '2019-01-01'), а интервал между вашими разделами равен 1 месяцу — вы бы удалили текущий раздел. Или интервал раздела 12 часов - вы бы сбросили слишком мало разделов. - person Wernfried Domscheit; 16.01.2019

arrow_upward
0
arrow_downward

В вашей процедуре есть синтаксические ошибки (например, execute_immediate(...)).

Рабочая процедура будет такой. Вы должны назвать его drop_partitions, а не purge_partitions, потому что вы отбрасываете разделы. "Очистка" также возможна с ALTER TABLE ... TRUNCATE PARTITION ...:

CREATE OR REPLACE PROCEDURE drop_partitions(
   p_owner IN VARCHAR2, 
   p_name IN VARCHAR2, 
   p_retention_period IN INTERVAL DAY TO SECOND) IS

    ts TIMESTAMP;

    CURSOR TabPartitions IS 
    SELECT partition_name, high_value 
    FROM dba_tab_partitions 
    WHERE table_owner = p_owner 
        AND table_name = p_name;

BEGIN

    FOR partition_rec IN TabPartitions LOOP 
        EXECUTE IMMEDIATE 'BEGIN :ret := '||partition_rec.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - p_retention_period THEN
            --drop partitions older than specified retention preriod
            EXECUTE IMMEDIATE 'ALTER TABLE ' || p_owner ||'.'||p_name 
                 || ' DROP PARTITION ' || partition_rec.partition_name || ' UPDATE GLOBAL INDEXES';
        END IF;
    END LOOP;

END drop_partitions;

Затем вы можете вызвать эту процедуру, например, как

purge_partitions('OWNER_NAME', 'TABLE_NAME', INTERVAL '60' DAY);
purge_partitions('OWNER_NAME', 'TABLE_NAME', NUMTODSINTERVAL(100, 'day'));

Обратите внимание, у вас также есть YEAR TO MONTH INTERVAL в Oracle. Если вы хотите использовать это, вы также должны перегрузить процедуру, т.е. создать вторую процедуру:

CREATE OR REPLACE PROCEDURE drop_partitions(
   p_owner IN VARCHAR2, 
   p_name IN VARCHAR2, 
   p_retention_period IN INTERVAL YEAR TO MONTH) IS

   ... rest would be exactly the same as above.

Имейте в виду, что для INTERVAL YEAR TO MONTH вы получаете исключения для вычислений, таких как timestamp '2019-03-31 00:00:00' - INTERVAL '1' MONTH, см. noreferrer">Арифметика даты и времени/интервала

person Wernfried Domscheit    schedule 16.01.2019
comment
Как я могу объявить TabPartitions курсора в спецификации пакета? - person p_eazy; 21.01.2019
comment
Просто напишите это в спецификации пакета, а не в процедуре. - person Wernfried Domscheit; 21.01.2019