A useful starting point would be the partition maintenance job, so we can see
both the step that is failing and the error text.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lok P
Sent: Thursday, January 13, 2022 5:02 AM
To: Pap
Cc: Oracle L
Subject: Re: Partition maintenance failure
Thank you Pap.
Actually that is what our initial thought was , but the team says the column is
created as a timestamp from when it was created. Could it depend on the type
data which is inserted into the table? Or any other way we can verify the cause
here?
On Thu, Jan 13, 2022 at 3:25 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
I created the table as per your exact DDL on a 11.2 instance and its showing
high_value as 'Timestamp' only. So it must be that someone changed the datatype
from date to timestamp for this partition column/key. So earlier your partition
maintenance job was working fine as it was DATE column and the high_value was
also DATE but now that it's changed to timestamp , the same statements (which
must be to see/retrieve the max date/time from the high_value column) was
looking for a string like 'TO_DATE' is not working anymore.
On Thu, Jan 13, 2022 at 2:20 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Hello , We have a third party application in which a table was created in the
past using DDL as below. And a partition maintenance jobs logic which was
referring to the "high_value" column of the dba_tab_partition and it was having
"to_date" in it. But we got to know , they keep on failing since past months
and the high_value is now showing "timestamp" in it. Data type of the partition
key column has not been changed as confirmed by the team. Can it be because of
the 19C migration?
Actually this database was on 11.2.0.4 and in the recent past it's been moved
to 19C. Not able to test the behavior of the column high_value because of the
absence of 11.2 dev instances. So wanted to check if anybody else encountered
such an issue while migrating or is it expected to change between 11.2 and 19C?
CREATE TABLE TAB1(
ID NUMBER(13,0) NOT NULL ,
PART_DATE TIMESTAMP NOT NULL ,
Col1 TIMESTAMP NOT NULL ,
)
PARTITION BY RANGE (PART_DATE)
(PARTITION MONTH_202008 VALUES LESS THAN (TO_DATE(' 2020-09-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION MONTH_202009 VALUES LESS THAN (TO_DATE(' 2020-10-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION MONTH_202012 VALUES LESS THAN (TO_DATE(' 2021-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
