nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Find more posts tagged with

Accepted answers

All comments

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():
&nbsp;&nbsp;&nbsp; if arcpy.Describe(fc).isVersioned:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; print(fc)
‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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?

KenGalliher1

This isn't an ArcGIS/Esri way but take a look at this tech article describing a SQL script that.. "reports information about a versioned geodatabase in Oracle. The output lists the number of versions, the number of versions blocking the DEFAULT version from being compressed to state 0 and the owner.names of the first 5 blocking versions, the number of states, the number of entries in the state_lineages table, and the number of rows in the versioned table and delta tables."

As has been mentioned in the other comments, to see what versioned feature classes have not had their edits posted and compressed to the base table is to look at the delta tables.

How To: Report geodatabase versioning statistics in an Oracle geodatabase

KenGalliher1

Also, try this query to check the count of unposted states on all versioned feature classes:

SELECT tr.table_name AS table_name ,
       s.owner AS state_owner,
       count(*) AS total_unposted_states
FROM sde.table_registry tr
    INNER JOIN sde.mvtables_modified mv
    ON tr.registration_id = mv.registration_id
    INNER JOIN sde.states s
    ON mv.state_id = s.state_id
GROUP BY tr.table_name, s.owner;

MiroUmlauf

Thank you Ken, that is brilliant. Had to just slightly adjust for our SQL server as:

SELECT tr.table_name AS table_name,
       s.owner AS state_owner,
       count(*) AS total_unposted_states
FROM sde.SDE_table_registry tr
    INNER JOIN sde.SDE_mvtables_modified mv
    ON tr.registration_id = mv.registration_id
    INNER JOIN sde.SDE_states s
    ON mv.state_id = s.state_id
GROUP BY tr.table_name, s.owner;

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.