RE: LOB indexes and impdp
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Joel.Patterson@xxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Mar 2011 10:40:59 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Friday, March 11, 2011 8:37 AM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Friday, March 11, 2011 8:21 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: LOB indexes and impdp
>From: <Joel.Patterson@xxxxxxxxxxx>
>To: <oracle-l@xxxxxxxxxxxxx>
>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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
