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.

[VaL]

Thanks. We use oracle.

I also got this idea, may be slightly wrong semantics.

But what would one record in the versions table with state_id > 0 mean?

Some feature classes are registered as versioned but not with a child version and there are edit in the A### and D### tables. Is that correct?

[TinaMorgan1]

if there is a record in the versions table that has a state_id of 0 then that means that version was created off of its parent when it too was at state zero, AND that no one has ever connected to that version and made edits within it.  Basically you can delete it, as it has never been used.

Feature classes can be registered as versioned and that has no effect on how many versions you actually have.  You may register every feature class in your geodatabase as versioned, but never create any transactional versions to edit within (other than DEFAULT which will always be there).

If there are edits in the a and d tables then that data has not been compressed.  Period.  If you ran a geodatabase compress and STILL see records in the a and d tables, then that means those edits are still being referenced by something, for example an open edit session or a version that has not been reconciled and posted.

[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;