Find tables with duplicate keys on Oracle DB/SAP db

[behlau_carlos]

Hello SAP experts,

does there exists a script/check function in SAP/oracle that identifies tables, that containing duplicate keys (even if it takes xx hours/days it would be OK)?

(just FYI: duplicate keys are avoiding creating indexes - so I would first been alert once a index needs to be created)

When I test it on a SAP system and run later validate structure check, there is no warning posted into the logs.

Here I create a test table and add duplicate values:

But analyze task validate structure (from Br*Tools/DB13) does not detect any issue.

Many thanks for your help.

Best regards

Carlos Behlau

[Former Member · Accepted solution]

Easy answer:

There is no duplicate key. Period.

(Just look up the definition of 'key'.)

What you tried in your example:

You created a table duplcomp, without a key.

Then you entered duplicate entries (not duplicate keys!) for the pair (a1,a2) in table duplcomp.

That's not forbidden.

And of course check tools will not detect a problem here.

There is no problem.

But later on, it seems, you decided that you will need a primary key.

Sorry, you should have thought of this earlier.

Now, with these data, you can't have a primary key.

Not even a 'duplicate key'.

And I don't understand what you want to check here.

Either there already is a primary key. Then there are no duplicate keys.

Or there isn't a primary key yet, and you may or may not want to create one. But then all will depend on which columns you are going to create the key. And only these columns will have to be checked. No generic answer is possible.

hope this helps

[behlau_carlos · Accepted solution]

Hello Josef,

we having a SAP system, where we think, a client copy of production to development, caused duplicated primary keys.

For example: I have the table KAKO that is containing twice same customer number.

Now we want to identify all the effected tables in the system, so we are able to correct them.

Best regards

Carlos Behlau

[behlau_carlos · Accepted solution]

Just FYI: We deleted already the client in development system, but there still several tables, containing duplicate entries (for example same customer number).

[Former Member · Accepted solution]

KAKO that is containing twice same customer number.

I just had a look in one of our systems; couldn find a field 'customer number'.

Anyway, the key of KAKO consists of two fields, MANDT and KAPID.

There is no reason why other fields shouldn't have duplicate entries.

Even KAPID could have the same value several times, as long as MANDT differs.

I still don't understand what your problem is, but please don't waste your time looking for nonexistent duplicate keys!

If there is an active primary key, it can't have duplicate values.

Only problem I could imagine: If a key was deleted for some reason, it may be impossible to create it again afterwards, for exactly that reason.

regards

[behlau_carlos · Accepted solution]

Hi Josef,

here is what is got from application team:

From my understanding, the client 401 (Field=MANDT) and field KAPID are the same.

Sorry for my false understanding of "customer ID", if I understood all correct KAKO is connected to several other application processes and when the issue was discussed, "customer id" was the term.

Best regards

Carlos Behlau

[Former Member · Accepted solution]

Then I suspect that the primary key has been deleted.

Please check which indexes are there for table KAKO.

[behlau_carlos · Accepted solution]

Hello Josef,

yes, we are correcting the tables.

But our problem is, how we can identify all the tables that are having the same issue.

At the moment we are on trail and error approach via export/import, as during export all indexes are getting dropped and import, indexes are getting created in later step.

It would been a great help, if there would be some sort of analyze program on oracle or SAP level, so we can fix all the reported tables.

We don't have a issue fixing it, once we identified the tables, but getting a list, is the tricky part ...

Import helps here, but it would be better of the in the source system a list.

Best regards

Carlos Behlau

[Former Member · Accepted solution]

... during export all indexes are getting dropped and import, indexes are getting created in later step.

I doubt that this is a wise approach.

This will require that you can control import, so that any one entry will be imported once, and only once.

Correcting afterwards is tedious, as you seem to have found out.

And I'm afraid the fastest way to find out will be: Try to create the primary index, and watch out for error messages!

regards

[Former Member · Accepted solution]

Hi Carlos,

I believe this is the one you're looking for..

2007272 - Identify Duplicated Records in Oracle tables

Regards

Prithviraj.

[behlau_carlos · Accepted solution]

Hi Prithviraj,

thanks you so much for your help.

This is exact what I (we) where looking for ...

Best regards

Carlos Behlau

[Former Member]

Hi Carlos,

A simple way to find the duplicate keys in a table is

select column1, column2, count(*) from table group by column1 column2 having count(*) > 1

Also check,

https://community.oracle.com/thread/2478364?start=0&tstart=0

Ask Tom "unique index"

Delete duplicate rows from Oracle tables

23237 - duplicate keys, duplicate rows, duplicate records

682926 - Composite SAP note: Problems with "create/rebuild index"

Lot of readings...

Regards,

Prithviraj.

[behlau_carlos]

Hello Prithviraj,

but this requires I know the effected tables.

I have a SAP system, due to a client copy (we guess), hosting unknown amount of tables with duplicate primary keys.

Now the question is, how to get a list of the tables, that are having duplicate primary keys.

I will get them, once I try to create a index, but this error and trail approach is consuming a lot of time ...

Best regards

Carlos Behlau