0

I have an Oracle enterprise geodatabase. In theory, all 500+ tables in the database should be registered as versioned, with the option to move edits to base.

However, it has come to my attention that a few of the tables were not registered with the option to move edits to base.

I would like to find all tables that were not registered with base (and then manually re-register them correctly).

Is it possible to write an SQL query for this purpose?

  • 1
    Where have you looked? What have you tried? – Midavalo Jan 15 '18 at 22:29
  • 2
    You cold do this with SQL, but it likely to be a hairy XML query. ArcPy could walk the tables in under 10 lines. – Vince Jan 16 '18 at 11:38
  • Post opened per your request. – Aaron Mar 2 '18 at 17:39
0

I've made some test tables.

Here's what they look like in the sde.table_registry system table:

+-----------------+------------------------+----------+---------------+--------------+---------------+--------------------+-----------------+-------------+----------------------------+
| REGISTRATION_ID |      TABLE_NAME        |  OWNER   | ROWID_COLUMN  | DESCRIPTION  | OBJECT_FLAGS  | REGISTRATION_DATE  | CONFIG_KEYWORD  | MINIMUM_ID  |       IMV_VIEW_NAME        |
+-----------------+------------------------+----------+---------------+--------------+---------------+--------------------+-----------------+-------------+----------------------------+
|          107101 | A_TEST_FC_NOREG        | ENG      | OBJECTID      |              |         16391 |         1520010751 | DEFAULTS        |           1 |                            |
|          107099 | A_TEST_FC_REGBASE      | ENG      | OBJECTID      |              |        114703 |         1520010643 | DEFAULTS        |           1 | A_TEST_FC_REGBASE_EVW      |
|          107100 | A_TEST_FC_REGNOBASE    | ENG      | OBJECTID      |              |         16399 |         1520010715 | DEFAULTS        |           1 | A_TEST_FC_REGNOBASE_EVW    |
|          107092 | A_TEST_TABLE_NOREG     | ENG      | OBJECTID      |              |             3 |         1520010203 | DEFAULTS        |           1 |                            |
|          107056 | A_TEST_TABLE_REGBASE   | ENG      | OBJECTID      |              |         98315 |         1520007082 | DEFAULTS        |           1 | A_TEST_TABLE_REGBASE_EVW   |
|          107057 | A_TEST_TABLE_REGNOBASE | ENG      | OBJECTID      |              |            11 |         1520007121 | DEFAULTS        |           1 | A_TEST_TABLE_REGNOBASE_EVW |
+-----------------+------------------------+----------+---------------+--------------+---------------+--------------------+-----------------+-------------+----------------------------+

I can query the gdb_items and sde.table_registry tables to get the information that I need:

select
    cast(rownum as number(38,0)) as rownum_,
    iu.name,
    iu.versioned,
    cast(tr.object_flags as number(38,0)) as object_flags,
    case tr.object_flags
        when 114703 then 'REGISTERED WITH BASE'
        when 98315  then 'REGISTERED WITH BASE'
        when 11     then 'NOT REGISTERED WITH BASE'
        when 16399  then 'NOT REGISTERED WITH BASE'
    end as versioning_type
from
    (
    select  --non spatial tables
        i.name as name,
        extractvalue(definition_xml.column_value, 'Versioned') as versioned
    from        
         sde.gdb_items_vw i 
    cross join 
        table(xmlsequence(xmltype(i.definition).extract('/DETableInfo/Versioned'))) definition_xml
    union all
    select  --feature classes     
        i.name as name,
        extractvalue(definition_xml.column_value, 'Versioned') as versioned
    from        
         sde.gdb_items_vw i 
    cross join 
        table(xmlsequence(xmltype(i.definition).extract('/DEFeatureClassInfo/Versioned'))) definition_xml
    ) iu
left join 
    sde.table_registry tr
    on iu.name = tr.owner || '.' || tr.table_name
where        
    iu.name like 'ENG.%'

The answer:

+---------------------------------+-----------+---------------+--------------------------+
|           TABLE_NAME            | VERSIONED | OBJECT_FLAGS  |     VERSIONING_TYPE      |
+---------------------------------+-----------+---------------+--------------------------+
| INFRASTR.A_TEST_FC_NOREG        | false     |         16391 |                          |
| INFRASTR.A_TEST_FC_REGBASE      | true      |        114703 | REGISTERED WITH BASE     |
| INFRASTR.A_TEST_FC_REGNOBASE    | true      |         16399 | NOT REGISTERED WITH BASE |
| INFRASTR.A_TEST_TABLE_NOREG     | false     |             3 |                          |
| INFRASTR.A_TEST_TABLE_REGBASE   | true      |         98315 | REGISTERED WITH BASE     |
| INFRASTR.A_TEST_TABLE_REGNOBASE | true      |            11 | NOT REGISTERED WITH BASE |
+---------------------------------+-----------+---------------+--------------------------+

As you can see, the resultset has a versioning_type column that contains the information that I need:

  • null (not versioned)
  • REGISTERED WITH BASE
  • NOT REGISTERED WITH BASE

The object_flags column:

I couldn't figure out how the versioning type (base or no base) is stored in the database. My best guess/workaround was to interpret the values in the object_flags column:

    when 114703 then 'REGISTERED WITH BASE'
    when 98315  then 'REGISTERED WITH BASE'
    when 11     then 'NOT REGISTERED WITH BASE'
    when 16399  then 'NOT REGISTERED WITH BASE'

However, these interpretations are just guesses. I have no idea what the object_flags actually mean (I couldn't find anything in the docs).


Error:

The query runs just fine in SQL Developer. However, it errors-out when run in ArcMap. My best guess is that the cause is some sort of bug in the XML data in my geodatabase system tables (which ArcMap can't handle, but SQL Developer can).

Error reading OID from table. Reading rows has been stopped. Check that the datasource is valid.
Underlying DBMS error[ORA-31011: XML parsing failed
ORA-1923:error occurred in XML processing at lines 1
LPX-00245: extra data after end of document
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1]
The operation is not supported by this implementation.

I'd be curious to know if anyone else has this problem, or if it's just my geodatabase that's causing the error.


XML paths:

Accessing XML paths can be tricky. I have an answer here that might be helpful to others: List of all fields and their domains, for each table using SQL

  • object_flags is an undocumented bit mask. There are likely thousands of valid combinations not captured by your WHEN clauses. Given that you need ArcGIS to enble the strays, the thrashing in SQL is hardly worth the effort when it would take six minutes to learn enough Python. – Vince Mar 2 '18 at 20:13
  • @Vince : Good to know about the "undocumented bit mask". Thanks. Fair points about SQL vs. Python. Will look into it. – Wilson Mar 5 '18 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.