How To: Recreate the ArcSDE state_lineages table when required
The instructions provided demonstrate how to recreate the ArcSDE state_lineages table. This step should only be taken if the consistency of the table is compromised in comparison to the ArcSDE states table. For example, if there are missing entries in the state_lineages table for lineages or lineage_id values, the table should be recreated.
To determine if the ArcSDE state_lineages table needs to be recreated, follow the proceeding steps.
If the table has been completely lost or corrupted because of database failure, skip to step number two.
Discover if there are any missing state lineage_name or lineage_id values by executing the following two SQL statements. If either statement returns a count greater than 0, proceed to step 2 and recreate the state_lineages table.
Connect to the DBMS as the ArcSDE administrator and execute the following statements. This example uses Oracle's SQL*Plus.
Code: SQL> SELECT COUNT(*) 2 FROM sde.state_lineages 3 WHERE lineage_name NOT IN 4 (SELECT DISTINCT lineage_name 5 FROM sde.states);
COUNT(*) ---------- 0
If the value of count is greater than 0, proceed to step 2.
Code: SQL> SELECT COUNT(*) 2 FROM sde.state_lineages 3 WHERE lineage_id NOT IN 4 (SELECT state_id 5 FROM sde.states);
COUNT(*) ---------- 0
If the value of count is greater than 0, proceed to step 2. If both queries return a count of 0, there is no need to recreate the state_lineages table.
If either of the previous two queries returned a count greater than 0, one should proceed to recreate the ArcSDE state_lineages table.
To recreate the table, first have all users disconnect from the instance (including users using direct connect), and stop the ArcSDE Application Service.
Next, as the ArcSDE administrative user, drop the existing state_lineages table.
Code: SQL> DROP TABLE state_lineages;
Note: Prior to performing any administrative steps, it's always best to ensure one has a quality backup of their database in case any unforeseen errors are encountered.
The next step is to execute the sdesetup command. The sdesetup command detects that the state_lineages table is not present, creates the table, and populates the table by using the states table.
See the ArcSDE documentation for instructions on the sdesetup command.