Time based 'cut' of all data 3

[Thargy]

Folks,

My problem is this:-

I have a db in a known guaranteed condition, with known and guaranteed data.

By means of known, controlled scripts, data will be added to this database. What I want to do is find a systematic method of distinguishing between the data in the db before the scripted addition and after, so that I can subsequently extract just the added data.

I can't think of a good method for achieving this, because the data is distinguished by time, and no other known factors. Users may add data to all or none of the tables, I just have to be able to get it out afterwards.

Does anyone have suggestions for an approach/method which will let me achieve this?

Regards

T

Grinding away at things Oracular

 

[ChrisHunt]

What exactly do you mean by "data will be added to this database"? Are they adding new rows of data to the tables, or amending the rows already there?

Either way, the solution is likely to involve adding a timestamp column to each table, together with a database trigger which updates it whenever a row is inserted or updated. Something like this:

CREATE TABLE my_table
( col1         NUMBER,
  col2         NUMBER,
  col3         NUMBER,
  last_change  DATE)
/

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT OR UPDATE
ON my_table
FOR EACH ROW
BEGIN
   :NEW.last_change := SYSDATE;
END ;

That gives you a column you can check in your queries.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

Chris,

users may add and/or update rows.

I have considered the timestamp solution, I was also wondering about a database DML trigger to capture events.

T



Grinding away at things Oracular

 

[SantaMufasa]

I was also wondering about a database DML trigger to capture events.

Yes, Tharg, even without native time storage in your table, you can distingush the "new" rows with a database trigger that you enable following the initial cutoff of data entry. That trigger could store (either in the table itself or another tracking table FKed to the original table) indication of "subsequent-to-original" data entry/update.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Checkout this asktom thread which discusses flashback and SCN's. It may give you some ideas.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5968150395572

In order to understand recursion, you must first understand recursion.

 

[jimirvine]

Depending on how long after the dml has bee performed you want to see the previous values, you could have a look at versions queries:

very quick demo.

CREATE TABLE flash_demo (id NUMBER, lname VARCHAR2(30), salary NUMBER);
INSERT INTO flash_demo VALUES (1, 'JONES',2000);
INSERT INTO flash_demo VALUES (2, 'SMITH',3000);
INSERT INTO flash_demo VALUES (3, 'MARTIN',4000);
INSERT INTO flash_demo VALUES (4, 'DAVIS',200);
COMMIT;

UPDATE flash_demo SET lname = 'SMITH' WHERE id = 1;
COMMIT;

UPDATE flash_demo SET salary = 2500 WHERE id = 4;

INSERT INTO flash_demo VALUES (5, 'HAL',2001);

UPDATE flash_demo SET salary = 2300 WHERE id = 4;
COMMIT;

SELECT id, lname, salary, versions_starttime, versions_endtime, versions_startscn, versions_endscn, versions_xid, versions_operation
FROM flash_demo
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

SELECT id, lname, salary
FROM flash_demo
AS OF TIMESTAMP TO_TIMESTAMP('31-JAN-08 15.29.32','DD-MON-YY HH24:MI:SS');

 

[Thargy]

Gentlemen,

my thanks to you all. I will probably go with the SELECT ... AS OF TIMESTAMP solution. I thought that perhaps the big O might have some db level tool to do this in a trice, so I was asking in order to avoid re-inventing any wheels.

Regards

T

Grinding away at things Oracular

 

[jimirvine]

Remeber to set you db_flashback_retention_target appropriately