检查SDE版本健康情况的常用SQL语句

检查各版本的状态树长度。

SELECT v.owner||'.'||v.name "VERSION NAME", COUNT(sl.lineage_id) "LINEAGE LENGTH"

FROM sde.states s, sde.state_lineages sl, sde.versions v

WHERE s.lineage_name = sl.lineage_name

AND sl.lineage_id <= s.state_id

AND v.state_id = s.state_id

GROUP BY v.owner, v.name, sl.lineage_name

ORDER BY "LINEAGE LENGTH";

长度越短查询性能越好。

检查某一图层的Default版本中未压缩到基础表的变化记录以及相对其他版本的占比

1、 首先获得图层的注册ID。

SELECT registration_id

FROM sde.table_registry

WHERE owner = 'ADMIN' AND table_name = 'PRIMARYOH';

2、 查询Default版本的状态ID和状态树名称。

VARIABLE lineage_id NUMBER;

BEGIN

SELECT state_id INTO :lineage_id FROM sde.versions WHERE owner = 'SDE' AND name = 'DEFAULT';

END;

VARIABLE lineage_name NUMBER;

BEGIN

SELECT lineage_name INTO :lineage_name FROM sde.states WHERE state_id = :lineage_id;

END;

3、 查询Default版本未压缩的增量表记录，假设图层的注册ID为67。

SELECT

 (SELECT COUNT(*) FROM admin.a67

   WHERE sde_state_id IN

    (SELECT lineage_id FROM sde.state_lineages

   WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",

       ROUND((SELECT COUNT(*) FROM admin.a67

              WHERE sde_state_id IN

                (SELECT lineage_id FROM sde.state_lineages

                 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))

    /(SELECT COUNT(*) FROM admin.a67) * 100, 2) AS "PERCENTAGE OF ROWS",

  (SELECT COUNT(*) FROM admin.a67) AS "TOTAL ROWS"

FROM dual;

4、 查询Default版本未压缩的删除表记录，假设图层的注册ID为67。

SELECT

 (SELECT COUNT(*) FROM admin.d67

   WHERE deleted_at IN

    (SELECT lineage_id FROM sde.state_lineages

    WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",

       ROUND((SELECT COUNT(*) FROM admin.d67

              WHERE deleted_at IN

                (SELECT lineage_id FROM sde.state_lineages

                 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))

     /(SELECT COUNT(*) FROM admin.d67) * 100, 2) AS "PERCENTAGE OF ROWS",

   (SELECT COUNT(*) FROM admin.d67) AS "TOTAL ROWS"

FROM dual;

Default版本的变化表记录越少越好，应该尽早提交阻碍Default版本压缩的版本，并压缩数据库。