SQL Queries - CAST Central Base - Checking and removing corruptions in CB - Ghost objects

Purpose of Query

This page will help you to identify and cleanup ghost objects (objects that are linked to some other objects but that are not present as objects)

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query for CSS

The query returns the Id’s of the ghost objects and the snapshots that contains the ghost objects

Remediation :

Run the following on the CB:

DELETE FROM dss_link_info WHERE next_object_id IN (SELECT dli.next_object_id FROM dss_link_info dli LEFT JOIN dss_objects o ON o.object_id = dli.next_object_id LEFT JOIN dss_object_info doi ON doi.object_id = dli.next_object_id LEFT JOIN dss_translation_table dtt ON dtt.object_id = dli.next_object_id WHERE dli.link_type_id = 2 AND o.object_id IS NULL AND dtt.object_id IS NULL AND doi.object_id IS NULL)

2. If snapshots were consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

**Query for Oracle**

```sql
SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query result interpretation

The query returns the Id’s of the ghost objects and the snapshots that contains the ghost objects

Query for CSS

Remediation :

Run the following on the CB:

2. If snapshots were consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

**Query for SQL server**

```sql
SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query result interpretation

The query returns the Id’s of the ghost objects and the snapshots that contains the ghost objects

Query for CSS

Remediation :

Run the following on the CB:

2. If snapshots was consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

**Notes/comments**

**Related Pages**