Hi,
I am using this code in order to automate a commands in DB:
begin
for i in 0..23 loop
dbms_output.put_line (
'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'||' PSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY')||' VALUES LESS THAN '||'((TO_DATE('''||to_char(sysdate,'DD')||to_char(sysdate,'MON')|| to_char(sysdate,'YYYY')||' ' ||to_char(mod(i+1,24),'fm00')|| ':00:00''' || ', ''DDMONYYYY HH24:MI:SS'')))' ||' LOGGING NOCOMPRESS TABLESPACE ' || 'TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY') || ' ' || 'STORAGE (BUFFER_POOL DEFAULT)');
end loop;
end;
/
Output is coming out to be:
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0001OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 01:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0001OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0102OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 02:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0102OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0203OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 03:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0203OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0304OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 04:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0304OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0405OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 05:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0405OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0506OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 06:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0506OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0607OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 07:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0607OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0708OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 08:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0708OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0809OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 09:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0809OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0910OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 10:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0910OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1011OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 11:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1011OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1112OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 12:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1112OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1213OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 13:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1213OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1314OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 14:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1314OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1415OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 15:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1415OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1516OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 16:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1516OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1617OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 17:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1617OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1718OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 18:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1718OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1819OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 19:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1819OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1920OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 20:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1920OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2021OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 21:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2021OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2122OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 22:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2122OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2223OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 23:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2223OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)
I require a small modification over here in the last time.
Last line
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)
should be
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('16OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)