Home » RDBMS Server » Server Utilities » How to compare column name of a schema with other schema's column name (PLSQL Oracle 11.2.0.1.0 Linux)
How to compare column name of a schema with other schema's column name [message #572673] Sat, 15 December 2012 07:18 Go to next message
nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR
Member
Hello

I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop. Kindly help me I am not good in plsql.

SET SERVEROUTPUT ON
DECLARE

   V_COLS                VARCHAR2(20);

BEGIN

           FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
                               WHERE OWNER LIKE 'CCD_MAIN'
                               )
           LOOP
                   FOR CUR_USR IN(SELECT DISTINCT TABLE_NAME, OWNER
                                FROM ALL_TABLES
                                AND OWNER NOT LIKE 'CCD_MAIN'
                                ORDER BY 2)

                       LOOP

                            FOR CUR_COL IN (SELECT DISTINCT COLUMN_NAME  FROM
                                 (SELECT DISTINCT COLUMN_NAME  FROM
                                  ALL_TAB_COLUMNS
                                  WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
                                  AND OWNER=CUR_CCD.OWNER
                                  MINUS
                                  SELECT DISTINCT  COLUMN_NAME FROM
                                  ALL_TAB_COLUMNS
                                  WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
                                  AND OWNER =CUR_USR.OWNER))
                           LOOP

                           DBMS_OUTPUT.PUT_LINE(CUR_USR.OWNER||' : '||CUR_CCD.TABLE_NAME||' :'||CUR_COL.COLUMN_NAME);

                           END LOOP;

                           END LOOP;
           END LOOP;

END;
/


Thanks
Neha


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Sat, 15 December 2012 10:14] by Moderator

Report message to a moderator

Re: How to compare column name of a schema with other schema's column name [message #572678 is a reply to message #572673] Sat, 15 December 2012 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what should be the output.

From your previous topic:

Michel Cadot wrote on Fri, 08 June 2012 15:35
...
Michel Cadot wrote on Fri, 17 February 2012 13:51
...
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...

...


Regards
Michel
Re: How to compare column name of a schema with other schema's column name [message #572679 is a reply to message #572673] Sat, 15 December 2012 11:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
The following contains minimal corrections and simplification. You could probably simplify it further or even do it all with just SQL, without PL/SQL.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_tab IN
    (SELECT DISTINCT ccd.owner ccd_owner, ccd.table_name ccd_table_name, usr.owner usr_owner
     FROM   all_tables ccd, all_tables usr
     WHERE  ccd.table_name = usr.table_name
     AND    ccd.owner != usr.owner
     AND    ccd.owner = 'CCD_MAIN'
     ORDER  BY ccd.table_name, usr.owner)
  LOOP
    FOR cur_col IN 
      (SELECT column_name  
       FROM   all_tab_columns
       WHERE  owner = cur_tab.ccd_owner
       AND    table_name = cur_tab.ccd_table_name
       MINUS
       SELECT column_name
       FROM   all_tab_columns
       WHERE  owner = cur_tab.usr_owner
       AND    table_name = cur_tab.ccd_table_name)
    LOOP
      DBMS_OUTPUT.PUT_LINE
        (cur_tab.usr_owner      || ' : ' || 
         cur_tab.ccd_table_name || ' :'  || 
         cur_col.column_name);
    END LOOP;
  END LOOP;
END;
/

[Updated on: Sat, 15 December 2012 11:49]

Report message to a moderator

Re: How to compare column name of a schema with other schema's column name [message #572680 is a reply to message #572679] Sat, 15 December 2012 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no need of slow PL/SQL loop.

Regards
Michel
Re: How to compare column name of a schema with other schema's column name [message #572681 is a reply to message #572680] Sat, 15 December 2012 13:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Sat, 15 December 2012 10:25
There is no need of slow PL/SQL loop.

Regards
Michel


You could do something like the following.

