New feature of oracle 11g Partition Table-pits in interval Partition

New feature of oracle 11g Partition Table-pits in interval Partition

The new interval partition feature in the range partition table of oracle 11g, which does not need to be defined. MAXVALUE,Oracle dynamically allocates new partitions to accommodate data beyond the range according to the step size defined by the partition.

The problem that the original range partition needs to be manually written job to pre-allocate the partition is solved, but the partition name is named automatically.

Therefore, the partition query criteria for interval partition interval (number) or interval (NUMTODSINTERVAL (1 minutes day') numbers or date automatic partition can be:

Select * from test_part partition for (number/date); can you also use this method when automatically cleaning up partitions? Here comes the pit, use this method to write a stored procedure to delete the old partition that was 30 days ago:

Create or replace procedure pd01.deltestdata_new (v_keep_days NUMBER DEFAULT 30) Authid CURRENT_USER

Is

V_date date

V_part_number number

Begin

V_date: = sysdate-v_keep_days

Select count (partition_name) into v_part_number from all_tab_partitions where table_owner='PD01' and table_name='PD_TESTDATA'

If v_part_number > = v_keep_days then

Execute immediate 'ALTER TABLE PD01.PD_TESTDATA DROP PARTITION FOR (' | | v_date | |')'

Select count (partition_name) into v_part_number from all_tab_partitions where table_owner='PD01' and table_name='PD_TESTDATA'

Insert into pdms01.del_testdata_log (exec_time,dropped_partition_date,remain_partition_number) values (sysdate,to_char (v_date), v_part_number)

Commit

Else

Insert into pdms01.del_testdata_log (exec_time,dropped_partition_date,remain_partition_number) values (sysdate,'none',v_part_number)

Commit

End if

End

/

Exec pdms01.deltestdata_new;-I can't believe it was wrong.

ORA-14763: cannot resolve FOR VALUES clause to partition number

ORA-06512: in "PDMS01.TEST11", line 7

ORA-06512: in line 1

Reason:

The partition for syntax in the interval partition does not support the use of bound variables. What a hoax.

Automatic cleanup of partitions can only be achieved by getting the partition name in other ways.