Skip to Main Content

SQL Developer Data Modeler

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Deleting mappings with transformation scripts

User_UQGFFApr 20 2021

Does anyone have experience with transformation scripts regarding mappings? i have a function that sets mappings accordingly:

RelationalDesign.getRMExtendedMap().createMapping(entity, table);
RelationalDesign.getRMExtendedMap().createMapping(attribute, column);

this works good so far - now i want to undo this. so delete existing mappings again (or remove them, since there seems to be no delete option).
i have tried a few things, but i am not getting anywhere... the documentation of the XML-API (datamodeler/xmlmetadata) is insufficient in this respect and I did not find any further information.
thanks to some try&error attempts I managed to find a way to use the following function calls:

RelationalDesign.getRMDeletedMap().addDeletedMapping(thismapping);
thismapping.setDeletedID(thismapping.getID());

but they do not run without errors

Comments

Manik

Try this query:

---------

SELECT index_owner,

         index_name,

         listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position)

            indexed_cols

    FROM dba_ind_columns

   WHERE index_name LIKE '%XPKTBL_A%'

GROUP BY index_owner, index_name;

--------------

Cheers,

Manik.

unknown-7404

>

I like to compare if two indexes from both users are identical.

>

And why is it you think you need to do that? Explain how that would even be useful.

What does 'identical' even mean to you in terms of indexes?

Erhan_toronto

Hi Manik,

can I have the result like below?

'Indexes are identical' ELSE 'Indexes are different' END "Result" from  DUAL;

Manik

Not efficient way but here it is...

----------------------------------------

WITH t AS

        (SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols)

                   cnt

           FROM (  SELECT index_owner,

                          index_name,

                          listagg (column_name, ',')

                             WITHIN GROUP (ORDER BY column_position)

                             indexed_cols

                     FROM dba_ind_columns

                    WHERE index_name LIKE '%XPKTBL_A%'

                 GROUP BY index_owner, index_name))

SELECT CASE

          WHEN cnt > 1 THEN 'Indexes are different'

          WHEN cnt = 0 THEN 'Indexes dont exist'

          WHEN cnt = 1 THEN 'Indexes are identical'

       END

          commnt

  FROM t

----------------------------------

Cheers,

Manik.

Erhan_toronto

Thanks for reply RP,

The reason that I want to compare index if they are identical or not I want to run another query depends on the result is correct or not like  below.:


if result is true (yes they are identical) than do nothing, else (run another query).



Erhan_toronto

why is the reason that it is not efficient ?

Erhan_toronto

Is this query checks all the indexes in userA or it checks both userA and UserB indexes and if it finds same index name from both user. than do the comparison?

Manik

Because there are efficient ways to deal with such comparisons. (which I have practically not used in my little experience)

Something like this will be more efficient (but even here I am abusing the dbms_metadata_diff package just because of the reason that you were getting null when you used it in 11gr1 (your version) when you asked similar question yesterday.

---------------------------------------------

SELECT CASE

          WHEN INSTR (NVL (dbms_metadata_diff.compare_alter ('INDEX',

                                                             'XPKTBL_A',

                                                             'XPKTBL_A',

                                                             'SCOTT1',

                                                             'SCOTT2'),

                           '0'),

                      'ORA-39287') > 0 THEN

             'INDEXES DIFFERENT'

             ELSE 'INDEXES SAME'

       END

          comparison

  FROM DUAL;

---------------------------------------

Cheers,

Manik.

Erhan_toronto

Actually I did upgrade my version to 11gR2

Erhan_toronto

Thanks Manik, I wasn't sure that I can also compare indexes using dbms_metadata_diff.compare_alter .... I did compare my table and it worked perfectly, now I want to compare my indexes.. I need to change my question little bite than... instead of getting result like same or different. just do the comparison " using dbms_metadata_diff.compare_alter " ? than my query looks like:

select dbms_metadata_diff.compare_alter ('INDEX','XPKTBL_A','XPKTBL_A','USER1','USER2') from dual; is this correct?

Manik

Well the problem with that package that in your case where your indexed columns change, the pacakge (correct to be honest) will not be able to generate sql alter statement (understandable).. And eventually you end up with ora-39287.

Cheers,

Manik.

Erhan_toronto

Yes, I just notice that it doesn't check the index columns if they are the same or not. Than this case what should I do? I think "dbms_metadata_diff.compare_alter" not a option for me to use...

Manik

Check this link and see if the schema comparator script can be of your use.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330

Cheers,

Manik.

Erhan_toronto

Thanks Manik, I will check this out!... Hope find something here

padders

> What does 'identical' even mean to you in terms of indexes?

Lalit Kumar B

By saying IDENTICAL If you mean DUPLICATE indexes, it would make sense.

To list all duplicate indexes use following SQL statement -


SELECT

  /*+ RULE */

  tab_owner.name owner, t.name table_name,

  o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name ,

  o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name

FROM sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2

WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND

  i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2

  WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND

  cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND

  i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND

  t.owner# = tab_owner.USER# AND tab_owner.name LIKE '<schema name>'

ORDER BY 1, 2

Erhan_toronto

What is this?

Erhan_toronto

Duplicate

Erhan_toronto

Hi manik,

Can you please check this:

I am using below query to compare two indexes from 2 different users but even though index name and columns are same... result shows me they are different.. what I am doing wrong? Thanks

WITH t AS

        (SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols)

                   cnt

           FROM (  SELECT index_owner,

                          index_name,

                          listagg (column_name, ',')

                             WITHIN GROUP (ORDER BY column_position)

                             indexed_cols

                     FROM dba_ind_columns

                    WHERE index_name='XPKTBL_A'

                 GROUP BY index_owner, index_name))

SELECT CASE

          WHEN cnt > 1 THEN 'Indexes are different'

          WHEN cnt = 0 THEN 'Indexes dont exist'

          WHEN cnt = 1 THEN 'Indexes are identical'

       END

          commnt

  FROM t

Result:

Indexes are different

but Actually if you check below they are same After when I run this query:

SELECT index_owner,

         index_name,

         listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position)

            indexed_cols

    FROM dba_ind_columns

   WHERE index_name='XPKTBL_A'

GROUP BY index_owner, index_name;

Result:

Index_ownerIndex_nameIndex_cols
USER1XPKTBL_AFIELD_A1
USER2XPKTBL_AFIELD_A1
jgarry

The owners are different, so there are 2.  2 > 1.

Erhan_toronto

I know owners are different but I only want to compare the columns not the owners... can we do that?

unknown-7404

Have you considered doing the comparison the old fashioned way and just look at the index definitions?

You could have compared them manually in a few minutes and you have already spent a day on it.

Lalit Kumar B

Erhan_toronto wrote:

What is this?

What do you expect it to be? You wanted a query to find out the duplicate indexes, right? So i gave you the query to do that. It will list out all the duplicate indexes.

Analyze on list and drop the duplicate indexes.

Erhan_toronto
Answer

Thanks for reply Lalit and sorry for the delayed response.. I got my answer allready.

Marked as Answer by Erhan_toronto · Sep 27 2020
APC

If you have an answer you should mark this thread as "answered", so people won't waste their time on this question.  

Cheers, APC

1 - 25

Post Details

Added on Apr 20 2021
0 comments
180 views