1 Reply Latest reply: Sep 28, 2013 3:18 PM by rp0428 RSS

    How can I make my query to compare only columns of two tables... not the storage information?

    Erhan_toronto

      11GR2

      =-----------------------------------

       

       

      I am using below querry to compare two table that has same name, under two different users... But after making storage information false like below and  if the storage information is different on column level than it create "Alter modify " statements for the column ... How can I make my query to compare only columns of two tables... not the storage information?

       

      begin

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE', FALSE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);

      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',FALSE);

      End;

       

      select REGEXP_REPLACE(dbms_metadata_diff.compare_alter('TABLE','TABLE_NAME_A','TABLE_NAME_A','USER1','USER2'),('USER1...'),'', 1, 0, 'i') from dual

        • 1. Re: How can I make my query to compare only columns of two tables... not the storage information?
          rp0428
          I am using below querry to compare two table that has same name, under two different users... But after making storage information false like below and  if the storage information is different on column level than it create "Alter modify " statements for the column ... How can I make my query to compare only columns of two tables... not the storage information?

          If you want help you have to SHOW us what you are doing and how you are doing it; you can't just try to tell us in your own words.

           

          We can't see your computer screen.