Re: Partition maintenance failure

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_at_gmail.com> 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')))
>

--
http://www.freelists.org/webpage/oracle-l