Re: Partitioning problems - Oracle 8.1.7.4

So in my last post everything was accurate apart from the text! I had meant to write "I only have an index corresponding to the global index....." but even got that wrong. Mondays! Thanks for the sound advise and pointers.

Cheers
Tony

Tim Gorman <tim_at_evdbt.com>
Sent by: oracle-l-bounce_at_freelists.org
02/07/2005 12:58 PM
Please respond to
tim_at_evdbt.com

To
"Oracle L (E-mail)" <oracle-l_at_freelists.org> cc

Subject
Re: Partitioning problems - Oracle 8.1.7.4

> When I drop the global index on my temp table, I get the same error, so
I
> dropped the local index and recreated the global.

You don't have "global" and "local" indexes on your non-partitioned temporary table. You have indexes on the temporary table which correspond to the global and local indexes on your partitioned table.

> So on my temp table I now only have the global index:
>
> SQL> create index I_TONYTAB_TEMP_1 on
> 2 TONYTAB_TEMP (CUSTOMER_ID,SERVICE_ID)
> 3 tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
> 4 storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease
> 0);
>
> Index created

No, this is the index on TONYTAB_TEMP which corresponds to the LOCAL index on TONYTAB, not the GLOBAL. Remember, you do not have LOCAL and GLOBAL indexes on non-partitioned tables.

>
> SQL> alter table TONYTAB exchange partition P01 with table TONYTAB_TEMP
> including indexes without validation;
>
> Table altered
>
> Which seems to be opposite to what you are suggesting :-(

No, this is exactly what I was saying. The use of the INCLUDING INDEXES means that you can only exchange indexes corresponding to LOCAL indexes. Having indexes corresponding to GLOBAL indexes will cause an error, because
even though they reference the same columns, they do not function the same way...

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



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