SELECT DISTINCT usr.owner, ccd.table_name, ccd.column_name
FROM   all_tab_columns ccd, all_tab_columns usr
WHERE  ccd.table_name = usr.table_name
AND    ccd.owner != usr.owner
AND    ccd.owner = 'CCD_MAIN'
AND    EXISTS
       (SELECT atc1.column_name  
        FROM   all_tab_columns atc1
        WHERE  atc1.owner = ccd.owner
        AND    atc1.table_name = ccd.table_name
        AND    atc1.column_name = ccd.column_name)
AND    NOT EXISTS
       (SELECT atc2.column_name
        FROM   all_tab_columns atc2
        WHERE  atc2.owner = usr.owner
        AND    atc2.table_name = ccd.table_name
        AND    atc2.column_name = ccd.column_name)
ORDER  BY ccd.table_name, usr.owner
/

Re: How to compare column name of a schema with other schema's column name [message #572689 is a reply to message #572681] Sun, 16 December 2012 02:01 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Welcome back mam, excellent way to find the difference at column level.
Re: How to compare column name of a schema with other schema's column name [message #572829 is a reply to message #572689] Tue, 18 December 2012 01:08 Go to previous messageGo to next message
nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR
Member
Thanks to all.
I chnaged my code.

SET SERVEROUTPUT ON
DECLARE
V_COLS                VARCHAR2(20);
BEGIN

                   FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER 
                                  FROM ALL_TABLES
                                  WHERE OWNER LIKE 'CCD_MAIN')
                                      
                   LOOP
                           FOR CUR_USR IN(SELECT DISTINCT TABLE_NAME, OWNER
                                          FROM ALL_TABLES
                                          WHERE OWNER LIKE '%_MAIN'
                                          and OWNER NOT in ('CCD_MAIN','SYS','SYSTEM','OUTLN','SCOTT')
                                          and table_name = cur_ccd.table_name
                                          ORDER BY 2)

                        LOOP

                                    FOR CUR_COL IN (SELECT DISTINCT COLUMN_NAME  FROM
                                                              (SELECT DISTINCT COLUMN_NAME  
                                                              FROM  ALL_TAB_COLUMNS
                                                              WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
                                                              AND OWNER=CUR_CCD.OWNER
                                                              MINUS
                                                              SELECT DISTINCT  COLUMN_NAME 
                                                              FROM ALL_TAB_COLUMNS
                                                              WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
                                                              AND OWNER =CUR_USR.OWNER))

                                           LOOP
                                            
                                            DBMS_OUTPUT.PUT_LINE(CUR_USR.OWNER||' : '||CUR_CCD.TABLE_NAME||' : '||CUR_COL.COLUMN_NAME);

                                           END LOOP;
                               END LOOP;
                   END LOOP;
        END;
        /

Thanks & Regards/
Neha Verma
*BlackSwan added {code} tags. do so yourself in the future!

[Updated on: Tue, 18 December 2012 08:47] by Moderator

Report message to a moderator

Re: How to compare column name of a schema with other schema's column name [message #572905 is a reply to message #572829] Tue, 18 December 2012 09:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
You have some unnecessary DISTINCT's. MINUS results in distinct values, so you do not need to use distinct within that query or within an outer query. Why not use just SQL, which is more efficient than PL/SQL?
Re: How to compare column name of a schema with other schema's column name [message #572949 is a reply to message #572905] Tue, 18 December 2012 21:48 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

