Correcting Duplicate Objects Loaded by SQL

One danger of bulk loading data with Oracle SqlLdr into a non-empty vault is that there is no way to check ahead of time if there is a conflict with object Type/Name/Revision patterns.

If a duplicate Type/Name/Revision is encountered, SqlLdr cannot enable the unique constraint on these columns once the load has completed, and you will get the following error:

ORA-01502: index 'XXXX_LXTYPE_UK' or partition of such index is in unusable state.

Use the following procedure to fix the problem.

  1. Execute this MQL command to find all objects in the vault: 

    <mql> temp query bus * * * vault VAULT_NAME;

    You can scan the results to see duplicates.

  2. Get the IDs of the duplicate business objects: 

    <mql> temp query bus TYPE NAME REV select id;

  3. Rename (or delete) one of the conflicting objects: 

    <mql> modify bus ID name NEWNAME;

  4. If necessary, repeat steps 2 and 3 for all duplicates.
  5. When the vault contains only uniquely named business objects, re-index the vault to restore the Type/Name/Rev constraint and index: 

    <mql> index vault VAULT_NAME;