Versioned tables in a geodatabase in Oracle

Versioned tables in a geodatabase in Oracle
Release 9.3

Versioned tables in ArcCatalog and ArcMap

Versioned tables in an Oracle DBMS

• Version delta tables
When you register a feature dataset or stand-alone feature class as versioned, the delta tables—the adds and deletes tables—get created in the database. The delta tables record any changes—new, modified, or deleted records—made to a versioned table or feature class at each state of the database.
A<registration_id>
The a<registration_ID> table (the adds table) maintains information on each inserted or updated record (feature) in a versioned business table and is queried to identify which records have been added or modified at a particular database state. The registration_ID in the name of the adds table is the value in the registration_id field of the TABLE_REGISTRY table that corresponds to the versioned table. In the hydrant example above, the registration_id of the hydrants feature class in the TABLE_REGISTRY table is 161; therefore, the adds table for the hydrants feature class is a161.
The adds table contains all the same fields as the versioned business table being edited plus a State_ID.



Field name	Field type	Description
<all the fields from the versioned business table>		All the attributes of the new feature
SDE_STATE_ID	NUMBER(38)	ID of the state in which the new feature was added or updated Primary key NOT NULL

For example, if you add a hydrant to the hydrants feature class during a versioned edit session, a record will get added to the adds table for that new hydrant as shown below:

D<registration_id>
The D<registration_id> table (or deletes table) maintains information on all rows that are deleted or updated in a versioned table and is queried to identify which rows have been deleted or modified at a particular state. When a row is deleted, the record isn't physically removed: it is flagged as deleted and never returned in subsequent database queries.



Field name	Field type	Description
DELETED_AT	NUMBER(38)	The state from which the feature is being deleted Primary key NOT NULL
SDE_DELETES_ROW_ID	NUMBER(38)	The unique ID of the deleted or updated feature Primary key NOT NULL
SDE_STATE_ID	NUMBER(38)	ID of the state in the lineage for which the feature is deleted Primary key NOT NULL

To correctly represent each database version, the delta tables are queried, in conjunction with the VERSIONS and STATE_LINEAGES system tables, to identify which change was made at which database state. A version then returns a seamless view of the data that takes into account the original state of the data plus any changes.
• System tables
In addition to the delta tables, there are system tables that track versioned tables and edits. These are the STATES, STATE_LINEAGES, VERSIONS, and MVTABLES_MODIFIED tables.
To manage the edits made to the data, a versioned geodatabase maintains a collection of database states, or units of change to the database. A state represents a discrete snapshot of the database whenever a change is made: every edit operation creates a new database state. (An edit operation is any task or set of tasks [additions, deletions, or modifications] undertaken on features and rows.) All geodatabase versions reference one of these database states and evolve over time through a series of states.
All geodatabase states have the same schema and differ only in the number of rows that represent each modified table or feature class. To identify conflicts, which can occur when the same feature is edited in either the same or different versions, the version state lineages are compared for differences or row conflicts during version reconcile.
All the information relating to states is managed in the STATES table.
The VERSIONS and STATES tables are queried to identify which database state each version references.
States are maintained in a tree structure where the parent-child relationships can be derived from the state lineage. Information about the state lineage of each version is maintained in a separate table, STATES_LINEAGES. This table stores a multiple-entry index for traversing state parent-child relationships and is used for all version queries.
To return the correct view of a version, its state lineage is queried to identify all the states that recorded each change made to that version. From this list of states, the table rows that correctly represent the version can be determined. As the geodatabase is edited and the versions change over time, the state tree becomes more complex.
In conjunction with the STATES table, the MVTABLES_MODIFIED table maintains the list of all tables that have been modified at each state of the database.
Anytime a feature class or table is modified in a state, a new entry is created in the MVTABLES_MODIFIED table. When two versions are reconciled, the first step in the process is to identify the states these two versions reference—the current edit version's state and the target version's state. From these states, a common ancestor state is identified by tracing back through the state lineage of these two versions.
The MVTABLES_MODIFIED table is then queried to identify all the tables that were modified between the common ancestor state and the target version state. From this list of modified tables, a second list of tables common to both state lineages is generated. For all common tables in this second list, a number of version difference queries are executed—INSERT, UPDATE, DELETE, UPDATE_UPDATE, UPDATE_DELETE.
The system tables involved in a versioned feature class can be viewed in the PDF file that opens when you click the following link:
Oracle versioned feature class tables

Versioned tables in an XML document

<Versioned>true</Versioned>

You can use geodatabase replication to create copies of data across two or more versioned geodatabases so that changes to the data may be synchronized. A synchronization involves one replica sending data changes and the relative replica receiving changes.

