Skip to content
March 17, 2017 / doganay

AUTOMATE DROPPING OLD PARTITIONS

--Print drop partition script older then 3 months for table owner1.t1
BEGIN
    FOR c
        IN (SELECT table_owner, table_name, partition_name, high_value
              FROM dba_tab_partitions
             WHERE     table_owner = 'OWNER1'
                   AND table_name = 'T1')
    LOOP
        EXECUTE IMMEDIATE
               'BEGIN
             IF sysdate >= ADD_MONTHS('
            || c.high_value
            || ', 3) THEN
                dbms_output.put_line(''ALTER TABLE '
            || c.table_owner
            || '.'
            || c.table_name
            || ' DROP PARTITION '
            || c.partition_name
            || ';'');
                  END IF;
          END;';
    END LOOP;
END;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: