1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to drop partitions based on date

Discussion in 'SQL PL/SQL' started by tspoon, Mar 28, 2017.

  1. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
    Hi,

    I have a number of tables with partitions by month

    My first script where I input the actual table works fine - as in it will drop the partitions which meet the criteria (see below)

    Code (SQL):
    DECLARE
      v_sql             varchar2(500);
      v_date            DATE;
      v_partition_name  user_tab_partitions.partition_name%TYPE;
      v_high_value      user_tab_partitions.high_value%TYPE;
      CURSOR c1 IS SELECT PARTITION_NAME, HIGH_VALUE
                   FROM user_tab_partitions WHERE TABLE_NAME='TEST_YFS_BAT_LOC_H' AND PARTITION_NAME!='P0';
    BEGIN
      OPEN c1;
      LOOP
        FETCH c1 INTO v_partition_name, v_high_value;
        EXIT WHEN c1%NOTFOUND;
        v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
        IF v_date < (sysdate - 30) THEN
          v_sql := 'alter table TEST_YFS_BAT_LOC_H drop partition ' || v_partition_name;
          EXECUTE immediate v_sql;
        END IF;
      END LOOP;
      CLOSE c1;
    END;
    /
    But I want to be able to drop all partitions from all tables belonging to this schema. So how can I do something similar which goes through all the tables and partitions and does the same thing?

    I am executing this script as the schema owner with 100+ tables

    Thanks in advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The easiest 'fix' is to eliminate the WHERE Clause in your cursor; that will process every partitioned table in the schema.
     
  3. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
    Hi,

    When I try to eliminate this - see below:

    Code (SQL):
     DECLARE
      v_sql             varchar2(500);
      v_date            DATE;
      v_partition_name  user_tab_partitions.partition_name%TYPE;
      v_high_value      user_tab_partitions.high_value%TYPE;
      CURSOR c1 IS SELECT PARTITION_NAME, HIGH_VALUE
                   FROM user_tab_partitions WHERE PARTITION_NAME!='P0' AND PARTITION_NAME LIKE '%_H_%';
    BEGIN
      OPEN c1;
      LOOP
        FETCH c1 INTO v_partition_name, v_high_value;
        EXIT WHEN c1%NOTFOUND;
        v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
        IF v_date < (sysdate - 30) THEN
          v_sql := "alter table 'c1' drop partition  || v_partition_name";
          EXECUTE immediate v_sql;
        END IF;
      END LOOP;
      CLOSE c1;
    END;
    /
    I get an error with the alter table statement line

    *
    ERROR at line 15:
    ORA-06550: line 15, column 16:
    PLS-00114: identifier 'alter table 'c1' drop partitio' too long


    SQL>


    Can you advise what I need to place here

    Code (SQL):
    "alter table 'c1' drop partition  || v_partition_name";
     
  4. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
     
  5. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You do need to include the table name in your select list; how else will you pair up partitions with tables?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    'c1' is not a table name, it's the name of your declared cursor so you can't use it as you have in the 'alter table' statement. Additionally you need to return the table name as well as the partition name to match one with the other to process the correct table. The following modifications were made to your code; please read them through and learn what has been done:

    Code (SQL):
    DECLARE
      v_sql             varchar2(500);
      v_date            DATE;
      v_tab_name        user_tab_partitions.table_name%TYPE;
      v_partition_name  user_tab_partitions.partition_name%TYPE;
      v_high_value      user_tab_partitions.high_value%TYPE;
      CURSOR c1 IS SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
                   FROM user_tab_partitions WHERE PARTITION_NAME!='P0' AND PARTITION_NAME LIKE '%_H_%';
    BEGIN
      OPEN c1;
      LOOP
        FETCH c1 INTO v_tab_name, v_partition_name, v_high_value;
        EXIT WHEN c1%NOTFOUND;
        v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
        IF v_date < (sysdate - 30) THEN
          v_sql := 'alter table '||v_tab_name||' drop partition  '|| v_partition_name;
          dbms_output.put_line(v_sql);
          EXECUTE immediate v_sql;
        END IF;
      END LOOP;
      CLOSE c1;
    END;
    /
     
     
  8. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
    Thankyou - this is exactly what I am seeking

    Tested and works as should

    Much appreciated for your help