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')))