Demo of ora_rowscn for CDC

oracle-rowscn-demo-full.sql

set echo off time on timing off linesize 9999 feedback off heading off

drop table t;

/* Create Table wiht ROWDEPENDENCIES enabled */
CREATE TABLE t
(
    x,
    y
)
ROWDEPENDENCIES
AS
    SELECT ROWNUM, substr(table_name,1,10)
      FROM all_tables
     WHERE ROWNUM < 100;

-- ALTER TABLE t ADD when_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

var max_row_scn number;

SELECT 'Resetting max scn ...' STATUS_MSG FROM DUAL;
BEGIN	
	SELECT MAX(ORA_ROWSCN) max_row INTO :max_row_scn FROM T;
END;
/

print max_row_scn

SELECT 'Updating 1 record in table ...' STATUS_MSG FROM DUAL;
UPDATE t
   SET y = 'DUAL123'
 WHERE x = 1;

COMMIT;

SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL;
SELECT t.*
       -- dbms_rowid.rowid_block_number (ROWID) blockno,
       -- SCN_TO_TIMESTAMP (t.ORA_ROWSCN),
       -- t.ORA_ROWSCN
  FROM t
  WHERE t.ORA_ROWSCN > :max_row_scn;

SELECT 'Resetting max scn ...' STATUS_MSG FROM DUAL;
BEGIN	
	SELECT MAX(ORA_ROWSCN) max_row INTO :max_row_scn FROM T;
END;
/

print max_row_scn

SELECT 'Inserting 3 rows into table...' STATUS_MSG FROM DUAL;
INSERT INTO T (x,y) VALUES(-1,'SOMENEWENTRY');
INSERT INTO T (x,y) VALUES(-2,'SOMENEWENTRY2');
INSERT INTO T (x,y) VALUES(-3,'SOMENEWENTRY3');
COMMIT;

SELECT 'Fetching changed data in table ...' STATUS_MSG FROM DUAL;
SELECT t.*
       -- dbms_rowid.rowid_block_number (ROWID) blockno,
       -- SCN_TO_TIMESTAMP (t.ORA_ROWSCN),
       -- t.ORA_ROWSCN
  FROM t
  WHERE t.ORA_ROWSCN > :max_row_scn;