Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

Home > Success Center > Database Performance Analyzer (DPA) > Orphaned data in CONSW and CONSS

Orphaned data in CONSW and CONSS

Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 30 Votes: 0 Revisions: 9

Overview

Under certain circumstances, old data can be orphaned (not cleaned) in a couple of DPA repository tables (CONSS and CONSW). When DPA detects orphaned rows, you will see an error message like this in the logs:

Orphaned rows detected in CONSS_15.

Note: In the above message, "15" is the monitored database ID, and CONSS_15 is the table containing orphaned rows.  You might also see messages for CONSW_xx tables.

This article explains how you can safely delete the orphaned rows.

 

Cleaning Orphaned Rows

1) Find Cutoff Date for Orphans:

To find out the cutoff date for orphaned records, you can run the following query (replace xx with the database id matching the message):

-- Oracle Repository
select trunc(min(IEDX)) from CONTT_xx

 

-- SQL Server Repository
select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx


You can safely delete any rows in the CONSS_xx and CONSW_xx tables that have dates older than the date returned by the above query. You may want to count the number of orphaned rows prior to deleting them. If there are a large number of rows, you may want to do them in batches.

 

2) Count Orphans:

-- Oracle Repository
select count(1) from CONSS_xx    -- or CONSW_xx
where D < (select trunc(min(IEDX)) from CONTT_xx)

 

-- SQL Server Repository
select count(1) from CONSS_xx    -- or CONSW_xx
where D < (select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx)

 

3) Clean Orphans:

If there are few enough orphaned rows (e.g. less than 250,000) you can safely delete them all at once:

-- Oracle Repository
delete from CONSS_xx    -- or CONSW_xx
where D < (select trunc(min(IEDX)) from CONTT_xx);
commit;

 

-- SQL Server Repository
delete from CONSS_xx    -- or CONSW_xx
where D < (select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx);
commit;
Last modified
19:17, 22 Jun 2016

Tags

Classifications

Public