Best Of
Re: How can I get all 9 digits of precision in systimestamp?
I guess that was done with the idea of "sub order by some arbitrary value", but then you may just sub order on some other column value instead as well.
Mr. C
Re: How can I get all 9 digits of precision in systimestamp?
Never order by rowid. ROWID will change by doing an export/import of data. Or by creating a second table which is a copy of the first one.
Re: How can I get all 9 digits of precision in systimestamp?
It's actually one billion, as 6 digits = 999999 and 9 digits is 999999999 … :)
But I guess that it's not that there are 2 inserts for every millionth of a second ( or even billion, doesn't matter as both are huge), I think the case may be that "it can happen" that there are 2 records in the same millionth/billionth of a second. That is something else. Maybe there aren't even a lot of records, but "something" is making them collide for the records that do exist. You never know, maybe this is coding to go to Mars, you don't know.
Mr. C
Re: How can I get all 9 digits of precision in systimestamp?
Hello Lisa,
if you have several rows inserted during the same millionth or a second, is the « real order » so important ? Does it even have a sense?(maybe yes, for example if you work in a nuclear experiments center)
If yes you have to fix the « issue » but if no, no need for a sequence that might impose too much extra work if order must be guaranteed; I would order by the timestamp(6)+rowid.
BTW, more than 1 million inserts in 1 second? I hope that it doesn’t happen too often, or our beloved Oracle is maybe not what you need…
Best regards,
Bruno Vroman
Re: How can I get all 9 digits of precision in systimestamp?
Didn't know Linux could go that deep, but then indeed it may be an Oracle issue getting that info from the OS.
Mr. C
Re: How can I get all 9 digits of precision in systimestamp?
You can see the nano-seconds on linux with the following command:
date +%T+%N
On our linux systems we see in OS 9 digits but in de oracle database just 6 digits. Looks like an oracle issue.
But like @Mr. C says use a sequence is a much better way to keep the order.
[oracle@ ~]$ date +%T+%N
11:00:30+627382024
SQL> SELECT SYSTIMESTAMP(9) FROM DUAL;
SYSTIMESTAMP(9)
11-JUL-25 11.01.11.855980000 AM +02:00
Re: How can I get all 9 digits of precision in systimestamp?
Maybe the system can't provide that much detail, as what we are talking about is 1 millionth of a second. I'm not sure what the timespecs are of regular Linux/Windows machines, but there is a limit somewhere and I think you found it. I've never needed to go less precise than a second ever, but your mileage may vary.
But, if the order is that important, and you need to be able to track the order, why don't you use a sequence instead ? This is what they are built for, by the way.
Mr. C
Re: Union Bargaining Unit Service Date for Leave Accrual
Hi Stella,
There is no blanket way to do this for the entire union group, but you could enter that date as an override on the Benefits Service Date (Workforce Administration > Job Information > Job Data > Employment Data link) for each affected employee if you don't have any other benefits for these employees that are dependent on Service where a different date would be needed.
Another option would be to consider Absence Management which provides greater flexibility in configuring leave entitlements.
Other customers who have had similar requirements are encouraged to share how they were able to meet that need in either Base Benefits Leave Accruals or Absence Management.
Regards,
Joyce
