Finding duplicated tables [message #442201] |
Fri, 05 February 2010 05:28  |
charles_stanes
Messages: 1 Registered: February 2010
|
Junior Member |
|
|
Hi,
I have got a table with three fields. My actual problem is, I need to find out another table with some of the original table's rows having duplicated.(i.e. the rows of the original table has been duplicated in another table - I need to find the new table). I am having as many as 42350 tables to compare and check one by one. Can anyone please help me by providing with a query where I can compare it very easily?
C. Charles Abraham
|
|
|
Re: Finding duplicated tables [message #442208 is a reply to message #442201] |
Fri, 05 February 2010 05:55   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
It probably can't be done very easily.
You can have a look at the user_tables and user_tab_columns views to get the tables and columns you have in the database, and then construct dynamic select statements.
|
|
|
|
|
Re: Finding duplicated tables [message #442217 is a reply to message #442209] |
Fri, 05 February 2010 07:04   |
John Watson
Messages: 8965 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Are you using release 11g? If so, you have the dbms_comparison package. It is meant for Streams, to detect whether tables contain different rows and if so converge them. But I think you could reverse the logic, and use it to detect duplicate rows instead. But across 42000 tables it would be a slow job (even my EBS databases have only 35000 tables.)
|
|
|
Re: Finding duplicated tables [message #442293 is a reply to message #442201] |
Sat, 06 February 2010 10:51   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
As already pointed out - you got a lot of tables there. It might indicate some architecture/design problem.
When I was assigned to my last project I have realized that some PL/SQL code is generating queries with "CREATE TABLE AS SELECT" and that every week there are ~15000 new tables created. Currently we got this down to 300 tables and there is no DDL involved from PL/SQL. Less mess, less maintenance, less to worry about. Previous person working on maintenance of this DB was working ~60 hours a week to maintain it. Now I can maintain it by spending ~5 minutes a week just to check that everything is OK (unless an issue occur - then it is usually ~1 hour).
|
|
|
|