Before you create a two-way or one-way replica, you must add a global ID column to the datasets to be replicated. This gives the rows in the dataset a unique value that will remain constant across geodatabases. (For details on getting a dataset ready for replication, consult Preparing data for replication.)

After changes have been made in one of the replicas, you can synchronize the geodatabases, bringing the changes made in one geodatabase into its relative geodatabase. When a geodatabase is synchronized with its relative geodatabase, a table is created in the user's schema of the replica geodatabase (the one that is sending the changes to the relative geodatabase) to track the lineages of altered datasets.

Replica tables in ArcCatalog and ArcMap

You won't see the table used for synchronization in ArcCatalog or ArcMap; it is used behind the scenes and only during synchronization.

You can, however, identify if a geodatabase is being used as a replica geodatabase by checking its properties in ArcCatalog. Right-click the geodatabase and click Properties. On the General tab, there is a Distributed Geodatabase Status section. If the geodatabase has been replicated or replicated to, the status states, This is a replica geodatabase.

In ArcMap, you know when an MXD file contains one or more layers that have been replicated because most (or even all) of the tools on the Distributed Geodatabase toolbar are active. Also, when you open the Replica Manager, one or more replicas are listed.

Replica tables in an Oracle DBMS

Before datasets can be replicated, they must have a global ID column and be registered as fully versioned (not registered with the option to save edits to base). Therefore, in the database, the business tables of any datasets that will be included in the replication have a GUID column, and delta tables exist for each of these datasets. The following is a versioned feature class, Districts, that has a global ID column.

Replicas are tracked in the database using the following ArcSDE geodatabase system tables. Click the table names to link to their descriptions.

GDB_REPLICADATASETS

GDB_REPLICALOG

GDB_REPLICAS

GDB_REPLICASEX

The tables are related as follows:

When synchronization is performed between two geodatabases, the table that is created to track dataset lineages is the SDE_UUID_TEMP$ table. This table can be used by multiple sessions simultaneously. In Oracle, the SDE_UUID_TEMP$ table is created as a global temporary table. The description of this table is below.

SDE_UUID_TEMP$

The SDE_UUID_TEMP$ table is used to discover the lineage of a given object via its global ID value.

Field name	Field type	Description
REGISTRATION_ID	NUMBER(38)	The versioned table's registration ID from the TABLE_REGISTRY table
GLOBALID	NCHAR(38)	The global ID for the altered row in the business table of the versioned dataset

The following shows how one of the replicated datasets is related to the SDE_UUID_TEMP$ table:

The SDE_UUID_TEMP$ table is not removed when the session exits because the table will likely be reused in another session.

Replica tables in an XML document

If you choose to replicate to an XML document when you create your replica, information about the replica and replicated datasets are enclosed in <GPReplica> tags as shown below. In this example, the name of the replica is toagency, and it was replicated from geodatabase version PHASE1.

<GPReplica xsi:type="esri:GPReplica">
  <Name>manager.toagency</Name> 
  <ID>-1</ID> 
  <ReplicaVersion>sde.PHASE1</ReplicaVersion> 
  <CreationDate>2007-04-23T12:13:07</CreationDate> 
  <GUID>AFC2DA1A-B751-4096-82DE-7AC9E601A563</GUID> 
  <Role>esriReplicaRoleChild</Role> 
  <AccessType>esriReplicaChildReadOnly</AccessType> 
  <MyGenerationNumber>0</MyGenerationNumber> 
  <SibGenerationNumber>0</SibGenerationNumber> 
  <SibMyGenerationNumber>0</SibMyGenerationNumber> 
  <ReplicaState>esriReplicaStateWaitingForData</ReplicaState> 
  <SibConnectionString>SERVER=liljoe;INSTANCE=5151;VERSION=sde.PHASE1;AUTHENTICATION_MODE=DBMS;ProgID=esriDataSourcesGDB.SdeWorkspaceFactory.1</SibConnectionString> 
 <GPReplicaDescription xsi:type="esri:GPReplicaDescription">
  <ModelType>esriModelTypeFullGeodatabase</ModelType> 
  <SingleGeneration>false</SingleGeneration>
