Oracle Database Discussions

Hi All,

I am working on 11gR2 database on linux and Recently I have created an HOURLY interval partitioning for a table and the management was decide to 3 days of retention policy. So i need to schedule a Cron job for removing older partitions, which are older than 3days, but i am not sure, how to write a shell script or a procedure to do this. Please help me on this and below are the table syntax and the partitions.

CREATE TABLE TRANSACTION
(
ID NUMBER(18) NOT NULL ,
ACCT_ID NUMBER(18) NOT NULL ,
BANKING_ID NUMBER(18) NOT NULL ,
CREATED_DATE TIMESTAMP(3) NOT NULL ,
COMMISSION_AMOUNT NUMBER(15,2) NULL ,
CONFIRMATION_NO NVARCHAR2(255) NULL ,
CREATED_BY NVARCHAR2(255) NOT NULL ,
CREATED_TS TIMESTAMP(3) NOT NULL ,
MODIFIED_BY NVARCHAR2(255) NOT NULL ,
MODIFIED_TS TIMESTAMP(3) NOT NULL
)
PARTITION BY RANGE ("CREATED_TS") INTERVAL( NUMTODSINTERVAL(1, 'HOUR'))
( PARTITION TRANS_DATA VALUES LESS THAN (TO_DATE(' 2011-11-04 20:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P,
PARTITION TRANS_DATA1 VALUES LESS THAN (TO_DATE(' 2011-11-04 21:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P1,
PARTITION TRANS_DATA2 VALUES LESS THAN (TO_DATE(' 2011-11-04 22:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P2,
PARTITION TRANS_DATA3 VALUES LESS THAN (TO_DATE(' 2011-11-04 23:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P3,
PARTITION TRANS_DATA4 VALUES LESS THAN (TO_DATE(' 2011-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P4,
PARTITION TRANS_DATA5 VALUES LESS THAN (TO_DATE(' 2011-11-05 01:00:00', 'YYYY-MM-DD HH24:MI:SS') ) TABLESPACE &&TABLE_TS_P5
);

Here Partitioning key is "CREATED_TS".

HERE are the partitions created for this table.

SQL>select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where TABLE_NAME='TRANSACTION';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------------------------ -------------------------------------------------------------------------------- ------------------
TRANS_DATA TIMESTAMP' 2011-11-04 20:00:00' 1
TRANS_DATA1 TIMESTAMP' 2011-11-04 21:00:00' 2
TRANS_DATA2 TIMESTAMP' 2011-11-04 22:00:00' 3
TRANS_DATA3 TIMESTAMP' 2011-11-04 23:00:00' 4
TRANS_DATA4 TIMESTAMP' 2011-11-05 00:00:00' 5
TRANS_DATA5 TIMESTAMP' 2011-11-05 01:00:00' 6
SYS_P41 TIMESTAMP' 2011-11-05 02:00:00' 7
SYS_P42 TIMESTAMP' 2011-11-05 03:00:00' 8
SYS_P44 TIMESTAMP' 2011-11-05 04:00:00' 9
SYS_P46 TIMESTAMP' 2011-11-05 05:00:00' 10
SYS_P49 TIMESTAMP' 2011-11-05 06:00:00' 11
SYS_P52 TIMESTAMP' 2011-11-05 07:00:00' 12
SYS_P102 TIMESTAMP' 2011-11-05 08:00:00' 13
SYS_P121 TIMESTAMP' 2011-11-05 09:00:00' 14
SYS_P141 TIMESTAMP' 2011-11-05 10:00:00' 15
SYS_P144 TIMESTAMP' 2011-11-05 11:00:00' 16
SYS_P147 TIMESTAMP' 2011-11-05 12:00:00' 17
SYS_P149 TIMESTAMP' 2011-11-05 13:00:00' 18
SYS_P151 TIMESTAMP' 2011-11-05 14:00:00' 19
SYS_P152 TIMESTAMP' 2011-11-05 15:00:00' 20
SYS_P154 TIMESTAMP' 2011-11-05 16:00:00' 21
SYS_P157 TIMESTAMP' 2011-11-05 17:00:00' 22
SYS_P222 TIMESTAMP' 2011-11-05 18:00:00' 23
SYS_P159 TIMESTAMP' 2011-11-05 19:00:00' 24
SYS_P243 TIMESTAMP' 2011-11-05 20:00:00' 25
SYS_P261 TIMESTAMP' 2011-11-05 21:00:00' 26
SYS_P282 TIMESTAMP' 2011-11-05 22:00:00' 27
SYS_P285 TIMESTAMP' 2011-11-06 01:00:00' 28
SYS_P303 TIMESTAMP' 2011-11-06 02:00:00' 29
SYS_P287 TIMESTAMP' 2011-11-06 03:00:00' 30
SYS_P289 TIMESTAMP' 2011-11-06 04:00:00' 31
SYS_P307 TIMESTAMP' 2011-11-06 05:00:00' 32
SYS_P324 TIMESTAMP' 2011-11-06 06:00:00' 33
SYS_P310 TIMESTAMP' 2011-11-06 07:00:00' 34
SYS_P313 TIMESTAMP' 2011-11-06 08:00:00' 35
SYS_P342 TIMESTAMP' 2011-11-06 09:00:00' 36
SYS_P292 TIMESTAMP' 2011-11-06 10:00:00' 37
SYS_P315 TIMESTAMP' 2011-11-06 11:00:00' 38
SYS_P295 TIMESTAMP' 2011-11-06 12:00:00' 39
SYS_P298 TIMESTAMP' 2011-11-06 13:00:00' 40
SYS_P361 TIMESTAMP' 2011-11-06 14:00:00' 41
SYS_P363 TIMESTAMP' 2011-11-06 15:00:00' 42
SYS_P365 TIMESTAMP' 2011-11-06 16:00:00' 43
SYS_P366 TIMESTAMP' 2011-11-06 17:00:00' 44
SYS_P368 TIMESTAMP' 2011-11-06 18:00:00' 45
SYS_P371 TIMESTAMP' 2011-11-06 19:00:00' 46
SYS_P373 TIMESTAMP' 2011-11-06 20:00:00' 47
SYS_P375 TIMESTAMP' 2011-11-06 21:00:00' 48
SYS_P377 TIMESTAMP' 2011-11-06 22:00:00' 49
SYS_P379 TIMESTAMP' 2011-11-06 23:00:00' 50
SYS_P501 TIMESTAMP' 2011-11-07 00:00:00' 51
SYS_P504 TIMESTAMP' 2011-11-07 01:00:00' 52
SYS_P505 TIMESTAMP' 2011-11-07 02:00:00' 53
SYS_P507 TIMESTAMP' 2011-11-07 03:00:00' 54
SYS_P510 TIMESTAMP' 2011-11-07 04:00:00' 55
SYS_P512 TIMESTAMP' 2011-11-07 05:00:00' 56
SYS_P514 TIMESTAMP' 2011-11-07 06:00:00' 57
SYS_P515 TIMESTAMP' 2011-11-07 07:00:00' 58
SYS_P517 TIMESTAMP' 2011-11-07 08:00:00' 59
SYS_P520 TIMESTAMP' 2011-11-07 09:00:00' 60
SYS_P582 TIMESTAMP' 2011-11-07 10:00:00' 61
SYS_P622 TIMESTAMP' 2011-11-07 11:00:00' 62
SYS_P604 TIMESTAMP' 2011-11-07 12:00:00' 63
SYS_P642 TIMESTAMP' 2011-11-07 13:00:00' 64
SYS_P682 TIMESTAMP' 2011-11-07 14:00:00' 65
SYS_P684 TIMESTAMP' 2011-11-07 15:00:00' 66
SYS_P686 TIMESTAMP' 2011-11-07 16:00:00' 67
SYS_P688 TIMESTAMP' 2011-11-07 17:00:00' 68
SYS_P690 TIMESTAMP' 2011-11-07 18:00:00' 69

69 rows selected.

Here the partition names are not in order, so i am not able to figure out, to do the syntax to drop the partitions. Please let me know, how to drop the older partitions.

Also i have one procedure and got it from online to drop the older partitions, but it won't specified any time frame in that script.

create or replace procedure drop_partitions
(
p_schema_owner varchar2,
p_table_name varchar2,
p_partition_name varchar2,
p_partition_ts timestamp
)
is
l_current_partition_ts timestamp;
begin

--DEAL WITH PARTITION'S BY DATE
if p_partition_ts is not null
then

for all_parts in
(
select
high_value,
partition_name,
partition_position
from
user_tab_partitions
where
table_owner = p_schema_owner
and
table_name = p_table_name
order by
partition_position asc
)
loop

execute immediate 'select ' || all_parts.high_value || ' from dual' into l_current_partition_ts;

if l_current_partition_ts <= p_partition_ts
then
execute immediate 'alter table ' || p_schema_owner || '.' || p_table_name || ' drop partition ' || all_parts.partition_name || ;
end if;

end loop;

end if;

end;
/

Please let me know, what are the changes in need to make.

Thanks in advance.