RE: oracle timestamp internal storage and precision
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2012 22:37:46 +0000
Thanks Tim, that does help. It could be I'm remembering something I heard about
SCNs, I'm not sure, which is why I was asking the question.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Wednesday, December 19, 2012 1:43 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: oracle timestamp internal storage and precision
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
