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@xxxxxxxxxxxxxxxx> 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 withmuch higher SCNs.
Regards
Mike
Am 16.02.2018 um 20:18 schrieb Martin Berger <martin.a.berger@xxxxxxxxx>:
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@xxxxxxxxxxxxxxxx>:
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
--
//www.freelists.org/webpage/oracle-l
