Home » SQL & PL/SQL » Client Tools » ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad (Toad 10.5.1.3)
ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598336] Mon, 14 October 2013 01:23 Go to next message
rc3d
Messages: 133
Registered: September 2013
Senior Member
SELECT SCN_TO_TIMESTAMP (ORA_ROWSCN), ORIGINAL_NAME FROM USER_RECYCLEBIN


This pseudocolumn is useful for determining approximately when a row was last updated.

http://i.imgur.com/jSdjFV5.png

It seems not working in Toad. Only with SQL*Plus?
Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598337 is a reply to message #598336] Mon, 14 October 2013 01:32 Go to previous messageGo to next message
Littlefoot
Messages: 18821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not allowed to view images that are stored on internet services like the one you use; could you, please, attach the image to your messages in the future?

Anyway: ORA_ROWSCN can be used with tables only. USER_RECYCLEBIN is a view (so I suppose that you got ORA-00904, invalid identifier).
icon2.gif  Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598338 is a reply to message #598336] Mon, 14 October 2013 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you want to know when an object was dropped (purpose of recyclebin) you have the DROPTIME and DROPSCN columns.

[Updated on: Mon, 14 October 2013 01:39]

Report message to a moderator

Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598341 is a reply to message #598336] Mon, 14 October 2013 01:59 Go to previous messageGo to next message
rc3d
Messages: 133
Registered: September 2013
Senior Member
Thank you both. On table it works.

SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM ent_user where user_id = 'TZR5457';

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
14-OCT-13 03.39.38.000000000 AM



second question: howto see SCN_TO_TIMESTAMP(ORA_ROWSCN) on every row in table? It seems only to work when there is a WHERE clause.
Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598342 is a reply to message #598341] Mon, 14 October 2013 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works even with no WHERE clause.

Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598343 is a reply to message #598342] Mon, 14 October 2013 02:41 Go to previous messageGo to next message
rc3d
Messages: 133
Registered: September 2013
Senior Member
SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), user_id FROM ent_user;
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), user_id FROM ent_user
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


found the solution with Google:

Quote:

ORA-08181

If the value supplied to the SCN_TO_TIMESTAMP function is either not a System Change Number or is considered by the database to be an SCN that is too old then an error message similar to the one in the following example will be returned:


SQL> select min(SCN) min_scn from sys.smon_scn_time;

   MIN_SCN
----------
3391402569

SQL> select SCN_TO_TIMESTAMP(3391402569) FROM DUAL;

SCN_TO_TIMESTAMP(3391402569)
---------------------------------------------------------------------------
09-OCT-13 12.50.51.000000000 AM


that's a really short history Embarassed
so the SCN_TO_TIMESTAMP(ORA_ROWSCN) function is not magic, as I first tought.

is that correct: the SCN starts at 1?
Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598352 is a reply to message #598343] Mon, 14 October 2013 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is that correct: the SCN starts at 1?


Yes, it is correct.

Re: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad [message #598353 is a reply to message #598343] Mon, 14 October 2013 04:08 Go to previous message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
I'm not sure I agree with the documentation's statement that Quote:
This pseudocolumn is useful for determining approximately when a row was last updated.

Granted it then clarifies
Quote:
It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.
But it is that very clarification that makes the first statement completely invalid. We can easily have a number of rows that appear to have been changed very recently, that have in fact not been changed since they were first inserted many moons ago.
--Without going into detail on the structure of a rowid, in the exmple below, the block in which a row is located can be identified by characters 10 to 15. So we can see that we have rows located in two separate blocks.

SQL> select employee_id, ora_rowscn, rowid
  2  from employees
  3  where employee_id in (100, 101, 102, 103, 104, 198, 199, 200, 201);

EMPLOYEE_ID ORA_ROWSCN ROWID
----------- ---------- ------------------
        100    5069516 AAAR5kAAFAAAADNAAA
        101    5069516 AAAR5kAAFAAAADNAAB
        102    5069516 AAAR5kAAFAAAADNAAC
        103    5069516 AAAR5kAAFAAAADNAAD
        104    5069516 AAAR5kAAFAAAADNAAE
        198     948777 AAAR5kAAFAAAADOAAA
        199     948777 AAAR5kAAFAAAADOAAB
        200     948777 AAAR5kAAFAAAADOAAC
        201     948777 AAAR5kAAFAAAADOAAD

9 rows selected.

--Let's update just one row in the table.
SQL>
SQL> update employees
  2  set salary = salary
  3  where employee_id = 100;

1 row updated.

SQL>
SQL> commit;

Commit complete.
--Note the ora_rowscn changes for ALL of the rows in the block that holds the row that was updated.
SQL>
SQL> select employee_id, ora_rowscn, rowid
  2  from employees
  3  where employee_id in (100, 101, 102, 103, 104, 198, 199, 200, 201);

EMPLOYEE_ID ORA_ROWSCN ROWID
----------- ---------- ------------------
        100    5069623 AAAR5kAAFAAAADNAAA
        101    5069623 AAAR5kAAFAAAADNAAB
        102    5069623 AAAR5kAAFAAAADNAAC
        103    5069623 AAAR5kAAFAAAADNAAD
        104    5069623 AAAR5kAAFAAAADNAAE
        198     948777 AAAR5kAAFAAAADOAAA
        199     948777 AAAR5kAAFAAAADOAAB
        200     948777 AAAR5kAAFAAAADOAAC
        201     948777 AAAR5kAAFAAAADOAAD

9 rows selected.

--Let's update a row in the 'other' block.
SQL> update employees
  2  set salary = salary
  3  where employee_id = 201;

1 row updated.

SQL> commit;

Commit complete.
--Again, ALL rows change their ora_rowscn value.
SQL> select employee_id, ora_rowscn, rowid
  2  from employees
  3  where employee_id in (100, 101, 102, 103, 104, 198, 199, 200, 201);

EMPLOYEE_ID ORA_ROWSCN ROWID
----------- ---------- ------------------
        100    5069623 AAAR5kAAFAAAADNAAA
        101    5069623 AAAR5kAAFAAAADNAAB
        102    5069623 AAAR5kAAFAAAADNAAC
        103    5069623 AAAR5kAAFAAAADNAAD
        104    5069623 AAAR5kAAFAAAADNAAE
        198    5069639 AAAR5kAAFAAAADOAAA
        199    5069639 AAAR5kAAFAAAADOAAB
        200    5069639 AAAR5kAAFAAAADOAAC
        201    5069639 AAAR5kAAFAAAADOAAD

9 rows selected.
Previous Topic: Restricting User from login to DB
Next Topic: Unsupported Oracle database version
Goto Forum:
  


Current Time: Wed Apr 16 14:37:24 CDT 2014

Total time taken to generate the page: 0.14451 seconds