Aw: SCN_TO_TIMESTAMP(ORA_ROWSCN)

Thanks Andy.

> What are you trying to do? Some sort of what’s-changed-in-my-table query?

It is a piece of failing comedy code was written by someone years back. The code is used for sorting trade blotter information into „rough“ chronological order as the database table then lacked trade datetime information. The developer converted the SCN to a timestamp and was ordering by that.

The trade blotter information needed to be generated the morning following the prior days trading. With the extensive DB upgrades, and blotter information was retrospectively generated after some lapsed days and clearly the SCN was stale for SCN_TO_TIMESTAMP usage (thanks for the documentation link and the ~120h reference/I was now aware).

I am just recounting the now known facts as you specifically asked ...... just when you thought you’d heard it all.

Mike

http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

> Am 17.02.2018 um 00:28 schrieb Andy Sayer <andysayer_at_gmail.com>:
> 
> Hi Mike,
> 
> If the SCN was from longer than a certain amount of time ago (at least 120 hours but after that it depends on undo retention https://docs.oracle.com/cd/E18283_01/server.112/e17118/functions161.htm <https://docs.oracle.com/cd/E18283_01/server.112/e17118/functions161.htm>) then Oracle will not be able to work out the timestamp the SCN was generated.
> 
> If you’re trying to run scn_to_timestamp for every ora_rowscn in a table, you’re going to have to be very lucky to not see that error - or very unlucky as it means every block/row* in your table has been updated in the last ~120 hours.
> 
> What are you trying to do? Some sort of what’s-changed-in-my-table query? Can you just handle the error and treat the block/row the same way you’d treat it if you found the date to be last week?
> 
> Hope this helps,
> Andrew
> 
> *If row dependencies are enabled
> 
> On Fri, 16 Feb 2018 at 19:34, Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk <mailto:woodwardinformatics_at_strychnine.co.uk>> wrote:
> Thanks for this Martin. I am in unfamiliar territory where the known details are „the DBA´s & infrastructure guys did something last weekend“, they only respond from offshore to each ticket or ticket response using a 48h SLA, there are some weird errors being observed in production systems at the moment, and I am grasping at straws. Please no-one ask me show the comedy code that uses SCN_TO_TIMESTAMP(...)  :-(
> 
> Last weekend there was some DB upgrade/migration/restructuring from 11.2 to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, HPUX too. The details are scarce, but certainly not inconsistent with 
> 
> > With transportable tablespaces you can get blocks from other DBs with much higher SCNs.
> 
> Regards
> 
> Mike
> 
> 

> 

--
http://www.freelists.org/webpage/oracle-l