Deleting the duplicates from ct_dn table

Manual modification of the OID database is not supported. The proper way is to always restore from a valid backup.
However, in certain circumstances you may need to delete the duplicate rows from the CT_DN table. In such circumstances you may need to follow the steps given below. You MUST take a backup of system before proceeding with the steps below:

REMOVING DUPLICATE ENTRIES:

1) In order to determine duplicate values in table cn_dn issue the following command while logged in as ODS user:
select rdn, parentdn, count(*)
from ct_dn
group by rdn, parentdn
having count(*) > 1;

2) If (1) returns any values then for sanity reasons we need to find out which rows we need to keep.
You can do that using the following sql:

select min(rowid),rdn, parentdn
from ct_dn
group by rdn, parentdn;

3) Now you can delete rows from ct_dn table using the following command:

delete from ct_dn
where rowid not in (select min(rowid) from ct_dn group by rdn,parentdn);

4) You will need to do the same thing for any other tables that have duplicate rows.
(Obs: Because the other table named here generic TABLENAME should have different columns name
sql statements are similar
for 2. select ENTRYID, count(*) from TABLENAME group by ENTRYID having count(*) > 1;
for 3. select min(rowid),ENTRYID from TABLENAME group by ENTRYID;
for 4. delete from TABLENAME where rowid not in (select min(rowid) from TABLENAME group by ENTRYID);
)

Please note there’s an index on CT_DN table which prevents this. You will need to check if you have the right indexes on your table.

5) Run the following selects using sqlplus when logged in as ODS:

– select index_name, uniqueness from user_indexes where table_name=’CT_DN’;
– select index_name, column_name, column_position from user_ind_columns
where table_name=’CT_DN’ order by 1,3;

It should return 3 indexes:

INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
RP_DN UNIQUE
PN_DN NONUNIQUE

The index RP_DN which consists of two columns, parentdn and rdn makes sure that we can’t have duplicate DNs. Check that this index exists and that it is unique, and that it covers correct columns.

If you do not have the index RP_DN then most likely your result will look like this:

SQL> select index_name, uniqueness from user_indexes where table_name=’CT_DN’;
INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
PN_DN NONUNIQUE

SQL> select index_name, column_name, column_position from user_ind_columns
2 where table_name=’CT_DN’ order by 1,3;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
—————————— ————————- —————
EP_DN ENTRYID 1
EP_DN PARENTDN 2
PN_DN PARENTDN 1

As discussed earlier there should be an index called “RP_DN” listed – notice it is not.
Since it is missing you can create it with the following sql but ONLY after you have removed duplicate rows
:

CREATE UNIQUE INDEX rp_dn on ct_dn (parentdn,rdn)
TABLESPACE OLTS_IND_CT_DN
PARALLEL 2 UNRECOVERABLE;

After you have created the index you will see the following result:

select index_name, uniqueness from user_indexes where table_name=’CT_DN’;

INDEX_NAME UNIQUENESS
—————————— ———-
EP_DN UNIQUE
RP_DN UNIQUE
PN_DN NONUNIQUE

select index_name, column_name, column_position from user_ind_columns
where table_name=’CT_DN’ order by 1,3;

INDEX_NAME COLUMN_NAME COLUMN_POSITION
—————————————————————
EP_DN ENTRYID 1
EP_DN PARENTDN 2
PN_DN PARENTDN 1
RP_DN PARENTDN 1
RP_DN RDN 2