RE: LOB indexes and impdp

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 11 Mar 2011 11:12:53 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA16151D85CD_at_JAXMSG01.crowley.com>



Mark,

Thank you. This example was but one of an entire series of queries that checked for all that and more. The point is that the output should be 'clean' if everything is the same, and it is not because datapump does not keep the index_name for LOBs.

The script include default data, column positions,... just about everything. The index names where but a specific issue.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, March 11, 2011 10:41 AM
To: Patterson, Joel; jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org Subject: RE: LOB indexes and impdp

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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Friday, March 11, 2011 8:37 AM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org Subject: RE: LOB indexes and impdp

Interesting.

Of course, if one wishes to compare schemas between production, acceptance, etc. etc. and have a log of LOB columns, then as a 'difference' they will always stand out because all the names start with SYS%.

I can move the LOB column this way, and then if I select from user_indexes minus select from user_indexes_at_otherdb zero rows will be returned. It is after all an index_name.

alter table BLEQUIPMENTINFO add (DESCOFGOODSES_TMP CLOB)   LOB (DESCOFGOODSES_TMP) STORE AS BLEQUIP_DESCOFGOODSES_SEG       (TABLESPACE INDEX1 INDEX BLEQUIP_DESCOFGOODSES_LOG_IX (TABLESPACE INDEX1)); update BLEQUIPMENTINFO set DESCOFGOODSES_TMP=DESCOFGOODSES;

alter table BLEQUIPMENTINFO drop column DESCOFGOODSES;

alter table BLEQUIPMENTINFO rename column DESCOFGOODSES_TMP to DESCOFGOODSES; Joel Patterson
Database Administrator
904 727-2546

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, March 11, 2011 8:21 AM
To: oracle-l_at_freelists.org
Subject: Re: LOB indexes and impdp

>From: <Joel.Patterson_at_crowley.com>
>To: <oracle-l_at_freelists.org>
>Sent: Friday, March 11, 2011 12:35 PM
>Subject: LOB indexes and impdp
>

>I do a shema data pump export on 10.2.0.4 database on sparc 64. LOB
>indexes are named.
>
> After import, Index names go away. Is this a known bug? Do I have to
> manually rename them? Did I do something wrong?

You're not supposed to know anything about the LOB indexes - and although the syntax exists to name them you're not supposed to use it. Presumably, therefore, the code that extracts the LOB DDL on export doesn't bother to query for the lobindex name.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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


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


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 11 2011 - 10:12:53 CST

Original text of this message