Does Oracle has similar datatype to SQL Server's RowVersion?
When you insert or update a row, the corresponding Version column(which is of type RowVersion) gets updated automatically.
Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.
You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read.
You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.
We are designing a data model with oracle and would like to use Version column to manage concurrency.
I would also like to know if there is better way in Oracle world.
The simple answer is No - but it's easy to create one yourself with a NUMBER column and a trigger to set/update it.
A simple example for Oracle 11gR2:
CREATE SEQUENCE global_rowversion_seq;
ALTER TABLE mytable1 ADD rowversion NUMBER;
ALTER TABLE mytable2 ADD rowversion NUMBER;
CREATE TRIGGER mytable1_biu
BEFORE INSERT OR UPDATE
ON mytable1
FOR EACH ROW
BEGIN
:NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable1_biu;
CREATE TRIGGER mytable2_biu
BEFORE INSERT OR UPDATE
ON mytable2
FOR EACH ROW
BEGIN
:NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable2_biu;
(If you're on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:
SELECT global_rowversion_seq.NEXTVAL
INTO :NEW.rowversion
FROM dual;
Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.
Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique - but if you are only interested in comparing changes to rows within a table, then this would be fine.
Another approach is to advance the counter for each row individually - this doesn't need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):
ALTER TABLE mytable ADD rowversion NUMBER;
CREATE TRIGGER mytable_biu
BEFORE INSERT OR UPDATE
ON mytable
FOR EACH ROW
BEGIN
:NEW.rowversion := NVL(:OLD.rowversion, 0) + 1;
END mytable_biu;
Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.