Tag Archives: delete partition older then

Delete old S_NQ_ACCT/S_NQ_DB_ACCT partitions

In the previous post I’ve described how to partition the S_NQ_ACCT and S_NQ_DB_ACCT tables used by OBIEE for Usage Tracking. Now I’m gonna describe how to delete partitions that are X days old.

When we partitioned the table, we configured it to be done daily. Now if we are going to issue the following command:

conn DEV_BIPLATFORM;
SELECT * FROM user_tab_partitions;

we are gonna notice that Oracle Database created the HIGH_VALUE as “TO_DATE(‘ 2013-11-09 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)”. If you want to delete all partitions that are 32 days old, I’ve used the following sql script:

SET serveroutput ON
SET feedback off
variable v_days_to_keep NUMBER;
exec :v_days_to_keep := 32;
 
DECLARE
    v_statement VARCHAR2(4000);
BEGIN
    FOR x IN (SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME = 'S_NQ_DB_ACCT') LOOP
      IF x.PARTITION_NAME != 'PART1'
        THEN
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          EXECUTE IMMEDIATE v_statement;
      END IF;
    END LOOP;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT disable CONSTRAINT "FK_S_NQ_DB_ACCT"';
    FOR x IN (SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME = 'S_NQ_ACCT') LOOP
      IF x.PARTITION_NAME != 'PART1'
        THEN
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          EXECUTE IMMEDIATE v_statement;
      END IF;
    END LOOP;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT enable novalidate CONSTRAINT "FK_S_NQ_DB_ACCT"';
END;
/

I’ve tested this and it’s working perfectly on OBIEE 11.1.1.7.1 with DB 11.2.0.3. All you have to do now is set a job in the DB or sh script in the OS to run daily. I’ve went with the second option, because it’s simpler for me to receive email alerts if anything goes wrong with the script. I’ve also added some output to the script so I know when something is deleted. The sh script is below. Please change the password and tns_entry accordingly to you environment.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin/:${PATH}
export TNS_ADMIN=$ORACLE_HOME/network/admin
 
before=`sqlplus -s DEV_BIPLATFORM/pass@tnsentry << EOFSQL
set heading off
set pages 0
select count(1) from user_tab_partitions;
exit;
EOFSQL`
 
sqlplus -s DEV_BIPLATFORM/pass@tnsentry << EOFSQL
Set serveroutput on
set feedback off
variable v_days_to_keep number;
exec :v_days_to_keep := 32;
 
declare
    v_statement varchar2(600);
begin
    for x in (select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME = 'S_NQ_DB_ACCT') loop
      if x.PARTITION_NAME != 'PART1'
        then
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          execute immediate v_statement;
      end if;
    end loop;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT disable CONSTRAINT "FK_S_NQ_DB_ACCT"';
    for x in (select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME = 'S_NQ_ACCT') loop
      if x.PARTITION_NAME != 'PART1'
        then
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          execute immediate v_statement;
      end if;
    end loop;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT enable novalidate CONSTRAINT "FK_S_NQ_DB_ACCT"';
end;
/
exit;
EOFSQL
 
after=`sqlplus -s DEV_BIPLATFORM/pass@tns_entry << EOFSQL
set heading off
set pages 0
select count(1) from user_tab_partitions;
exit;
EOFSQL`
 
deleted=`expr $before - $after`
[[ ! $deleted -eq 0 ]] && echo "$deleted paritions deleted."