Sample script to drop interval partitions

SITUATION

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;

Name Null? Type
----------------------- -------- --------------
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER

SOLUTION

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> set serveroutput on
SQL> declare
dt date;
begin
for x in (select table_name,partition_name, high_value
from user_tab_partitions where INTERVAL='YES' )
loop
execute immediate 'select '||x.high_value||' from dual' into dt;
if dt < sysdate - 20
then
dbms_output.put_line('to drop partition: '||x.table_name||'.'||x.partition_name);
execute immediate 'alter table '||x.table_name||' drop partition '|| x.partition_name||' update indexes ';
end if;
end loop;
end;
/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

SITUATION

An interval partition table structure is just created, then try to import the data from exported dump file. It causes ORA-14300 error. The same error occurs when insert records from another online backup table:

SQL> insert into tbl_test select * from tbl_test_old;
insert into tbl_test select * from tbl_test_old;
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

CAUSE

There are NULL values in partition key column.

SOLUTION

Update NULL values of  partition key column with non-null values, or remove those invalid records from source data.

Please note there are possible 1048575 partitions or sub-partitions for the partition key:

$ oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action Ensure that the partitioning key falls within 1048575 partitions or subpartitions.