in case if i want to do the same column comparisions for same table lying in two different databases, then how can i compare.lets us there is one database user rakdb which has table om_item with field item_code varchar2(12),item_name varchar2(12), item_long_name varchar2(20) but in another database user raklive the same table exists with one column less, om_item with fields item_code varchar2(12),item_long_name varchar2(20).How to list out them.
Re: How to compare column name of a schema with other schema's column name [message #572950 is a reply to message #572949] Tue, 18 December 2012 21:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330
Re: How to compare column name of a schema with other schema's column name [message #572951 is a reply to message #572950] Tue, 18 December 2012 22:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks mam for the prompt response , i read that article which you referred but there is one small confusion there are two options
1. do i need to create a db link and compare the user,table,column
2. do i need to import that schema user to the existing one and then compare
Actually i have to go with option a since both the user names on different database are same, like db1 have rakdb and db2 also have rakdb.Can you please suggest me how to create this dblink and do the comparision.
Re: How to compare column name of a schema with other schema's column name [message #572952 is a reply to message #572951] Tue, 18 December 2012 22:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
The syntax for creating a database link is available in the online documentation.
Re: How to compare column name of a schema with other schema's column name [message #572953 is a reply to message #572952] Tue, 18 December 2012 22:35 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks very much , i will search for this in the documentation and will compare the columns , is there a way to find out differences at procedure/triggers/packages level to know whether they have been modified or not just like tables.Actually there is one problem basically we are facing , we are having two databases one at our site called raklive with user rakdb and another at remote site with another database jedlive with user jeddb but objects in both the databases are same , sometimes at our headoffice that is jedlive does some programming and they fail to intimate us about the changes and so we need to compare everytime, i have two queries and i need your goodself help on this.
1.I need to update the changes whatever done there to be done here using some standard method.
2.There is one table om_item where they are inserting records on daily basis and the user there he creates insert statments using toad and then ships us to run on out server to insert those records, can we automate this.

Re: How to compare column name of a schema with other schema's column name [message #573012 is a reply to message #572953] Wed, 19 December 2012 16:21 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I rely heavily on the last_ddl_time to see which objects have changed.
ENWEBP1P > @last_ddl.sql
Enter value for like_object_name: %
Enter value for like_object_type: view
Enter value for like_owner: %
Enter value for days_to_look_back: 10

CREATED   LAST_DDL             OWNER           OBJECT_NAME                    OBJECT_TYPE
--------- -------------------- --------------- ------------------------------ -----------
06-JUN-12 19-DEC-2012 10:28    SITE_USER       ECM_RELATED_IMAGES             VIEW

1 row selected.

ENWEBP1P > list
  1  select created,
  2  to_char(last_ddl_time,'DD-MON-YYYY HH24:MI') Last_ddl,
  3  owner,object_name,object_type ,subobject_name
  4  from all_objects
  5  where object_name like upper('%&like_object_name%')
  6  and object_type like upper('%&like_object_type%')
  7  and owner like upper('%&like_Owner%')
  8  and last_ddl_time > sysdate-&days_to_look_back
  9  and owner not like 'SYS%'
 10* order by last_ddl_time
Re: How to compare column name of a schema with other schema's column name [message #573024 is a reply to message #573012] Thu, 20 December 2012 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But last_ddl_time is also modified by a simple grant or revoke:
SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
11/12/2012 08:30:39

1 row selected.

SQL> grant select on t to scott;

Grant succeeded.

SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
20/12/2012 08:15:18

1 row selected.

SQL> revoke select on t from scott;

Revoke succeeded.

SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
20/12/2012 08:15:50

1 row selected.

Regards
Michel
Re: How to compare column name of a schema with other schema's column name [message #573056 is a reply to message #572953] Thu, 20 December 2012 06:40 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
arif_md2009 wrote on Wed, 19 December 2012 04:35
thanks very much , i will search for this in the documentation and will compare the columns , is there a way to find out differences at procedure/triggers/packages level to know whether they have been modified or not just like tables.Actually there is one problem basically we are facing , we are having two databases one at our site called raklive with user rakdb and another at remote site with another database jedlive with user jeddb but objects in both the databases are same , sometimes at our headoffice that is jedlive does some programming and they fail to intimate us about the changes and so we need to compare everytime, i have two queries and i need your goodself help on this.
1.I need to update the changes whatever done there to be done here using some standard method.
2.There is one table om_item where they are inserting records on daily basis and the user there he creates insert statments using toad and then ships us to run on out server to insert those records, can we automate this.



Do you not have source control? Cause that would fix all these issues if used properly.
Re: How to compare column name of a schema with other schema's column name [message #573066 is a reply to message #573024] Thu, 20 December 2012 08:05 Go to previous message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
Compiling will also update the LAST_DDL_TIME.
Previous Topic: IMPDP from 10.2.0.1.0 to 11.2.0.3 leads to empty tables
Next Topic: export dump
Goto Forum:
  


Current Time: Wed Jul 30 22:28:43 CDT 2014

Total time taken to generate the page: 0.09219 seconds