While trying to run pre_upgrade_status.sql script for DB upgrade (10.2.0.4 to 220.127.116.11), we got following error
@pre_upgrade_status.sql ERROR at line 1: ORA-01873: the leading precision of the interval is too small ORA-06512: at line 8
On debugging further , found out that it was due to limit imposed on "INTERVAL DAY TO SECOND" datatype. By default precision is 2 i.e. interval day to second is equivalent to interval day(2) to second(2). As per Oracle this is not bug and you should use internal datatype DSINTERVAL_UNCONSTRAINED
I couldn't get any hit on web for this as this error will only be encountered if the AWR retention is set to 100 or more days. This is easily reproducible
--Set retention to >100 days execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention =>145440) --execute following code declare sap_awr_small BOOLEAN := FALSE; sap_awr_retention_days NUMBER; sap_awr_retention INTERVAL DAY TO SECOND; BEGIN sap_awr_small := FALSE; SELECT RETENTION INTO sap_awr_retention from DBA_HIST_WR_CONTROL WHERE dbid = (select dbid from v$database); -- dbms_output.put_line(sap_awr_retention); sap_awr_retention_days := extract (day from sap_awr_retention); -- dbms_output.put_line(sap_awr_retention_days); if (sap_awr_retention_days < 42) THEN sap_awr_small := TRUE; END IF; end; / ERROR at line 1: ORA-01873: the leading precision of the interval is too small ORA-06512: at line 8
Easy workaround is to set retention to lower value say 99 days and revert it post upgrade
execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention =>142560)
In case you are facing this error in your application code, then you can Refer MyOracle Support article ORA-1873 : When Passing Datetime Interval with Precision > 2 in a Parameter (Doc ID 394260.1) for modifying your code to use custom type.