Re: LOB indexes and impdp

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Sun, 13 Mar 2011 08:23:51 +0100
Message-ID: <AANLkTikMT8f1mXeJZ80eg3ZM2FCgoc7Y3OfCzzjGY94j_at_mail.gmail.com>

I recently did a kind of more elaborated query on dba_ind_columns.

you will see, it only compares the columns, but you can enhance it to even more informations to compare between the DBs to question:

   WITH col

     AS (SELECT table_owner,

                table_name,

                index_owner,

                index_name,

                LEVEL                                 l,

                Sys_connect_by_path(column_name ||'~'||descend, '^') COLUMNS

         FROM   dba_ind_columns

--         WHERE  table_owner = 'ANALYZER'

         START WITH column_position = 1

         CONNECT BY PRIOR ( column_position + 1 ) = column_position

                AND PRIOR table_owner = table_owner

                AND PRIOR table_name  = table_name

                AND PRIOR index_owner = index_owner

                AND PRIOR index_name  = index_name)

SELECT c3.table_owner,

       c3.table_name,

       c3.index_owner,

       c3.index_name,

       c2.COLUMNS

FROM (SELECT c1.table_owner,

               c1.table_name,

               c1.index_owner,

               c1.index_name,

               MAX(c1.l) ml

        FROM col c1

        GROUP BY c1.table_owner,

                  c1.table_name,

                  c1.index_owner,

                  c1.index_name) c3,

       col c2

WHERE c3.table_owner = c2.table_owner

       AND c3.table_name = c2.table_name

       AND c3.index_owner = c2.index_owner

       AND c3.index_name = c2.index_name

       AND c3.ml = c2.l

/

hth

 Martin

On Fri, Mar 11, 2011 at 16:40, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Relying on index names as a sign of the indexes matching is a leap of faith
> that can blindside you to a difference.
>
> You need to produce the slightly more challenging query on the
> dba_ind_columns (or user...), including the column positions. You can
> report
> excess indexes on either side this way. Missing from production is often a
> problem, but different in production shows up as well. That can be harder
> to
> detect.
>
> From the indexes in definition that match by column order, you then have
> two
> sets of possible names, and you can query whether the detailed information
> about the indexes that match by column and column order are enabled, etc.
> and have something like a reasonable control on the differences between two
> instantiations of the schema. You can report name differences if you like,
> but except in tie breaker situations in certain releases of the optimizer
> the name of an index is operationally immaterial.
>
> Although from a sense of order and art, I damn well would like to be able
> to
> make all the object names match myself, it isn't material to whether there
> is operational identity. (Except in the aforementioned tie-breaker
> situations, which seem unlikely to eventuate in system named indexes.
>
> mwf
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 13 2011 - 01:23:51 CST