I am inserting a row into a table, and seeing the ora_rowscn
change for all the rows in the table.

1. Is this because all the (small) rows are in the same
block, and the SCN is tied to the block?

2. Is there a better way of mapping the change time for
a row, besided adding a timestamp column and updating
that column in a trigger?

Many TIA!
Mark


desc a;
select ora_rowscn, a.* from a;
insert into a values(7);
commit;
select ora_rowscn, a.* from a;

Name Null Type
---- ---- ----
A NUMBER(1)

ORA_ROWSCN A
---------------------- ----------------------
14032868105 1
14032868105 2

ORA_ROWSCN A
---------------------- ----------------------
14032868107 7
14032868107 1
14032868107 2

--
Mark Harrison
Pixar Animation Studios

On 29 Jul 2009 23:23:01 GMT, mh (AT) pixar (DOT) com wrote:

Obviously the SCN is not tied to a block.
The above output is not conclusive it is, as you don't post the -old
style rowid- of the affected rows. When the table has row movement
enabled, the rowid is not guaranteed to stay the same anyway.

2 You are aware ora_rowscan does not provide a permanent timestamp?
So the answer to your question is: the only *guaranteed* method is a
timestamp.

--
Sybrand Bakker
Senior Oracle DBA

On Jul 29, 6:23*pm, m... (AT) pixar (DOT) com wrote:
From the documentation:

"For each row, ORA_ROWSCN returns the conservative upper bound system
change number (SCN) of the most recent change to the row. This
pseudocolumn is useful for determining approximately when a row was
last updated. It is not absolutely precise, because Oracle tracks SCNs
by transaction committed for the block in which the row resides."

http://download.oracle.com/docs/cd/B....htm#sthref833

Since all of the rows are in the same block the same ORA_ROWSCN is
returned. Let's set up an example where that's not the case:

SQL>
SQL> --
SQL> -- Create non-partitioned copy of emp
SQL> --
SQL>
SQL> create table emp_l
2 as select *
3 from emp;

Table created.

SQL>
SQL> --
SQL> -- Let's add more data
SQL> --
SQL>
SQL> DECLARE
2 rindex BINARY_INTEGER;
3 slno BINARY_INTEGER;
4 sofar NUMBER(9,2);
5 target BINARY_INTEGER;
6 totwork NUMBER := 5000;
7 BEGIN
8 rindex := dbms_application_info.set_session_longops_nohint;
9
10 SELECT object_id
11 INTO target
12 FROM all_objects
13 WHERE object_name = 'EMP_L';
14
15 FOR i IN 1 .. totwork
16 LOOP
17 sofar := i*14;
18 dbms_application_info.set_session_longops(rindex, slno,
19 'Partition', target, 0, sofar, 70000, 'Completed Inserts',
'Rows');
20
21 insert into emp_l select
22 EMPNO,
23 ENAME,
24 JOB,
25 MGR,
26 HIREDATE+i/24,
27 SAL,
28 COMM,
29 DEPTNO
30 From emp;
31
32 END LOOP;
33 COMMIT;
34 END;
35 /

PL/SQL procedure successfully completed.

SQL>
SQL> create table emp_t
2 as
3 select *
4 from emp_l
5 order by job;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null,
tabname=>'EMP_T', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check ora_rowscn for emp_t
SQL> --
SQL> select ora_rowscn, empno, ename, sal, comm, job, hiredate
2 from emp_t;

ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 01:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 01:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 02:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 02:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 03:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 03:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 04:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 04:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 05:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 05:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 06:00:00

....
ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634287 7902 FORD
3000 ANALYST 04-DEC-1981 18:00:00
10450060634287 7788 SCOTT
3000 ANALYST 10-DEC-1982 19:00:00
10450060634287 7902 FORD
3000 ANALYST 04-DEC-1981 19:00:00
10450060634287 7369 SMITH
800 CLERK 17-DEC-1980 00:00:00
10450060634287 7876 ADAMS
1100 CLERK 12-JAN-1983 00:00:00
10450060634287 7900 JAMES
950 CLERK 03-DEC-1981 00:00:00
10450060634287 7934 MILLER
1300 CLERK 23-JAN-1982 00:00:00
10450060634287 7369 SMITH
800 CLERK 17-DEC-1980 01:00:00
10450060634287 7876 ADAMS
1100 CLERK 12-JAN-1983 01:00:00
10450060634287 7900 JAMES
950 CLERK 03-DEC-1981 01:00:00
10450060634287 7934 MILLER
1300 CLERK 23-JAN-1982 01:00:00

