I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition
.
PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 01-jan-2013','DD-MON-YYYY' )))
This gives me a new system generated partition names every day (like SYS41
, SYS42
, ...).
I have two questions:
I want a script which should run daily and change this system generated name to custom name (like "01-jan-2013" and so on for every date). This script should be run daily through
dbms_scheduler
. I have a script which give me the name of latest created partition.SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS A WHERE PARTITION_POSITION = ( SELECT MAX(PARTITION_POSITION) FROM USER_TAB_PARTITIONS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.TABLE_NAME = 'IPART' );
This may help you to give the answer.
I want a script which should delete the old partitions (say 3 months) automatically. This should also run daily. And that 3 months time should be configurable.
How can I achieve this?