Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two table structure (Oracle 10g)
Difference between two table structure [message #398381] Thu, 16 April 2009 02:27 Go to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi everyone,

I have a similar set of table in two different schema. I want to compare between each similar table.

For e.g.
schema1 has the tables table1 and table2. schema2 has the tables table1_arc and table2_arc. Ideally schema1.table1 and schema2.table1_arc should have same number of columns and the case is same for table2 and table2_arc.

I want to know if any new column is added in schema1.table1 or schema1.table2 etc. with the help of a pl/sql code.

I just want to know the difference between two tables in terms of columns and not in any other terms like tablespace or constraints etc.

I do not want to use any tool like toad or plsql editor etc. The solution should be made through a pl/sql code.

Any type of assistance in this regard is appreciated.



Thanks in advance,
K Mitra
Re: Difference between two table structure [message #398384 is a reply to message #398381] Thu, 16 April 2009 02:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check all_tab_columns. You can use minus or count....

By
Vamsi
Re: Difference between two table structure [message #398386 is a reply to message #398384] Thu, 16 April 2009 02:35 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Thanks vamsi,


I have checked that. If the comparison is made in two tables of same schema then it is fine. But if tables are placed in two different schema, then the pl/sql code got hanged and after some times throws the message 'not connected to oracle'

Regards,
KM

Re: Difference between two table structure [message #398389 is a reply to message #398386] Thu, 16 April 2009 02:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Paste your query.
You may hardcode the schema name as the owner and try.

By
Vamsi
Re: Difference between two table structure [message #398393 is a reply to message #398386] Thu, 16 April 2009 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
k_mitra100 wrote on Thu, 16 April 2009 09:35
Thanks vamsi,

I have checked that. If the comparison is made in two tables of same schema then it is fine. But if tables are placed in two different schema, then the pl/sql code got hanged and after some times throws the message 'not connected to oracle'

Regards,
KM

So your code is wrong.

Note that OEM has a (paying) module to do this.

Regards
Michel
Re: Difference between two table structure [message #398394 is a reply to message #398381] Thu, 16 April 2009 02:46 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi Vamsi,
The pl/sql code is attached here.

CREATE OR REPLACE procedure compare_table ( table1 in varchar2, table2 in varchar2) is
cursor cursor2( table1 varchar2, table2 varchar2) is
with
t1 as (select column_name, data_type, data_length from all_tab_columns where table_name=table1 and owner ='SCHEMA1'),
t2 as (select column_name, data_type, data_length from all_tab_columns where table_name=table2 and owner = 'SCHEMA2 )
select
t1.column_name TABLE1,
t1.data_type TABLE1_TYPE,
t1.data_LENGTH TABLE1_DATALENGTH,
t2.column_name TABLE2,
t2.data_type TABLE2_TYPE,
t2.data_LENGTH TABLE2_DATALENGTH
from
t1 full join t2
on (t1.column_name=t2.column_name);

p_table1 varchar2(1000);
p_table2 varchar2(1000);

begin


for x in cursor2 ( table1 , table2 )
loop
IF x.TABLE2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE(rpad(X.TABLE1,20) ||rpad(' ',5)|| rpad(x.TABLE1_TYPE,20) ||rpad(' ',5)||RPAD( x.table1_DATALENGTH,20) || RPAD(' ',5) || rpad(X.TABLE2,20) || rpad(' ',5)|| X.TABLE2_TYPE ||rpad(' ',5)|| RPAD(x.TABLE2_DATALENGTH,20));
END IF;
end loop;
end;
/



EXEC compare_table('TABLE1','TABLE1_ARC');

I tried to write this procedure in the 'SCHEMA2' and in the 'SYSTEM' schema as well. In both the cases the proc got hanged.

Thanks,
KM
Re: Difference between two table structure [message #398397 is a reply to message #398394] Thu, 16 April 2009 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This code can't compile.
Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: Difference between two table structure [message #398406 is a reply to message #398394] Thu, 16 April 2009 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It works fine for me.

I hope you're not putting your tables in the SYSTEM schems - it's not an advisable practice.

If (as your code suggests) you're only interested in columns in T1 that don't exist in T2, you could just rewrite your query as
SELECT table_name
      ,column_name
      ,data_type
      ,data_length
FROM   all_tab_columns
WHERE  owner = 'SCHEMA1'
AND    table_name = 'T1'
AND    column_name NOT IN (SELECT column_name
                           FROM   all_tab_columns
                           WHERE  owner = 'SCHEMA2'
                           AND    table_name = 'T2');


You should have a closer look at the columns in All_Tab_Columns - you might want more than just data_type and data_length.
Re: Difference between two table structure [message #398412 is a reply to message #398406] Thu, 16 April 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It works fine for me.

It can't: " owner = 'SCHEMA2 )". Missing quote.

Regards
Michel
Re: Difference between two table structure [message #398431 is a reply to message #398412] Thu, 16 April 2009 04:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So you cut and pasted the code, and gave up because there was a typo?

Laughing

How very helpful....

Re: Difference between two table structure [message #398432 is a reply to message #398431] Thu, 16 April 2009 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No I immediately saw this error and did not try to find if there was more, just ask to post something real and not try to answer to something inconsistent.

Regards
Michel

[Updated on: Thu, 16 April 2009 04:27]

Report message to a moderator

Re: Difference between two table structure [message #398437 is a reply to message #398406] Thu, 16 April 2009 04:54 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi,

The solution works for me. Thanks to everyone who has contributed on this thread.

Using the query provided by 'JRowbottom' in a pl/sql block it works for me.


Thanks & Regards,
KM
Re: Difference between two table structure [message #409734 is a reply to message #398386] Tue, 23 June 2009 14:43 Go to previous messageGo to next message
khaled kandil
Messages: 1
Registered: March 2008
Junior Member
Laughing
simply you can check the user_objects_view
and compare between the last_ddl column of the two tables
if the date is not nearly so it means that an alter table comand was issued to one table to add or modfiy a column in one table and it does not hapeend in the second ...excuse me if there some errors in writting ...english is not my native language
Re: Difference between two table structure [message #409738 is a reply to message #409734] Tue, 23 June 2009 15:10 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
khaled kandil wrote on Tue, 23 June 2009 15:43
Laughing
simply you can check the user_objects_view
and compare between the last_ddl column of the two tables
if the date is not nearly so it means that an alter table comand was issued to one table to add or modfiy a column in one table and it does not hapeend in the second ...excuse me if there some errors in writting ...english is not my native language


I would have to disagree. Do not worry about english writing skills; I think I got what you were saying, but...comparing the LAST_DDL column so that they are close together in time does not make sense to me.

What if you added/alter a column in one table on Monday then did the same thing to the other table 2 days later?

Or what if you had two sessions opened and ran the alter/add on the two tables almost simultaneously, but instead of NUMBER(1) in one table you did a NUMBER(10) on the other?
Previous Topic: Update two tables using single query
Next Topic: problem in row generation
Goto Forum:
  


Current Time: Fri Dec 02 18:38:45 CST 2016

Total time taken to generate the page: 0.27971 seconds