Is there an ESRI way to see if there is versioned data still in progress that needs compressing?

[VaL]

We have oracle 11 SDE database with some possible versioned feature classes.

Besides looking into the relevant tables involved in versioning is there any way to find out if a FC is versioned and might need compressing/finalizing.

Thanks for the input.

[JamesMacKay3]

You can get a list of versioned feature classes using something like this from the Python window:

arcpy.env.workspace = r"Database Connections\YourConnFile.sde"
for fc in arcpy.ListFeatureClasses():
    if arcpy.Describe(fc).isVersioned:
        print(fc)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You could do that for each distinct connection you've got, or put the SDE file paths in a list and iterate through them all together.

Edit: This will only work with standalone feature classes and would have to be extended to detect those inside of feature datasets...

[VaL]

I will need to hit the database directly to see if there are versioned tables/FCs.

From these tables: STATES, STATE_LINEAGES, VERSIONS, and MVTABLES_MODIFIED which one is the best to query to get a definitive answer if there are versioned FCs/tables in the sde db?

I was counting how many records exist in the versions table, but there could be 1 record, but still there are versioned FCs. In this case state_id is > 0.

There are also DBs with more than on record in the versions table.

So what is the best way to query the SDE db to find out?

[Asrujit_SenGupta]

Looking for this -- Example: Determining which datasets are versioned in a geodatabase—Help | ArcGIS Desktop ??

You can query the definition column of the GDB_Items table (or GDB_Items_vw view in Oracle) to return a list of feature classes that have the versioned value set to true (or 1, depending on the database).

[VaL]

Thanks, strangely I can see one FC which I know is versioned but it is not in gdb_items_vw.

Also the sql in the example wont return any records but I know there are versioned FCs.

[VaL]

I was wondering, would state_id <> 0 in the states table indicate presence of versioned data?

I dont care which FCs are versioned, but if there are any or not.

[Asrujit_SenGupta]

If my understanding is correct, you just want to know whether the Geodatabase is fully compressed or it can be compressed further to reach State 0. If that is the case, simply check the Compress Log after performing a compress and check the End State.

You cannot perform the compress for a single Feature Class anyway, as it happens at the Geodatabase level.

You can see the results of each compression in ArcGIS for Desktop in the SDE_compress_log table. You can also check the SDE_versions table to see if the state ID for the DEFAULT version has returned to zero. If it has and there are no other outstanding versions, full compression has been achieved.

Geodatabase compression—Help | ArcGIS Desktop 

[TinaMorgan1]

Right, my understanding of the question is that they are trying to find out which delta tables have records still in them, in other words, which feature class's delta tables still contain records that can be compressed to the base table.  

I am not sure of a way to check the record count of delta tables in ArcCatalog, and the way I would do this would be to just query the database with SQL.

Depending on what your data looks like, you may get something like this by making a database view on the base table.  Since Esri applications will be the base table + the delta tables, making a view on the base table alone could give you an idea of the differences.

[VaL]

Let me double check my understanding of versioning here.

I have 2 major cases 

Case 1: only one record in the sde.versions table with two subcases 1.1: state_id = 0 and 1.2 state_id > 0.

Case 2 more than one records in the sde.versions table with state_id > 0 for each of them

What I think this means is that one can have versioned data in the default version (case 1) with numerous states ( = additions/deletions). Also there can be other versions children to default (most simple case) with numerous states. 

Is that correct?

What happens when the DB is compressed?

[TinaMorgan1]

So being picky on semantics here, versions do not hold data.  So the default version doesn't 'hold versioned data'.  It is simply a view that combines edits made within that version.  The view uses the state_id which can be traced up the versions lineage (state_lineages) all the way up to the base table.  Edits (record updates, inserts and deletes) are what hold states, and those edits must be made within a version so the version state_id only represents that.

So when you compress a geodatabase those edits (with state ids) are pushed from the delta tables into the base table.  That's where the bulk of the action happens.  The other tables (versions, lineages, etc) are for used to implement Esri's multi-user geodatabase functionality.  

(first what kind of database are you using?  SQL Server, oracle, ??)  You can go into your database tables and you should see some tables like a## and d##.  These are your delta tables.  The number corresponds to the registration_id of the table_registry table.  That's where your edits are in a versioned database.  When you compress the geodatabase those a and d tables are truncated, and the edits are pushed into the base table.

This is an old article, but a good one...

Versioned tables in a geodatabase in Oracle