1 Reply Latest reply on Sep 28, 2013 8:18 PM by rp0428

    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