Re: SCN_TO_TIMESTAMP(ORA_ROWSCN)

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) 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> 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
>
>
> Am 16.02.2018 um 20:18 schrieb Martin Berger <martin.a.berger_at_gmail.com>:
>
> An untested guess:
> With transportable tablespaces you can get blocks from other DBs with much
> higher SCNs.
> I'm sure they are confusing the new DB.
>
> Your environment might have a different reason. I'd risk a blockdump on an
> affected block and check for interesting traces ...
>
> Martin
>
>
> Am 16.02.2018 18:39 schrieb "Michael D O'Shea/Woodward Informatics Ltd" <
> woodwardinformatics_at_strychnine.co.uk>:
>
>> Hi everyone, I am just asking ..... how can this happen
>>
>>
>>
>> select SCN_TO_TIMESTAMP(ORA_ROWSCN)
>> from bigTable
>>
>>
>>
>> ORA-08181: Angegebene Zahl ist keine gültige SCN
>> ORA-06512: in "SYS.SCN_TO_TIMESTAMP", Zeile 1
>> 08181. 00000 - "specified number is not a valid system change number"
>> *Cause: supplied scn was beyond the bounds of a valid scn.
>> *Action: use a valid scn.
>>
>>
>>
>>
>> Mike
>> http://www.strychnine.co.uk
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

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