As the title says - I want to create a query in Oracle SQL which will read all dates from the geoprocessing history which can be found using ArcCatalog to show the "Description"-tab from a feature. (You need to change the settings of ArcCatalog to use the following "Metadata Style")

If possible I just want to use SQL only - no python or arcpy.

Settings changed in ArcCatalog to INSPIRE Metadata Directive

enter image description here

The first approach I used was to query the "cdate" field within "sde.layers" table with the following query:

SELECT Upper(sde.layers.owner 
         || '.' 
         || sde.layers.table_name)                                    AS 
   ( To_date('1970-01-01', 'YYYY-MM-DD') + sde.layers.cdate / 86400 ) AS 
FROM   sde.layers 
WHERE  sde.layers.owner LIKE 'SDE_V_%' 
ORDER  BY last_update DESC; 

The query above will list the creation date of every layer within the ArcSDE which is not what I need.

I tried querying the SDE_XML_DOC2 table which should contain the information I described above but wasn't successful because oracle developer always outputs the following error:


SELECT Xmltype(xml_doc, 871) 
FROM   sde.sde_xml_doc2 
WHERE  sde_xml_id = 8641; 


LPX-00217 invalid character error

I tried different character sets (1 US7ASCII, 170 WIN1250, 171 WIN1251, 178 WIN1252, 871 UTF-8) within the XMLType function but all of them returned the same error.

I'am stuck at this point and need some help. The general idea is to get the last modified date of all layers within the sde (vector and raster).

1 Answer 1


SQL-only / database-only possible solutions:

A. - add a timestamp column to every layer using systimestamp as default value, then query for latest timestamp

B. - add a trigger ON INSERT and ON UPDATE to every layer table, that updates last modified date value for each layer, on a table of your own.

  • While that is possible i would like to use the data already given by esri/arcgis. Thank you for the different approach. It would be nice to get an example querying the already given tables.
    – theaando
    Commented Nov 2, 2015 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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