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?
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)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.