Home » SQL & PL/SQL » SQL & PL/SQL » Finding duplicated tables
Finding duplicated tables [message #442201] Fri, 05 February 2010 05:28 Go to next message
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 Go to previous messageGo to next message
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 #442209 is a reply to message #442201] Fri, 05 February 2010 05:56 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
charles_stanes wrote on Fri, 05 February 2010 05:28
table). I am having as many as 42350 tables to compare and check
C. Charles Abraham


Shocked 42350 tables in one DB Are you sure ?

check folloiwng query.

  select TABLE_NAME , COLUMN_NAME from user_tab_columns;
Re: Finding duplicated tables [message #442210 is a reply to message #442201] Fri, 05 February 2010 06:00 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just bear in mind that user_tab_columns contains columns of views so you'll probably want to filter those out.
Re: Finding duplicated tables [message #442217 is a reply to message #442209] Fri, 05 February 2010 07:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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).
Re: Finding duplicated tables [message #442406 is a reply to message #442293] Sun, 07 February 2010 21:15 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hope.. He got the Moral of this story...

sriram
Previous Topic: simple sql
Next Topic: sub query
Goto Forum:
  


Current Time: Fri Feb 14 08:26:27 CST 2025