Database DataWarehousing (MOSC)

MOSC Banner

How to "enable" microseconds in SYSTIMESTAMP on Windows platform / extend fractional seconds ?

edited Nov 29, 2019 4:06AM in Database DataWarehousing (MOSC) 3 commentsAnswered

Hi all,

in context with  an     ORA-00001: Unique Constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST)  violation - which occures during   DBMS_STATS.GATHER_TABLE_STATS() on a table having a lot of empty partitions -  we realized   that  SYSTIMESTAMP returns only  milliseconds  -  the last 3 digits of the default-precision of 6 fractional seconds    are always 000:

for example:

select systimestamp from dual;

07/11/2019 19:46:42,093000 +01:00

We checked our Logging-table-entries  and  could find the same problem:  values of all  timestamp(6) -columns  contain  millisecond-resolution only ..

Also in Oracle's internal tables - as  SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY -    we could find the same / limited resolution:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center