Month: July 2020

by

DBMS_JOB interval defined as past time.

Once create the DBA_JOBS from DBMS_JOB, the next run would be base on the results from formula in interval field . What happen if the result from “interval” is fall in past ? Would the job stopped forever or tune to invalid ?

DBMS_JOB is an user submit job, when the job be triggered to run by backend job processes, it would set the next run time by job interval definition. This is why when job failed and suspend, even DBA resume the job back, without manual successfully runs the job to set the next running time, the job would running in future.

Bankend CJQn will pick the next run time after last CJQn to todate time, so if the job didn’t ran to reset next run time in future, then it would run in future until manual trigger the job to reset next run time again.

by

Interval partition on date type and between clause in where

When config the partition table for interval partitioning (auto allocate the new partition), if the partition key on the date type and query runs on the partition key, the interesting thing would be happened with date type column with between operation on max date value (31-DEC-9999), solution for this always only use MAX date value to 31-DEC-9998 for yearly interval.

TT1

CREATE TABLE “IDSAPPDBA”.”TT1″
( “ID” NUMBER,
“DESCRIPTION” VARCHAR2(50 BYTE),
“CREATED_DATE” DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
PARTITION BY RANGE (“CREATED_DATE”) INTERVAL (NUMTOYMINTERVAL(12,’MONTH’))

Partitions list :

PART_01 21-DEC-2016 22:01:43 0 0 TO_DATE(‘ 2005-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P34333 21-DEC-2016 22:01:43 1 250 1 TO_DATE(‘ 2010-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P34334 21-DEC-2016 22:01:44 1 250 1 TO_DATE(‘ 2011-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P34335 21-DEC-2016 22:01:43 1 250 1 TO_DATE(‘ 9999-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P34336 21-DEC-2016 22:01:43 1 250 1 TO_DATE(‘ 9991-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
SYS_P34337 21-DEC-2016 22:01:43 3 250 3 TO_DATE(‘ 9912-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)\

select * from tt1 where CREATED_DATE < ’16-DEC-9999′;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 – “(full) year must be between -4713 and +9999, and not be 0”
*Cause: Illegal year entered
*Action: Input year in the specified range
Error at Line: 1 Column: 40

select * from tt1 where CREATED_DATE < ’16-DEC-9998′;

ID DESCRIPTION CREATED_DATE
9 t1 01-DEC-2009 00:00:00
9 t1 01-DEC-2010 00:00:00
9 t1 01-DEC-9911 00:00:00
9 t1 01-DEC-9900 00:00:00
9 t1 01-DEC-8888 00:00:00
9 t1 01-DEC-9990 00:00:00
9 t1 01-DEC-9998 00:00:00

For annual base partition, the “9999” is not valid, but if change to daily partition, this problem whould happened.

alter table tt1 set interval (numtodsinterval(1,’DAY’));

select * from tt1 where CREATED_DATE <= ’31-DEC-9999′;

ID DESCRIPTION CREATED_DATE
9 t1 01-DEC-2009 00:00:00
9 t1 01-DEC-2010 00:00:00
9 t1 01-DEC-9911 00:00:00
9 t1 01-DEC-9900 00:00:00
9 t1 01-DEC-8888 00:00:00
9 t1 01-DEC-9990 00:00:00
9 t1 01-DEC-9998 00:00:00