....
ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634287 7900 JAMES
950 CLERK 08-JUN-1982 07:00:00
10450060634287 7934 MILLER
1300 CLERK 29-JUL-1982 07:00:00
10450060634287 7369 SMITH
800 CLERK 22-JUN-1981 08:00:00
10450060634287 7876 ADAMS
1100 CLERK 18-JUL-1983 08:00:00
10450060634287 7900 JAMES
950 CLERK 08-JUN-1982 08:00:00
10450060634287 7934 MILLER
1300 CLERK 29-JUL-1982 08:00:00
10450060634287 7369 SMITH
800 CLERK 22-JUN-1981 09:00:00
10450060634287 7876 ADAMS
1100 CLERK 18-JUL-1983 09:00:00
10450060634287 7900 JAMES
950 CLERK 08-JUN-1982 09:00:00
10450060634287 7566 JONES
2975 MANAGER 06-OCT-1981 01:00:00
10450060634287 7698 BLAKE
2850 MANAGER 04-NOV-1981 01:00:00

....

70014 rows selected.

SQL>
SQL> --
SQL> -- Update some rows
SQL> --
SQL> update emp_t
2 set sal = sal+100 where job = 'CLERK';

20004 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Check ora_rowscn for emp_t
SQL> --
SQL> select ora_rowscn, empno, ename, sal, comm, job, hiredate
2 from emp_t;

ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 01:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 01:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 02:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 02:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 03:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 03:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 04:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 04:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 05:00:00
10450060634287 7902 FORD
3000 ANALYST 08-JUN-1982 05:00:00
10450060634287 7788 SCOTT
3000 ANALYST 14-JUN-1983 06:00:00

....
ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634642 7902 FORD
3000 ANALYST 04-DEC-1981 18:00:00
10450060634642 7788 SCOTT
3000 ANALYST 10-DEC-1982 19:00:00
10450060634642 7902 FORD
3000 ANALYST 04-DEC-1981 19:00:00
10450060634642 7369 SMITH
900 CLERK 17-DEC-1980 00:00:00
10450060634642 7876 ADAMS
1200 CLERK 12-JAN-1983 00:00:00
10450060634642 7900 JAMES
1050 CLERK 03-DEC-1981 00:00:00
10450060634642 7934 MILLER
1400 CLERK 23-JAN-1982 00:00:00
10450060634642 7369 SMITH
900 CLERK 17-DEC-1980 01:00:00
10450060634642 7876 ADAMS
1200 CLERK 12-JAN-1983 01:00:00
10450060634642 7900 JAMES
1050 CLERK 03-DEC-1981 01:00:00
10450060634642 7934 MILLER
1400 CLERK 23-JAN-1982 01:00:00

....
ORA_ROWSCN EMPNO ENAME SAL
COMM JOB HIREDATE
--------------- --------------- ---------- ---------------
--------------- --------- --------------------
10450060634642 7698 BLAKE
2850 MANAGER 25-NOV-1981 04:00:00
10450060634642 7782 CLARK
2450 MANAGER 03-JAN-1982 04:00:00
10450060634642 7566 JONES
2975 MANAGER 27-OCT-1981 05:00:00
10450060634642 7698 BLAKE
2850 MANAGER 25-NOV-1981 05:00:00
10450060634642 7782 CLARK
2450 MANAGER 03-JAN-1982 05:00:00
10450060634287 7566 JONES
2975 MANAGER 27-OCT-1981 06:00:00
10450060634287 7698 BLAKE
2850 MANAGER 25-NOV-1981 06:00:00
10450060634287 7782 CLARK
2450 MANAGER 03-JAN-1982 06:00:00
10450060634287 7566 JONES
2975 MANAGER 27-OCT-1981 07:00:00
10450060634287 7698 BLAKE
2850 MANAGER 25-NOV-1981 07:00:00
10450060634287 7782 CLARK
2450 MANAGER 03-JAN-1982 07:00:00

....

70014 rows selected.

Notice the ora_rowscn changes for the blocks where records with the
job 'CLERK' are found, noticing also that this includes some 'ANALYST'
and 'MANAGER' records. When the data retrieved is no longer in
affected blocks the ora_rowscn changes back to its original value,
which supports the documentation quoted above.


David Fitzjarrell

ddf <oratune (AT) msn (DOT) com> wrote:
This clears it up for me... great example.

Thanks!
Mark

--
Mark Harrison
Pixar Animation Studios