<SpatialRelation>esriSpatialRelIntersects</SpatialRelation> 
 <QueryGeometry xsi:type="esri:EnvelopeN">
  <XMin>-5543912.2421665</XMin> 
  <YMin>3741401.908035</YMin> 
  <XMax>-5538272.6904335</XMax> 
  <YMax>3743452.058665</YMax> 
 <SpatialReference xsi:type="esri:ProjectedCoordinateSystem">
  <WKT>PROJCS["Asia_South_Albers_Equal_Area_Conic",GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Albers"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",125.0],PARAMETER["Standard_Parallel_1",7.0],PARAMETER["Standard_Parallel_2",-32.0],PARAMETER["Latitude_Of_Origin",-15.0],UNIT["Meter",1.0]],VERTCS["Ha_Tien_1960",VDATUM["Ha_Tien_1960"],PARAMETER["Vertical_Shift",0.0],PARAMETER["Direction",1.0],UNIT["Meter",1.0]]</WKT> 
  <XOrigin>-21663300</XOrigin> 
  <YOrigin>-10280500</YOrigin> 
  <XYScale>207890747.363998</XYScale> 
  <ZOrigin>0</ZOrigin> 
  <ZScale>1</ZScale> 
  <MOrigin>-100000</MOrigin> 
  <MScale>10000</MScale> 
  <XYTolerance>0.001</XYTolerance> 
  <ZTolerance>2</ZTolerance> 
  <MTolerance>0.001</MTolerance> 
  <HighPrecision>true</HighPrecision> 
  </SpatialReference>
  </QueryGeometry>
<GPReplicaDatasets xsi:type="esri:ArrayOfGPReplicaDataset">
 <GPReplicaDataset xsi:type="esri:GPReplicaDataset">
  <DatasetName>projects.editor.roads</DatasetName> 
  <DatasetType>esriDTFeatureClass</DatasetType> 
  <RowsType>esriRowsTypeFilter</RowsType> 
  <IsPrivate>false</IsPrivate> 
  <UseGeometry>true</UseGeometry> 
  </GPReplicaDataset>

Other dataset definitions

 </GPReplicaDatasets>
  <TransferRelatedObjects>true</TransferRelatedObjects> 
  </GPReplicaDescription>
  <ReconcilePolicy>esriReplicaResolveConflictsNone</ReconcilePolicy> 
</GPReplica>

Beginning with ArcGIS 9.2, you have the ability to track transaction-time history for your data using geodatabase archiving. Transaction time represents the moment in time when an event is represented in the database. It is delimited when the feature is inserted in the database, then modified or logically deleted. Tracking a dataset's history allows you to keep a record of when and how the data has changed. It also allows you to query previous versions of the data.

Archiving tables in ArcCatalog and ArcMap

To use geodatabase archiving, you register the data as fully versioned, then enable it for archiving in ArcCatalog. (For details on how to perform this operation, see Geodatabase archiving and its related topics.) You can tell if a dataset has already had archiving enabled if, when you right-click the dataset in ArcCatalog and click Archiving, the context menu has Disable Archiving enabled but Enable Archiving is disabled.

Archive classes cannot be viewed in ArcCatalog, but you can save a connection to a specific historical version through ArcCatalog. See the section "Connecting to a specific version of the database" in Creating spatial database connections and the topic Working with a historical version for instructions on how to do this. To help you view the changes made at specific times, you can create historical markers that can be used by others to view the state of the data at that specific time. For details on creating historical markers, see Working with historical markers.

Archiving tables in an Oracle DBMS

When a table is enabled for archiving, an archive class is created. This is a copy of the business table and contains all the same fields plus three new fields: GDB_FROM_DATE, GDB_TO_DATE, and GDB_ARCHIVE_OID. For a description of how these fields are populated, see The archive process.

The name of the archive class table is the same as the original business table name with an underscore and H appended to it. For example, if a feature class named trails is enabled for archiving, an archive class, trails_H, is created in the schema of the owner of the feature class. The archive class table is read-only, stores changes saved or posted to the DEFAULT version of the geodatabase, and is not deleted if its corresponding dataset is unregistered as versioned or deleted. If an archived dataset is unversioned or deleted, the archive class is converted to a temporal table and can still be queried. See Working with the Geodatabase History Viewer for details on viewing different historical versions.

When changes are made to the schema of a dataset that is enabled for archiving—for example, if a field is added or deleted—these changes are automatically added to the corresponding archive class.

NOTE: Never directly alter the schema of an archive class.

Also, when a table is enabled for archiving, a record gets added to the SDE_ARCHIVES table. This record stores the registration IDs of the table that was enabled for archiving and its associated archive class table. For further information on the SDE_ARCHIVES table, see System tables of a geodatabase stored in Oracle.

The following shows a business table—TRAILS—enabled for archiving, its corresponding archive class table, and records in the SDE_ARCHIVES table.

When you create historical markers to view the state of the data at a specific time, the GDB_HISTORICALMARKERS table is populated. For details on creating historical markers, see Working with historical markers.

Archiving tables in an XML document

Archive classes are not exported to XML workspace documents.