FAQ: Can a multiversioned view be deleted without leaving an entry in the table registry?

Question

Can a multiversioned view be deleted without leaving an entry in the table registry?

Answer

Yes. To delete a multiversioned view, always use sdetable -o delete_mv_view.

Important facts about deleting multiversioned views:

· For -t, specify the name of the table or layer from which the view is created. For example: use the same value used in the -t option to create the multiversioned view. This procedure is shown below:

Code:
C:>sdetable -o create_mv_view -T shale_vw -t shale -i esri_sde -u sde -p sde

ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
Successfully created MV view shale_vw for table shale.

C:\>sdetable -o delete_mv_view -t shale -i esri_sde -u sde -p sde
ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
Delete MV view for table shale?
Are you sure? (Y/N): y
Successfully deleted MV view for table shale.

Using the above method, a multiversioned view can be created subsequently without issue, as shown below:

Code:
C:\>sdetable -o create_mv_view -T shale_vw -t shale -i esri_sde -u sde -p sde

ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
Successfully created MV view shale_vw for table shale.

· Caution: Using sdetable -o delete -t <view_name> leaves orphaned entries that associates the original table or layer with the multiversioned view in the table registry. This must be manually corrected. If it is not manually corrected, an error results if a multiversioned view is recreated. For example:

Code:
C:\>sdetable -o create_mv_view -T beach_vw -t beach -i esri_sde -u sde -p sde

ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
Successfully created MV view beach_vw for table beach.

C:\>sdetable -o delete -t beach_vw -i esri_sde -u sde -p sde

ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
Delete table beach_vw! Are you sure? (Y/N): y
Successfully deleted table beach_vw.

C:\>sdetable -o create_mv_view -T beach_vw -t beach -i esri_sde -u sde -p sde

ArcSDE 9.1 Oracle10g  Build 407 Mon Mar  5 10:50:46 PST 2007
Attribute        Administration Utility
-----------------------------------------------------
        Error: DBMS view exists (-238).
        Error: Unable to create MV view beach_vw for table beach.

When the above happens, use SQL to clean up the entry in the table registry as follows:

For Oracle, connect as the SDE user and run this update statement:

Code:
UPDATE table_registry
SET imv_view_name = NULL 
WHERE table_name = 'BEACH' AND owner = '<owner of BEACH>'

For SQLServer, connect as the SDE user, or database operational (DBO) if using a DBO schema, and run this update statement:

Code:
UPDATE sde_table_registry
SET imv_view_name = NULL 
WHERE table_name = 'BEACH' AND owner = '<owner of BEACH>'

· At version 9.2, the orphaned entry is cleaned up by ArcSDE.