Re: ora_rowscn changing for non-updated rows?
From: ddf <oratune_at_msn.com>
Date: Thu, 30 Jul 2009 09:19:00 -0700 (PDT)
Message-ID: <64078526-a13f-4238-a65b-8d04665fad50_at_r38g2000yqn.googlegroups.com>
On Jul 29, 6:23 pm, m..._at_pixar.com wrote:
> 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
2 as select *
3 from emp;
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
20
31
32 END LOOP;
33 COMMIT;
34 END;
35 /
--------------- --------------- ---------- ---------------
--------------- --------------- ---------- ---------------
--------------- --------------- ---------- ---------------
2 set sal = sal+100 where job = 'CLERK';
--------------- --------------- ---------- ---------------
--------------- --------------- ---------- ---------------
--------------- --------------- ---------- ---------------
Date: Thu, 30 Jul 2009 09:19:00 -0700 (PDT)
Message-ID: <64078526-a13f-4238-a65b-8d04665fad50_at_r38g2000yqn.googlegroups.com>
On Jul 29, 6:23 pm, m..._at_pixar.com wrote:
> 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
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/B28359_01/server.111/b28286/pseudocolumns007.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, hiredate2 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, hiredate2 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 Received on Thu Jul 30 2009 - 11:19:00 CDT