Re: oracle timestamp internal storage and precision
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 19 Dec 2012 13:43:02 -0700
Jed,
Whether you're referring to the SCN (for internal timestamping within
the RDBMS engine itself) or DATE/TIMESTAMP data types, one has to
question it.
For example, in order for Oracle to "sacrifice" precision over time by
updating data values, there has to be a process to do so, and that
process will generate redo, undo, and consume CPU and memory. How would
it handle READ ONLY tablespaces? How would it affect or be affected by
database triggers? What about audit trails? Certainly changing the
precision of data values should be considered an auditable event. How
would this background process deal those reasonable restrictions on
behind-the-scenes scheduled updates?
Upshot: I don't think there is any such mechanism built in to the
RDBMS, based on the enormous impact such functionality would create.
In trying to parse your original question, I suspect you had heard about
SCNs, which are stored as 6-byte numerics comprised of a "base" (4
bytes) and "wrap" (2 bytes). Be very clear -- the SCN is not a numeric
representation of time as with UNIX (i.e. number of seconds since 01-Jan
1970, etc), but is instead simply a sequence number, started when the
database is created. Riyaj Shamsudeen posted an excellent article about
SCNs on his blog at
"http://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/";.
But there can be no "sacrificing" of precision with the SCN for several
reasons, not the least of which is how can one alter the precision of an
integer, no matter how large, anyway? Integers only have scale, no
precision.
But what you described in the original post sounds vaguely like a
distorted impression of the mechanism of SCN "base" and "wrap", if you
kind of squint at it from a distance on a foggy day, perhaps? :-)
Hope this helps...
Thanks!
-Tim
On 12/19/2012 9:45 AM, Walker, Jed S wrote:
> Thanks Jared. I'm still curious if anyone knows the answer to my question?
> Does Oracle's internal format sacrifice precision over time to avoid running
> out of dates?
>
> From: Jared Still [mailto:jkstill@xxxxxxxxx]
> Sent: Tuesday, December 18, 2012 9:23 PM
> To: Walker, Jed S
> Cc: oracle-l
> Subject: Re: oracle timestamp internal storage and precision
>
>
> On Tue, Dec 18, 2012 at 4:49 PM, Walker, Jed S
> <Jed_Walker@xxxxxxxxxxxxxxxxx<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>> wrote:
> I could swear that a while back I read or saw a presentation from someone
> where they discussed how Oracle stores the internal timestamp. They said it
> was stored as a number and that in order to not expire to early (like UNIX
> time) the storage would, over time, sacrifice sub-second precision to extend
> the date range. I tried searching for this, but have found nothing. I wonder
> if I'm remembering wrong, or if maybe it just isn't something that has been
> published.
> Do any of the Oracle internal experts out there know?
>
>
> I was going to point you to an article I wrote some time ago on oracle dates.
>
> The website it used to appear on has been deprecated unfortunately.
>
> Following is the location of a zip file that contains an article on oracle
> dates, and oracle date internals.
> Code is included.
>
> http://jaredstill.com/downloads/oracle_dates.zip
>
> One interesting thing you will learn is the difference between to_date,
> sysdate and dates stored in a table.
>
> An exercise for the reader - see if the those differences remain the same in
> 11g.
> Please report back to the list if you do. :)
>
> There are a couple things not covered in the article:
> * timestamps
> * trace files
>
> It is possible to parse dates out of trace files (I've used that)
> While it is not something you would often need to do (maybe never)
> it can be useful in some instances.
>
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
