[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


What is a "versioned table"?

Posted on 2007-03-30
Medium Priority
Last Modified: 2013-12-19
Could someone tell what a versioned table is/does?  I just have never come across this in all my years around databases etc.  Is this something that MS SQL Server (where all my experiance comes from, big surprise eh?) does not offer and yet another reason why Oracle is so much more better gooderer than MS SQL?

Thnx for your help in advance...a
Question by:Atropa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 18826605
I think this is related to Oracle Workspace Manager

Oracle9i Workspace Manager is a new feature of Oracle9i that transparently and securely versions relational content in place with no changes to application SQL (DML), while permitting simultaneous read and write access to the same production data.

Subject:       Frequently Asked Questions for Oracle Workspace Manager
        Doc ID:       Note:156963.1

Subject:       What's the impact of Enabling Version Support?
        Doc ID:       Note:197188.1


Author Comment

ID: 18826662
Umm, thnx(?).  Remember I said I am a MS SQL type person so you lost me once you said "Versions relationsal content in place with no changes to application SQL"

So is another "copy" of the table made and the old one stored as a temp table so you can roll back changes at a latter date?  Is it like Begin / Commit Transaction on major steroids?  Is there ever a problem with not having the current "version" of a table?

What if two apps "version" a table at close to / at the same time?  How are your certain to get the current version?  Am I on the right track with these questions?

Accepted Solution

Tayger earned 1000 total points
ID: 18827125
What you probably heard of is "Flashback Version" available since Oracle 10.1: http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html

In general it allows the DBA/developer/user to kinda rollback the data status back to a certain date. Example

Record A at 12th of January looks like
Customer     Orders
-----------      ---------
J. Smith        3

Then J. Smith does one more order at 5th February. The record looks like:
Customer     Orders
-----------      ---------
J. Smith        4

Now The chief comes to you and asks: How many orders made John Smith till the end of 31th of January? Your answer might be: Erm, errr, hmmm, .... between 0 and 4?
What your chief wanted to hear is: 3
By versioning that table you store the information when and how a record in the table has changed.  I dont know any other DB than Oracle 10.1 that support this feature by default. Maybe some other do. If not  you have to version the table yourself als DBA or developer. There are now many different ways to do it, depending how you need it. The most simple (but not most perfomed) way is f.e. to copy every changed record into a similar looking table including a timestamp (when the record has changed). The difference between two records will show you then WHAT has changed.
Versioning is used a lot in DWH environment.

Hope I could help


Assisted Solution

ramumorla earned 1000 total points
ID: 18834717
Every time a change is made to a version-enabled table a new row is created to reflect the change,
with all new rows being stored in the same tablespace. Since only changed rows are stored,
the storage overhead is minimized. The topmost workspace is always used to create a new workspace,
irrespective of where it is in the workspace heirachy. A table can be version-enabled using:

EXEC DBMS_WM.EnableVersioning('SCOTT.EMP');

This will rename the SCOTT.EMP table to SCOTT.EMP_LT and create a view called SCOTT_EMP.
The view uses instead-of triggers to perform all operations against the version enabled table. This hides alot of the
versioning mechanism from the users.

The SCOTT.EMP_LT table has the following additional columns:

NEXTVER             VARCHAR2(500)
DELSTATUS           NUMBER(38)
LTLOCK              VARCHAR2(100)

These are used to store the current version, next version, delete status and lock status respectively.

An additional parameter HIST can be used to track changes to the table via a view called _HIST. The default is no history.
The VIEW_W_OVERWRITE parameter shows only the most recent change to a row.
The VIEW_NO_OVERWRITE parameter shows all modifications to a row.

Author Comment

ID: 18836808
Thank you both for the details.  That is an awesome feature for sure and I can definately see where the benefit comes from.  Thanks again...a

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question